JSON Keypaths

Some of the built-in JSON functions operate on sub-elements of 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 indices. Whether a particular key is treated as a string or as an integer depends on the structure of the JSON value being decomposed.

In the following example, the two SELECT queries are equivalent.

INSERT INTO users (userdata) VALUES ('{
    "postIds": [
        { "text": "apples" },
        { "text": "bananas" },
        { "text": "cabbages" },
        { "text": "dried plums" }
    ]
}');

SELECT JSON_EXTRACT_JSON(userdata, 'postIds', 2, 'text') FROM users;
SELECT userdata::postIds::`2`::text FROM users;

UPDATE users SET userdata = JSON_DELETE_KEY(userdata, 'postIds', 2);

Each query yields the JSON object “cabbages”, which is the “text” field of the 2-th element of the “postIds” field of userdata. Because JSON uses 0-based array indices, {“text”:“apples”} is the 0-th element of userdata::postIds.

In the UPDATE query above, the JSON_DELETE_KEY built-in function takes a keypath. The function yields a new JSON object with the 2-th element of the "postIds" field removed—namely, {"postIds": [{"text":"apples"},{"text":"bananas"},{"text":"dried plums"}]}.

The following JSON functions accept keypaths:

• JSON_DELETE_KEY
• JSON_EXTRACT_<type>
• JSON_SET_<type>

Was this Article Helpful?

Thanks For Your Feedback!