Query and modify JSON values

OceanBase Database supports querying and referencing JSON values, and supports extracting parts of a JSON document or modifying parts of a JSON document by using path expressions.

Reference JSON values

OceanBase Database supports the following two methods for querying and referencing JSON values:

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

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

Here is an example:

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 files are hierarchical, JSON functions need to extract parts of a JSON document or modify parts of a JSON document by using path expressions. You can also specify the operation location in the document. For more information about JSON functions, see JSON functions.

OceanBase Database uses the path syntax "leading $ character plus selector" to represent the JSON document being accessed. The types of selectors are as follows:

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

  Here is an 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 to indicate the value at position N in the array, where N is a non-negative integer. Array positions are integers starting from 0. If path does not select an array value, path[0] has the same computed value as path.

  Here is an example:

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

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

  Here is an 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 the * or ** wildcard. The following table describes these wildcards:

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

  Note

  Paths that do not exist in the document (data that is calculated as nonexistent) are calculated as NULL.

Modify JSON values

OceanBase Database also supports modifying complete JSON values by using DML statements, and modifying parts of a JSON value by using the JSON_SET(), JSON_REPLACE(), or JSON_REMOVE() function in UPDATE statements.

Here is an example:

// 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 operated on, indicated by the leading $ character.

Path segments are separated by period characters (.). Elements in an array are represented 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:
    ']'