Query and modify JSON values

OceanBase Database allows you to query and reference JSON values. You can use path expressions to extract and modify parts of a JSON document.

Reference JSON values

OceanBase Database allows you to query and reference JSON values by using one of the following two methods:

• Use the -> symbol to reference a key value that is quoted in double quotation marks (") in the JSON data.

• Use the ->> symbol to reference a key value that is not quoted in double quotation marks (") in the JSON data.

Here are some 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

JSON documents are hierarchical. Therefore, a JSON function must use path expressions to extract or modify parts of a JSON document or specify operation positions in the JSON document. For more information about JSON functions, see JSON functions.

OceanBase Database uses a leading dollar sign ($) followed by a symbol selector to indicate the JSON document being accessed. The following types of symbol selectors are supported:

• A period (.) indicates the name of the key that you want to access. You must quote the key name by using double quotation marks ("). An unquoted name, such as a space, is invalid in a path expression.

  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
  

• [N] is placed after the path of the selected array to indicate the value of position N in the array. N is a non-negative integer. This means that an array position is an integer starting from 0. If path does not select an array value, then path[0] and path produce the same value in the calculation.

  Here is an example:

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

• [M to N] specifies a subset or range of array values that starts from the value at position M and ends with 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
  

• A path expression can also contain the * or ** wildcard:

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

  • [*] represents the value of all elements in a calculated 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 only the ** or *** wildcard to describe a path.

  Note

  Paths that do not exist in a JSON document are calculated as nonexistent data and evaluate to NULL.

Modify JSON values

OceanBase Database allows you to modify all JSON values by using DML statements. You can also use the JSON_SET(), JSON_REPLACE(), or JSON_REMOVE() function in the UPDATE statement to modify part of the JSON values.

Here are some examples:

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

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

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

// Use a function to update some 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 legs. The scope of a path used in a JSON function is the document being searched or otherwise operated. The path is indicated by a leading dollar sign ($).

Path legs are separated with periods (.). The elements in an array are represented by [N], where N is a non-negative integer. The name of a key must be a string quoted in double quotation marks (") or a valid ECMAScript identifier.

A path expression, such as JSON text, must be encoded by using the ascii, utf8, or utf8mb4 character set. Other character sets are implicitly converted to utf8mb4.

The syntax of a JSON path is as follows:

pathExpression: // The path expression.
    scope[(pathLeg)*]   // The scope is indicated by a leading dollar sign ($).

pathLeg:
    member | arrayLocation | doubleAsterisk

member:
    period ( keyName | asterisk )

arrayLocation:
    leftBracket ( nonNegativeInteger | asterisk ) rightBracket

keyName:
    ESIdentifier | doubleQuotedString

doubleAsterisk:
    '**'

period:
    '.'

asterisk:
    '*'

leftBracket:
    '['

rightBracket:
    ']'