JSON

In-Depth Exploration of JSON Functions in Modern Databases

JSON (JavaScript Object Notation) has become a widely adopted format for data interchange, and many modern relational databases now offer native support for JSON. This enables developers to store, query, and manipulate semi-structured data alongside traditional relational data. In this article, we explore JSON functions in different types of databases—highlighting how each system implements JSON support, the functions available, and best practices for working with JSON data.

1. Introduction to JSON in Databases

JSON is a lightweight, human-readable format that represents data as key-value pairs and arrays. As applications increasingly require flexibility to handle diverse data structures, relational databases have evolved to support JSON natively. This integration allows you to:

Store semi-structured data: Easily embed JSON objects in columns.
Query JSON data: Use built-in functions to extract, transform, and search within JSON documents.
Combine relational and non-relational paradigms: Leverage the robustness of SQL while retaining the flexibility of JSON.

2. JSON Support in Different Databases

Each major relational database offers JSON support with a unique set of functions and operators. Here, we examine the offerings in PostgreSQL, MySQL, Microsoft SQL Server, and Oracle.

2.1. PostgreSQL

PostgreSQL is known for its advanced JSON capabilities, offering two data types:

JSON: Stores JSON data as text.
JSONB: A binary format that provides efficient indexing and faster query performance.

Key Functions & Operators:

Access Operators:
- -> extracts JSON object field as JSON.
- ->> extracts JSON object field as text.
Functions:
- jsonb_set(target, path, new_value) to update values.
- to_json() and json_build_object() for constructing JSON objects.

Example:

-- Extract a value as text from a JSONB column
SELECT info->>'name' AS employee_name
FROM employees
WHERE info->>'department' = 'Engineering';

-- Update a JSONB field
UPDATE employees
SET info = jsonb_set(info, '{salary}', '75000'::jsonb)
WHERE info->>'employee_id' = '12345';

2.2. MySQL

MySQL introduced native JSON support in version 5.7. The JSON data type and associated functions allow for efficient storage and manipulation of JSON documents.

Key Functions:

JSON_EXTRACT(json_doc, path): Retrieves data from a JSON document.
JSON_SET(json_doc, path, value): Inserts or updates data.
JSON_ARRAY(), JSON_OBJECT(): Create JSON arrays and objects.
-> and ->> Operators: For syntactic sugar, similar to PostgreSQL (available in MySQL 5.7+).

Example:

-- Retrieve a value from a JSON column
SELECT JSON_EXTRACT(info, '$.name') AS employee_name
FROM employees
WHERE JSON_EXTRACT(info, '$.department') = '"Engineering"';

-- Update a JSON value
UPDATE employees
SET info = JSON_SET(info, '$.salary', 75000)
WHERE JSON_EXTRACT(info, '$.employee_id') = '12345';

2.3. Microsoft SQL Server

SQL Server supports JSON functions without having a dedicated JSON data type. Instead, JSON is stored as NVARCHAR, and built-in functions are used to parse and query JSON content.

Key Functions:

JSON_VALUE(json_doc, path): Returns a scalar value.
JSON_QUERY(json_doc, path): Returns an object or an array.
OPENJSON(json_doc): Parses JSON text and returns objects as rows.
FOR JSON: Converts query results to JSON format.

Example:

-- Extract a scalar value from a JSON column
SELECT JSON_VALUE(info, '$.name') AS employee_name
FROM employees
WHERE JSON_VALUE(info, '$.department') = 'Engineering';

-- Parse JSON array into rows
SELECT *
FROM OPENJSON(@json)
WITH (employee_id INT, name NVARCHAR(100));

2.4. Oracle

Oracle Database supports JSON natively starting with version 12c. JSON data is stored as a CLOB, BLOB, or VARCHAR2, and Oracle provides a rich set of functions to manipulate JSON data.

Key Functions:

JSON_VALUE(json_doc, path): Extracts a scalar value.
JSON_QUERY(json_doc, path): Returns JSON objects or arrays.
JSON_OBJECT, JSON_ARRAY: Functions to generate JSON data.
IS JSON: Used in constraints to ensure valid JSON data.

Example:

-- Extract a value from a JSON column
SELECT JSON_VALUE(info, '$.name') AS employee_name
FROM employees
WHERE JSON_VALUE(info, '$.department') = 'Engineering';

-- Create a JSON object from column values
SELECT JSON_OBJECT('id' VALUE employee_id, 'name' VALUE first_name)
FROM employees;

3. Comparing JSON Functionality Across Databases

While each database has its nuances, common themes emerge:

Data Types:
PostgreSQL uses dedicated JSON/JSONB types, whereas SQL Server and Oracle store JSON as strings.
Function Names and Syntax:
Despite differences in function names (e.g., JSON_EXTRACT in MySQL vs. JSON_VALUE in SQL Server and Oracle), the functionality is similar.
Indexing and Performance:
PostgreSQL's JSONB allows indexing directly on JSON fields, while MySQL offers generated columns that can be indexed. SQL Server and Oracle require careful planning to optimize queries on JSON data.
Query Integration:
All systems integrate JSON functions into standard SQL queries, allowing for seamless use of JSON alongside traditional relational data.

4. Best Practices for Working with JSON in Databases

Choose the Right Data Type:
Use native JSON types (or equivalent) when available to benefit from optimized storage and indexing.
Indexing JSON Data:
Create indexes on frequently queried JSON attributes. For example, PostgreSQL allows you to create indexes on JSONB expressions.
Validate JSON Data:
Use constraints (e.g., IS JSON in Oracle) to ensure that stored data adheres to valid JSON format.
Limit JSON Nesting:
Deeply nested JSON can complicate queries and reduce performance. Consider flattening data where possible.
Monitor Performance:
Analyze query plans and optimize JSON functions, particularly when dealing with large JSON documents.
Leverage Application Logic:
For very complex JSON transformations, consider pre-processing data in the application layer before storing it in the database.

5. Conclusion

Native JSON support in relational databases has bridged the gap between structured and semi-structured data storage, allowing developers to take advantage of flexible data models while leveraging SQL’s robust querying capabilities. Whether you're using PostgreSQL, MySQL, SQL Server, or Oracle, understanding the available JSON functions and their specific implementations is crucial for building modern, efficient applications.

By following the examples and best practices discussed in this article, you can harness the power of JSON in your databases—enabling you to manage and query complex data structures with ease and precision.

Last modified: 05 March 2025