Query and modify JSON values

OceanBase Database allows you to query and reference JSON values and extract or modify part of a JSON document by using a path expression.

Reference JSON values

OceanBase Database provides two methods for querying and referencing JSON values:

• You can use the ->" operator to reference the value of a key in a JSON document by specifying the key name in double quotation marks.

• You can use the ->> operator to reference the value of a key without specifying the key name in double quotation marks.

Here are 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, so you need to specify a path expression when you want to extract or modify part of a JSON document. You can also specify the position for the operation in the document. For more information about JSON functions, see JSON functions.

OceanBase Database uses the "$" and selector syntax to represent a JSON document. The supported selector types are as follows:

• The "." specifies a key. The key name in the path must be enclosed in double quotation marks. An unquoted key name in a path expression is invalid (for example, a space).

  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] appended to a selected array path specifies the value at position N in the array. N is a nonnegative integer. Array elements are indexed from 0. If a path does not select an array value, then path[0] is the same 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] appended to a selected array path specifies the values from position M to N in the array.

  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
  

• The following wildcard characters are supported in path expressions:

  • .[*] specifies the values of all members in the JSON object.

  • [*] specifies the values of all elements in the JSON array.

  • prefix**suffix specifies all paths that start with a named prefix and end with a named suffix. The suffix is required. The prefix is optional. Do not use ** or *** to describe arbitrary paths.

  Note

  Paths that do not exist in a document (calculated to NULL) are calculated as NULL.

Modify JSON values

OceanBase Database also allows you to modify a complete JSON value by using a DML statement or to modify a partial JSON value in the UPDATE statement by using the JSON_SET(), JSON_REPLACE(), or JSON_REMOVE() function.

Here are 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 is composed of a scope and one or more path segments. In JSON functions, the scope is the document that you want to search for or manipulate, and is indicated by the leading $ character.

Path segments are separated by a period (.) character. An element of an array is indicated by [N], where N is a nonnegative integer. Key names must be quoted strings or valid ECMAScript identifiers.

A path expression (such as a JSON value) can use 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 specified 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:
    ']'