JSON

MySQL

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.

PostgreSQL

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.

SQL Server

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.

Oracle

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.