OceanBase AI Database supports querying and referencing JSON values, and allows you to extract parts of a JSON document or modify specific content within it using path expressions.
Reference JSON values
OceanBase AI Database supports the following two methods for querying and referencing JSON values:
Reference a double-quoted key-value pair in JSON data using the "->" symbol.
Reference a key-value pair without double quotes in the JSON data using the "->>"' symbol.
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
JSON files are hierarchical. Therefore, JSON functions use path expressions to extract parts of a JSON document or modify specific content within it, and you can also specify the location of the operation within the document. For more information about JSON functions, see JSON_ARRAY.
OceanBase AI Database uses the path syntax "leading $ character + symbol selector" to represent the JSON document being accessed. The types of symbol selectors are as follows:
The symbol "." indicates the key name to access. Names without quotes are invalid in path expressions (for example, because they may contain spaces), so you must enclose the key name in double quotes.
Example:
obclient> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name'); +---------------------------------------------------------+ | JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') | +---------------------------------------------------------+ | "Aztalan" | +---------------------------------------------------------+ 1 row in setThe symbol "[N]" is placed after the path of the selected array to access the value at position N in the array, where N is a non-negative integer. Array positions are integers starting from zero. If
pathdoes not select an array value, thenpath[0]has the same computed value aspath.Example:
obclient> SELECT JSON_SET('"x"', '$[0]', 'a'); +------------------------------+ | JSON_SET('"x"', '$[0]', 'a') | +------------------------------+ | "a" | +------------------------------+ 1 row in setThe 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.
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 setPath expressions can also include the * or ** wildcard characters, as described below:
.[*]represents the value of all members in the JSON object.[*]indicates calculating the values of all elements in a JSON array.prefix**suffixspecifies all paths that start with the prefix and end with the suffix. The prefix is optional, but the suffix is required. You cannot use "**" or "***" to describe any path.
Note
A path that does not exist in the document (calculated as non-existent data) will be evaluated as
NULL.
Modify a JSON value
OceanBase AI Database also supports modifying entire JSON values using DML statements, and operating on partial JSON values within an UPDATE statement by using the JSON_SET(), JSON_REPLACE(), or JSON_REMOVE() functions.
An example is as follows:
// 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;
//Update some data by using a function.
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 branches. For paths used in JSON functions, the scope is the document being searched or otherwise manipulated, represented by the leading $ character.
Path segments are separated by the dot character (.). Elements in an array are represented as [N], where N is a non-negative integer. A key name must be a double-quoted string or a valid ECMAScript identifier.
Path expressions (such as JSON text) should be encoded using the ascii, utf8, or utf8mb4 character set (other character encodings are implicitly forced to utf8mb4).
The complete syntax is as follows:
pathExpression: //Path expression
scope[(pathLeg)*] // The scope is described by the prefix $.
pathLeg:
member | arrayLocation | doubleAsterisk
member:
period ( keyName | asterisk )
arrayLocation:
leftBracket ( nonNegativeInteger | asterisk ) rightBracket
keyName:
ESIdentifier | doubleQuotedString
doubleAsterisk:
'**'
period:
'.'
asterisk:
'*'
leftBracket:
'['
rightBracket:
']'