SQL Reference

JSON

JSON (JavaScript Object Notation) in SQL refers to the data type or feature that enables SQL databases to efficiently and safely store, query, and retrieve data formatted as JSON documents. It serves a critical purpose in managing semi-structured data within SQL-based databases, allowing the handling of data that does not necessarily fit neatly into traditional tabular structures. JSON in SQL preserves the flexibility of JSON data allowing nested and hierarchical data organization while leveraging SQL's robustness and reliability. This integration enhances data interoperability between SQL databases and various applications, especially those that use JSON extensively such as most modern web services and APIs.

Example

SELECT CAST('{"user":"John", "age":30, "city":"New York"}' AS JSON);

Output

{
"user": "John",
"age": 30,
"city": "New York"
}

Explanation

In the aforementioned example, a raw JSON string is selected and cast as JSON data type in MySQL. The JSON object holds a user named John, who is 30 years old, and lives in New York.

Example

SELECT json_build_object(
    'companyname', companyname,
    'address', json_build_object(
        'street', street,
        'city', city,
        'postalcode', postalcode
    )
)
FROM company;

Output

{
    "companyname" : "Tech Corp",
    "address" : {
        "street" : "123 Tech Street",
        "city" : "Technology city",
        "postalcode" : "12345"
    }
}

Explanation

The json_build_object function in PostgreSQL is used to build JSON objects. In this example, the function creates a JSON object that contains the companyname and a nested address JSON object, which includes street, city, and postalcode. The FROM clause specifies the “company” table as the source of the data.

Example

DECLARE @json NVARCHAR(4000)
SET @json =
N'{
    "info": {
    "type": 1,
    "address": {
        "town": "Bristol",
        "county": "Avon",
        "country": "England"
               },
    "tags": ["Sport", "Water polo"]
            },
    "type": "Basic"
}'
SELECT
    JSON_VALUE(@json, '$.type') as type,
    JSON_VALUE(@json, '$.info.address.town') as town,
    JSON_QUERY(@json, '$.info.tags') as tags

Output

type    | town    | tags
--------|---------|-----------
Basic   | Bristol | ["Sport", "Water polo"]

Explanation

The JSON_VALUE function is used to extract a scalar value from a JSON string. In this example, the function retrieves the value of the ‘type’ field and the ‘town’ field.

The JSON_QUERY function is used to extract an object or an array from a JSON string. Here, it retrieves the entire ‘tags’ array.

Example

SELECT json_object('Customer_Name' value cust_name, 'Email' value email)
FROM customers
WHERE cust_id = 1;

Output

{
    "Customer_Name": "John Doe",
    "Email": "johndoe@example.com"
}

Explanation

The json_object function is used to create a JSON object from a list of key-value pairs. For each pair, the key is given as a string literal and the value is an expression which will be evaluated for each row in the result set. In the above example, we are creating a JSON object for a specific customer having ‘cust_id’ 1 with ‘Customer_Name’ and ‘Email’ as keys mapped to ‘cust_name’ and ‘email’ columns from ‘customers’ table respectively.