Top 5 JSON commands in SQL

Here are sample examples for the Top 5 JSON commands in SQL:

1. JSON_VALUE

Purpose: Extracts a scalar value (e.g., string, number) from a JSON document.

Use Case: Extract a specific value from a JSON object (e.g., retrieving a product’s name).

Example:

— Table: products
— Column: details (JSON data type)

— Extract the value of the “name” key from the JSON stored in the “details” column
SELECT JSON_VALUE(details, ‘$.name’) AS product_name
FROM products;

Result: | product_name | |————–| | Laptop | | Smartphone | | Tablet |

1. JSON_QUERY

Purpose: Extracts an entire JSON object or array as JSON-formatted text.

Use Case: Extract a sub-JSON object or array from a JSON document (e.g., retrieving product attributes).

Example:

— Table: products
— Column: details (JSON data type)

— Extract the entire “attributes” object from the JSON in the “details” column
SELECT JSON_QUERY(details, ‘$.attributes’) AS product_attributes
FROM products;

Result: | product_attributes | |—————————————-| | {“color”: “red”, “weight”: “2kg”} | | {“color”: “black”, “weight”: “0.5kg”} | | {“color”: “white”, “weight”: “1.2kg”} |

1. JSON_EXISTS

Purpose: Checks whether a given key or condition exists in the JSON data.

Use Case: Filter rows based on the existence of a specific key or value in JSON (e.g., check if a product has a price key).

Example:

— Table: products
— Column: details (JSON data type)

— Select products where the JSON object has the “price” key
SELECT *
FROM products
WHERE JSON_EXISTS(details, ‘$.price’);

Result (only rows where the “price” key exists): | product_id | details | |————|——————————————–| | 1 | {“name”: “Laptop”, “price”: 1200} | | 2 | {“name”: “Smartphone”, “price”: 800} |

1. JSON_MERGEPATCH

Purpose: Merges or updates a JSON object with new data, without affecting the existing structure.

Use Case: Update a specific key in a JSON object (e.g., update the price of a product).

Example:

— Table: products
— Column: details (JSON data type)

— Update the “price” key in the “details” column
UPDATE products
SET details = JSON_MERGEPATCH(details, ‘{“price”: 1500}’)
WHERE JSON_VALUE(details, ‘$.name’) = ‘Laptop’;

Result: After executing the above query, the details column of the “Laptop” row will be updated as follows: | product_id | details | |————|——————————————————–| | 1 | {“name”: “Laptop”, “price”: 1500, “color”: “red”} |

1. JSON_ARRAYAGG

Purpose: Aggregates multiple rows into a single JSON array.

Use Case: Combine multiple rows into a JSON array (e.g., retrieve all product details as a JSON array).

Example:

— Table: products
— Column: details (JSON data type)

— Aggregate all product details into a single JSON array
SELECT JSON_ARRAYAGG(details) AS all_product_details
FROM products;

Result: | all_product_details | |——————————————————————————————————| | [{“name”: “Laptop”, “price”: 1200}, {“name”: “Smartphone”, “price”: 800}, {“name”: “Tablet”, “price”: 600}] |

Summary of All Examples:

These examples cover a wide range of JSON handling scenarios in SQL, from extracting values to updating and aggregating data.