Query JSON data

In OceanBase Database, you can use dot notation and JSON functions to access JSON data. OceanBase Database recommends that you store JSON data in the JSON base type.

Access JSON data by using dot notation

The dot notation syntax is essentially a table alias, followed by a JSON column name, and then one or more field names, separated by periods (.) to indicate the fields.

table_alias.json_col_name. json_field

# or

.json_field followed by array_step.(field_name)

When using the dot notation syntax as a query parameter in SQL, the following constraints apply:

• table_alias must be an alias for the table.
• json_col_name must be valid JSON data (with the IS JSON constraint or itself being of the JSON type).
• array_step cannot exist alone and must follow json_field, for example: .json_field[1,2].
• The identifier length for each node must not exceed 128 bytes.
• The size of the query return value must not exceed 4k; if it does, the return value is NULL.

In the following example, po is an alias for the j_purchaseorder table, po_doc is a JSON column or a VARCHAR2, BLOB, or CLOB type with the IS JSON constraint, and PONumber is the field name in the JSON data.

SELECT po.po_doc.PONumber FROM j_purchaseorder po;

The dot notation syntax can also be used as a parameter for functions and can invoke methods supported by JSON Path. Here is an example:

SELECT SUBSTR(po.po_doc.PONumber.string(),2,2) FROM j_purchaseorder po;

Access JSON data by using JSON path

Because JSON files are hierarchical, you must use a path expression to extract parts of a JSON document, modify parts of a JSON document, or specify the location in the document to operate on. JSON Path matches JSON data by using JSON functions and conditions, and selects zero or more matching or satisfying JSON values. JSON Path can use wildcards and array ranges, and the matching is case-sensitive.

JSON Path syntax

The basic syntax of JSON Path consists of a context symbol ($) followed by zero or more object, array, and recursive steps. Each node can be followed by a filter expression or a function node. JSON Path (also known as a path expression) can be an absolute path expression or a relative path expression.

• An absolute path expression starts with a $ symbol and is followed by zero or more nodes. For example, in the expression $.filed_a.field_b, $, filed_a, and filed_b are all nodes.
• A relative path expression starts with an @ symbol and is similar to an absolute path expression, except that it only uses relative path expressions in the filter expression.

In a basic path expression, a single function node (Function Step) is optional and typically follows the item_method. If present, it is the last step in the path expression. An object node (Object Step) is a period (.) followed by a field or a wildcard (*), indicating a single field or all fields.

An array node (Array Step) is a left bracket ([) followed by a star wildcard (*) and a right bracket (]). It represents all array elements, one or more specific array indices, or a specified range separated by commas (,).

In a path expression, an array index (Array Indexing) specifies a single array position. The array index can be an integer literal (0, 1, 2, ...). Array positions and indices are zero-based, with the first array element having an index of 0 (specifying position 0). You can use the last index to reference the last element of any non-empty array. The array index can also be in the last - N format, where - represents subtraction. N is an integer literal (0, 1, 2, ...) that is not greater than the array size minus 1. The range is specified as "N to M", for example, "3 to 1", "2 to 4", or "last-1 to last-2". For example, in the original data ["1", "2", "3", "4", "5", "6", "7", "8", "9"], the selected objects are [3 to 1, 2 to 4, last-1 to last-2, 0, 0], and the result is ["2", "3", "4", "3", "4", "5", "7", "8", "1", "1"].

A descendant node (Descendant Step) is represented by two consecutive periods (..) followed by a field. It recursively descends into objects or arrays that match the preceding node and returns all collected field values. Here is an example:

obclient> SELECT JSON_QUERY('{ "a" : { "b" : { "z" : 1 }, "c" : [ 5, { "z" : 2 } ], "z" : 3 }, "z" : 4 }', '$.a..z' WITH ARRAY WRAPPER) FROM DUAL;
+----------------------------------------------------------------------------------------+
| JSON_QUERY('{"A":{"B":{"Z":1},"C":[5,{"Z":2}],"Z":3},"Z":4}','$.A..Z'WITHARRAYWRAPPER) |
+----------------------------------------------------------------------------------------+
| [3,1,2]                                                                                |
+----------------------------------------------------------------------------------------+
1 row in set

A filter expression (Filter Expression, abbreviated as Filter) is represented by a question mark (?) followed by a filter condition enclosed in parentheses (()). If the filter condition is satisfied, it returns true. A filter condition (Filter Condition) uses a predicate (boolean function) as an argument. The filter condition can be in the following forms, where each cond, cond1, and cond2 represents a filter condition.

• (cond): Parentheses are used for grouping, separating the filter condition cond from other filter conditions before or after it.
• cond1 && cond2: and requires both cond1 and cond2 to be satisfied.
• cond1 || cond2: Requires cond1 or cond2 to be satisfied, or both.
• ! (cond): The negation of cond, meaning cond must not be satisfied.
• exists (followed by a relative path expression): The target data exists under the specified condition.
• The comparison predicate has the following forms:
  • A relative path expression, followed by a comparison predicate, and then a JSON scalar value or JSON variable.
  • A JSON scalar value or JSON variable, followed by a comparison predicate, and then a relative path expression.
  • A JSON scalar value, followed by a comparison predicate, and then another JSON scalar value.
• The connection predicate supports &&, ||, and !. The comparison predicate supports >, >=, <, <=, ==, and !=. For strings, the comparison predicate supports has substring and starts with. Other comparison predicates such as like, like_regex, and eq_regex are not supported.

Here are some examples of accessing JSON data using JSON Path:

$.fruits # The value of the fruits field in the object.

$.fruits[0]  # The first element of an array object.

$.fruits[0].name  # The value of the name field in the object, which is the first element of the fruits array.

$.fruits[*].name  # The value of the name field in each object of the fruits array.

$.*[*].name  # The value of the name field in all array objects contained in the object.

$.fruits[3, 7 to 10, 12]  # The 4th, 8th to 11th, and 13th elements of the fruits array. The elements must be specified in ascending order.

$.fruits[3].price  # The value of the price field in the 4th element of the fruits array.

$.fruits[3].*  # The value of the 4th element of the fruits array.

$.fruits[3].price[0].year  # The value of the year field in the 1st element of the price array in the 4th element of the fruits array.

$.fruits[3].price[0]?(@.year > 2016)  # The value of the 1st element of the price array in the 4th element of the fruits array, if the value of the year field in the 1st element of the price array can be converted to a number and satisfies '> 2016'.

$.fruits[3].price[0]?(@.year.number() > 2016)  # Same as the previous example, but the year field value must have the number() method (which returns a number for strings and arrays, such as 2017, "2017", and returns 2017), and the method's return value must be greater than 2016.

$.fruits[3].price[0]?(@.year.numberOnly() > 2016)  # Same as the previous example, but only the numberOnly() method of the year field value is used, excluding cases where the year value is a string number (such as "2017").

$.fruits[3]?(@.produce.city == "San Francisco")  # The 4th element of the fruits array, provided it has a produce field whose value is an object with a city field whose value is the string "San Francisco".

JSON Path also supports a lenient syntax. The difference between the lenient and strict syntax is in how arrays are handled. In the lenient syntax, accessed JSON data is automatically wrapped in array type or unwrapped.

For example, in the lenient syntax, $.fruits is equivalent to $.fruits or $[*].fruits in the strict syntax. In the lenient syntax, $.fruits[0].name is equivalent to $.fruit[0].name, $.fruit.name, $[*].fruits.name, or $[*].fruits[0].name in the strict syntax.

Item methods of JSON Path

The item methods of JSON Path are the final step in a path expression. They can convert the target JSON data into (possibly other) JSON data. However, queries using path expressions (with or without item methods) can return data in SQL data types that do not support JSON data. The following table lists the item methods supported in the current version.

In addition to JSON data types, a set of SQL functions can be used to create, query, and modify JSON values. For more information, see JSON functions.