SQL - JSON Functions

SQL - JSON Functions

Modern databases have powerful built-in support for storing and querying JSON (JavaScript Object Notation) data. This allows you to blend the flexibility of schema-less data with the robustness of a relational database. SQL provides a suite of functions to create, query, and manipulate JSON directly within your queries.

1. Why Use JSON in a SQL Database?

• Flexible Schemas: Store semi-structured data, like product attributes, user preferences, or settings, without needing a separate column for every possible attribute.
• API Integration: Easily store and process JSON responses from web APIs.
• Hierarchical Data: Naturally represent nested data structures that are difficult to model in traditional flat tables.

Different databases handle JSON storage differently. PostgreSQL has a highly optimized binary JSONB type, while MySQL has a JSON type. SQL Server stores JSON as NVARCHAR but provides functions to work with it as if it were a native type.

2. Creating JSON Data

You can construct JSON text from your relational data using standard functions.

JSON_OBJECT

Creates a JSON object from a list of key-value pairs.

-- Syntax: JSON_OBJECT('key1', value1, 'key2', value2, ...)
SELECT JSON_OBJECT('id', 1, 'name', 'Laptop', 'in_stock', 1) AS ProductInfo;

JSON_ARRAY

Creates a JSON array from a list of values.

-- Syntax: JSON_ARRAY(value1, value2, ...)
SELECT JSON_ARRAY('Laptops', 'Monitors', 'Keyboards') AS Categories;

3. Querying JSON Data

To extract data from a JSON column, you use a ‘JSON Path Expression’, which is a string that specifies the location of the data (e.g., $.name, $.tags[0]). The $ represents the root of the JSON document.

JSON_VALUE - Extract a Scalar Value

Use JSON_VALUE to pull out a single, simple value like a string, number, or boolean. It’s perfect for use in WHERE clauses or the SELECT list.

-- Syntax: JSON_VALUE(json_column, 'json_path')
SELECT JSON_VALUE(ProductData, '$.name') AS ProductName FROM Products;

JSON_QUERY - Extract an Object or Array

Use JSON_QUERY when you want to extract a nested JSON object or array, rather than a simple value.

-- Syntax: JSON_QUERY(json_column, 'json_path')
SELECT JSON_QUERY(ProductData, '$.specs') AS Specifications FROM Products;

4. Modifying JSON Data

You can also perform in-place updates to JSON documents without rewriting the entire string.

JSON_MODIFY (SQL Server)

Updates a value, adds a new property, or deletes one.

-- Syntax: JSON_MODIFY(json_column, 'json_path', new_value)

-- Update the price of a product
UPDATE Products
SET ProductData = JSON_MODIFY(ProductData, '$.price', 999.99)
WHERE ProductID = 123;

MySQL uses functions like JSON_SET, JSON_INSERT, and JSON_REMOVE for similar purposes.

5. A Complete Practical Example

Let’s model a Products table with a JSON column for flexible attributes.

Step 1: Create the table (Example for SQL Server).

CREATE TABLE Products (
    ProductID INT PRIMARY KEY IDENTITY,
    SKU VARCHAR(50) UNIQUE NOT NULL,
    Attributes NVARCHAR(MAX) -- Stores JSON data
    CONSTRAINT CHK_Attributes_Is_JSON CHECK (ISJSON(Attributes) > 0)
);

Step 2: Insert a product with JSON attributes.

INSERT INTO Products (SKU, Attributes)
VALUES ('LP-BLK-15',
'{
  "name": "ProBook 15",
  "color": "Black",
  "specs": {
    "ram_gb": 16,
    "storage_gb": 512
  },
  "tags": ["business", "15-inch"]
}');

Step 3: Query for products with a specific attribute.

SELECT SKU, Attributes
FROM Products
WHERE JSON_VALUE(Attributes, '$.color') = 'Black';

Step 4: Extract a nested value.

SELECT SKU, JSON_VALUE(Attributes, '$.specs.ram_gb') AS RAM_GB FROM Products;

Step 5: Add a new property to the JSON.

UPDATE Products
SET Attributes = JSON_MODIFY(Attributes, '$.on_sale', 'true')
WHERE SKU = 'LP-BLK-15';

6. Performance and Indexing Considerations

Querying inside JSON strings can be slower than querying indexed relational columns. To optimize performance:

• Use Native Binary Types: If your RDBMS supports it (like JSONB in PostgreSQL), use it. It’s much faster for querying.
• Index on JSON Properties: Some databases allow you to create indexes on values within the JSON. In SQL Server, you can create a computed column based on a JSON_VALUE expression and then index that column. In PostgreSQL, you can create a GIN index directly on a JSONB column.
• Promote Common Keys: If you find yourself frequently filtering by a specific key inside the JSON (like color in our example), consider ‘promoting’ it to its own regular, indexed column on the table for maximum performance.