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 through path expressions.

Reference JSON values

OceanBase Database supports the following two methods to query and reference JSON values:

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

• Use the "->>" symbol to reference a key-value pair without double quotation marks in the 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

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

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

• The symbol "." indicates the key name to be accessed. Unquoted names in path expressions are invalid (for example, spaces), so the key name must be specified 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 symbol "[N]" 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 zero. 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 symbol "[M to N]" 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 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.

  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 complete JSON values through DML statements and using the JSON_SET(), JSON_REPLACE(), or JSON_REMOVE() function in UPDATE statements 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 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;

// Use a function to update partial 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 paths used in 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 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 sets 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:
    ']'