Working with JSON

The JSON data format is used for storing and processing data.

Below are basic operations with data in this format:

• Check the existence of the name object.
• Retrieve the value of the age object.
• Request data from the object.

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

SELECT
    JSON_EXISTS($json, "$.friends[*].name"), -- Checking the existence of the `name` object.
    JSON_VALUE($json, "$.friends[0].age"),   -- Retrieving the value of the `age` object.
    JSON_QUERY($json, "$.friends[0]");       -- Requesting data from the object.

View the example in the right-hand section and click Run.
Query results are available in the Result tab as a table or schema.

Escaping quotes in JSONEscaping quotes in JSON

Let's look at the 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 string value, a raw string and escaping with \" are used. To insert the second string, escaping with \\\" is used.

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

See alsoSee also

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