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 by using double quotation marks (") in the JSON data.

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

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.

  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.

  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.

  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.

Example:

// 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 all 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.

Syntax of a JSON path:

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