Query and modify JSON values

seekdb allows you to query and reference JSON values and extract parts of a JSON document or modify parts of a JSON document by using path expressions.

Reference JSON values

seekdb allows you to query and reference JSON values in the following two ways:

• Use the -> operator to reference a key-value pair with double quotation marks in JSON data.

• Use the ->> operator to reference a key-value pair without double quotation marks in JSON data.

Here are some examples:

SELECT c->"$.name" AS name FROM jn WHERE g <= 2;

The result is as follows:

+---------+
| name    |
+---------+
| "Fred"  |
| "Wilma" |
+---------+
2 rows in set

SELECT c->>"$.name" AS name FROM jn WHERE g <= 2;

The result is as follows:

+-------+
| name  |
+-------+
| Fred  |
| Wilma |
+-------+
2 rows in set

SELECT JSON_UNQUOTE(c->'$.name') AS name
 FROM jn WHERE g <= 2;

The result is as follows:

+-------+
| name  |
+-------+
| Fred  |
| Wilma |
+-------+
2 rows in set

Because JSON files are hierarchical, JSON functions require path expressions to extract parts of a JSON document or modify parts of a JSON document, and you can also specify the operation position in the document. For more information about JSON functions, see JSON functions.

seekdb uses the path syntax with a leading $ character and a selector to indicate the JSON document being accessed. The types of selectors are as follows:

• The . operator indicates the name of the key to be accessed. Unquoted names in path expressions are invalid (for example, spaces). Therefore, you must specify the key name in double quotation marks.

  Here is an example:

  SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');

  The result is as follows:

  +---------------------------------------------------------+
  | JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
  +---------------------------------------------------------+
  | "Aztalan"                                               |
  +---------------------------------------------------------+
  1 row in set (0.001 sec)

• The [N] selector is placed after the path of the selected array, indicating the value at position N in the array, where N is a non-negative integer. Array positions are integers starting from 0. If the path does not select an array value, then path[0] has the same computed value as path.

  Here is an example:

  SELECT JSON_SET('"x"', '$[0]', 'a');

  The result is as follows:

  +------------------------------+
  | JSON_SET('"x"', '$[0]', 'a') |
  +------------------------------+
  | "a"                          |
  +------------------------------+
  1 row in set (0.001 sec)

• The [M to N] selector is used to specify a subset or range of array values, starting from the value at position M and ending at the value at position N.

  Here is an example:

  SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');

  The result is as follows:

  +----------------------------------------------+
  | JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]') |
  +----------------------------------------------+
  | [2, 3, 4]                                    |
  +----------------------------------------------+
  1 row in set (0.001 sec)

• Path expressions can also contain * or ** wildcards, as described below:

  • .[*] 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 start with a named prefix and end with a named suffix. The prefix is optional, but the suffix is required. You cannot use ** or *** to describe any path.

  info

  Paths that do not exist in the document (computed as non-existent data) are calculated as NULL.

Modify JSON values

seekdb also allows you to modify complete JSON values by using DML statements and to modify parts of JSON values by using the JSON_SET(), JSON_REPLACE(), or JSON_REMOVE() function in UPDATE statements.

Here are some examples:

//Insert all 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 all 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 partial data
DELETE FROM json_tab WHERE id=1;

//Update partial data by 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 path scope and one or more path legs. For paths used in JSON functions, the scope is the document being searched or otherwise operated on, indicated by the leading $ character.

Path segments are separated by period characters (.). Elements in an array are indicated by [N], where N is a non-negative integer. Key names must be double-quoted strings or valid ECMAScript identifiers.

Path expressions (such as 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 described 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:
    ']'