Working with JSON

The JSON format is used for storing and processing data.

Basic JSON data operations:

• Check if the name object exists.
• Retrieve the age object’s value.
• Request object data.

$json = CAST(@@{
    "friends": [
        {
            "name": "James Holden",
            "age": 35
        },
        {
            "name": "Naomi Nagata",
            "age": 30
        }
    ]
}@@ AS Json);

SELECT
    JSON_EXISTS($json, "$.friends[*].name"), -- Check if the `name` object exists
    JSON_VALUE($json, "$.friends[0].age"),   -- Retrieve the `age` object’s value
    JSON_QUERY($json, "$.friends[0]");       -- Request object data

Check the example in the right-hand section and click Run.
The query result will appear in the Result tab as a table or chart.

Escaping quotes in JSONEscaping quotes in JSON

Here is an example showing two ways to add a JSON string to a table:

UPSERT INTO test_json(id, json_string)
VALUES
    (1, Json(@@[{"name":"Peter \"strong cat\" Kourbatov"}]@@)),
    (2, Json('[{"name":"Peter \\\"strong cat\\\" Kourbatov"}]'))
;

To insert the first value, we use a raw string and escaping via \". To insert the second value, we use escaping via \\\".

We recommend using a raw string and escaping via \", as it is more readable.

Useful linksUseful links

• SELECT operator
• JSON_EXISTS function
• JSON_VALUE function
• JSON_QUERY function
• Functions for JSON