Query and modify JSON values

OceanBase Database supports querying and referencing JSON values, and 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 -> operator to reference a JSON value with a double-quoted key.

• Use the ->> operator to reference a JSON value without double quotes.

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, you must use path expressions to extract parts of a JSON document or modify parts of a JSON document. 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 that starts with a $ character and is followed by a selector to indicate the JSON document being accessed. The types of selectors are as follows:

• The . selector indicates the name of the key to be accessed. Names without quotes 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] selector is placed after the path of the selected array to indicate the value at position N in the array, where N is a nonnegative integer. Array positions are integers starting from 0. If path does not select an array value, then path[0] and path have the same calculated value.

  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] 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:

  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 ** wildcards. 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 named prefix and end with the named 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 to be nonexistent) are calculated as NULL.

Modify JSON values

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

Here are some examples:

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

// Insert part of the 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 part of the data.
UPDATE json_tab SET json_info=JSON_REPLACE(json_info, '$[2]', 'aaa') WHERE id=1;

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

// Use a function to update part of the data.
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 segments. For JSON functions, the scope is the document being searched for 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 nonnegative 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 indicated 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:
    ']'