Query and modify JSON values

seekdb supports querying and referencing JSON values. Using path expressions, you can extract or modify specific portions of a JSON document.

Reference JSON values

seekdb provides two methods for querying and referencing JSON values:

• Use the -> operator to return a key's value with double quotes in JSON data.

• Use the ->> operator to return a key's value without double quotes in JSON data.

Examples:

obclient> SELECT c->"$.name" AS name FROM jn WHERE g <= 2;
+---------+
| name    |
+---------+
| "Fred"  |
| "Wilma" |
+---------+
2 rows in set

obclient> SELECT c->>"$.name" AS name FROM jn WHERE g <= 2;
+-------+
| name  |
+-------+
| Fred  |
| Wilma |
+-------+
2 rows in set

obclient> SELECT JSON_UNQUOTE(c->'$.name') AS name
 FROM jn WHERE g <= 2;
+-------+
| name  |
+-------+
| Fred  |
| Wilma |
+-------+
2 rows in set

Because JSON documents are hierarchical, JSON functions use path expressions to extract or modify portions of a document and to specify where in the document the operation should occur.

seekdb uses a path syntax consisting of a leading $ character followed by a selector to represent the JSON document being accessed. The selector types are as follows:

• The . symbol represents the key name to access. Unquoted names are not valid in path expressions (for example, names containing spaces), so key names must be enclosed in double quotes.

  Example:

  obclient> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
  +---------------------------------------------------------+
  | JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
  +---------------------------------------------------------+
  | "Aztalan"                                               |
  +---------------------------------------------------------+
  1 row in set

• The [N] symbol is placed after the path of the selected array and represents the value at position N in the array, where N is a non-negative integer. Array positions are zero-indexed. If path does not select an array value, then path[0] evaluates to the same value as path.

  Example:

  obclient> SELECT JSON_SET('"x"', '$[0]', 'a');
  +------------------------------+
  | JSON_SET('"x"', '$[0]', 'a') |
  +------------------------------+
  | "a"                          |
  +------------------------------+
  1 row in set

• The [M to N] symbol specifies a subset or range of array values, starting from position M and ending at position N.

  Example:

  obclient> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');
  +----------------------------------------------+
  | JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]') |
  +----------------------------------------------+
  | [2, 3, 4]                                    |
  +----------------------------------------------+
  1 row in set

• Path expressions can also include * or ** wildcard characters:

  • .[*] represents the values of all members in a JSON object.

  • [*] represents the values of all elements in a JSON array.

  • prefix**suffix represents all paths that begin with the specified prefix and end with the specified suffix. The prefix is optional, but the suffix is required. Using ** or *** alone to match arbitrary paths is not allowed.

  info

  Paths that do not exist in the document (evaluating to non-existent data) evaluate to NULL.

Modify JSON values

seekdb also supports modifying complete JSON values using DML statements, and using the JSON_SET(), JSON_REPLACE(), or JSON_REMOVE() functions in UPDATE statements to modify partial JSON values.

Examples:

// Insert complete data.
INSERT INTO json_tab(json_info) VALUES ('[1, {"a": "b"}, [2, "qwe"]]');

// Insert partial data.
UPDATE json_tab SET json_info=JSON_ARRAY_APPEND(json_info, '$', 2) WHERE id=1;

// Update complete data.
UPDATE json_tab SET json_info='[1, {"a": "b"}]';

// Update partial data.
UPDATE json_tab SET json_info=JSON_REPLACE(json_info, '$[2]', 'aaa') WHERE id=1;

// Delete data.
DELETE FROM json_tab WHERE id=1;

// Update partial data using a function.
UPDATE json_tab SET json_info=JSON_REMOVE(json_info, '$[2]') WHERE id=1;

JSON path syntax

A path consists of a scope and one or more path segments. For paths used in JSON functions, the scope is the document being searched or otherwise operated on, represented by the leading $ character.

Path segments are separated by periods (.). Array elements are represented by [N], where N is a non-negative integer. Key names must be either double-quoted strings or valid ECMAScript identifiers.

Path expressions (like JSON text) should be encoded using the ascii, utf8, or utf8mb4 character set. Other character encodings are implicitly converted to utf8mb4.

The complete syntax is as follows:

pathExpression: // Path expression
    scope[(pathLeg)*]   // Scope is represented by the leading $ character

pathLeg:
    member | arrayLocation | doubleAsterisk

member:
    period ( keyName | asterisk )

arrayLocation:
    leftBracket ( nonNegativeInteger | asterisk ) rightBracket

keyName:
    ESIdentifier | doubleQuotedString

doubleAsterisk:
    '**'

period:
    '.'

asterisk:
    '*'

leftBracket:
    '['

rightBracket:
    ']'