JSON Keypaths

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

Some built-in JSON functions operate on sub-elements in their first argument. A particular sub-element is selected by means of a keypath, which consists of a list of string keys and/or integer array indexes. How a particular key is treated depends on the structure of the JSON value.

The following table and values were used in the examples:

SQL

CREATE TABLE greek_alpha (ltr_name text(25));

DESC greek_alpha;

+----------+------+-----+-----+---------+-------+
| Field    | Type | Null| Key | Default | Extra |
+----------+------+-----+-----+---------+-------+
| ltr_name | text | YES |     | NULL    |       |
+----------+------+-----+-----+---------+-------+

SQL

INSERT INTO greek_alpha (ltr_name) VALUES ('{
    "posIds": [
        { "text": "alpha" }, 
        { "text": "beta" },
        { "text": "gamma" },
        { "text": "delta" }  
    ]
}');

In the following examples, both of the SELECT queries will have the same result, "gamma". JSON arrays begin with zero so in these examples "gamma" is in the second position of the array.

SQL

SELECT JSON_EXTRACT_JSON(ltr_name, 'posIds', 2, 'text') FROM greek_alpha;

+---------------------------------------------------+
| JSON_EXTRACT_JSON(ltr_name, 'posIds', 2, 'text')  |
+---------------------------------------------------+
| "gamma"                                           |
+---------------------------------------------------+

SELECT ltr_name::posIds::`2`::text FROM greek_alpha;


+------------------------------+
| ltr_name::posIds::`2`::text  |
+------------------------------+
| "gamma"                      |
+------------------------------+

In the UPDATE query, the built-in function JSON_DELETE_KEY takes a keypath. The function will generate a new JSON object with the second element being removed.

SQL

UPDATE greek_alpha SET ltr_name = JSON_DELETE_KEY(ltr_name, 'posIds', 2);

SELECT * FROM greek_alpha;

+----------------------------------------------------------------+
| ltr_name                                                       |
+----------------------------------------------------------------+
| {"posIds":[{"text":"alpha"},{"text":"beta"},{"text":"delta"}]} |                    |
+----------------------------------------------------------------+

Note

For more info on JSON operators ::, ::$, and ::%, see Accessing Fields in a JSON Object.

The following JSON functions accept keypaths:

JSON Keypaths

Was this article helpful?

Was this article helpful?