Query JSON data

In OceanBase Database, you can use the Dot Notation syntax and JSON functions to access JSON data. We recommend that you store JSON data by using basic JSON data types in OceanBase Database.

Access JSON data by using the Dot Notation syntax

The Dot Notation syntax contains a table alias, followed by a JSON column name and one or more field names. The field names are separated with periods (.).

table_alias.json_col_name. json_field

# or

.json_field followed by array_step.(field_name)

The following are limitations for using the Dot Notation syntax as a query parameter in an SQL statement:

• table_alias must be an alias of a table.
• json_col_name must be valid JSON data with the IS JSON constraint or of a JSON data type.
• array_step cannot be used alone and must be placed after json_field. For example, you can specify it in the .json_field[1,2] format.
• The identifier of each step cannot exceed 128 bytes in length.
• The return value of the query cannot exceed 4 KB. Otherwise, NULL is returned.

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

SELECT po.po_doc.PONumber FROM j_purchaseorder po;

The Dot Notation syntax can also be used as a function parameter and can call methods supported by a JSON path expression. Here is an example:

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

Access JSON data by using a JSON path expression

A JSON file is structured. Therefore, you must use a JSON path expression to extract or modify specific content of the JSON file or specify an operation position in the file. JSON path expressions are matched by JSON functions and conditions against JSON data to select zero or more matching JSON values. JSON path expressions can use wildcards and array ranges, and the matching is case-sensitive.

Syntax of a JSON path expression

The basic syntax of a JSON path expression consists of a context-item symbol ($) followed by zero or more object, array, or descendant steps. Each step can be followed by a filter expression or a function step. A JSON path expression is an absolute path expression or a relative path expression.

• An absolute path expression starts with a dollar sign ($), followed by zero or more steps. For example, in the $.filed_a.field_b expression, $, field_a, and field_b are steps.
• A relative path expression starts with an at sign (@) and is similar to an absolute path expression. The difference lies in that a relative expression is used inside a filter expression.

In a basic path expression, a function step is optional and is usually followed by item_method. If present, a function step is the last step of the path expression. An object step starts with a period (.), followed by a field name or an asterisk (*) wildcard that stands for all fields.

An array step starts with a left bracket ([), followed by an asterisk (*) wildcard, which stands for all array elements, or one or more specific array indexes or range specifications separated with commas (,), and ends with a right bracket (]).

In a path expression, an array index specifies the position of a single array, and the array index can be an integer literal (0, 1, 2, ...). An array position or index starts with 0, and the index of the first array element is 0, which specifies position 0. You can use the index last to reference the last element of a non-empty array of any size. An array index can also be specified in the last - N format, where - is a minus sign and N is an integer literal (0, 1, 2, ...) that is no greater than the array size minus 1. The range is specified in the "N to M" format, such as "3 to 1", "2 to 4", and "last - 1 to last - 2". For example, if the raw data is ["1", "2", "3", "4", "5", "6", "7", "8", "9"] and [3 to 1, 2 to 4, last - 1 to last - 2, 0, 0] are the selected objects, the result is ["2", "3", "4", "3", "4", "5", "7", "8", "1", "1"].

A descendant step is two consecutive periods (..) followed by a field name. It recursively descends to the object or array matching the step immediately preceding it and returns all the 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 for short) starts with a question mark (?), followed by a filter condition enclosed in parentheses (( )). If the filter condition is met, true is returned. A filter condition applies a predicate (Boolean function) to its arguments. Filter conditions can be specified in the following formats, where cond, cond1, and cond2 each stands for a filter condition:

• (cond): The parentheses are used for grouping, separating the filter condition cond as a unit from other filter conditions that may precede or follow it.
• cond1 && cond2: The conjunction and of the cond1 and cond2 conditions, requiring that both conditions be satisfied.
• cond1 || cond2: The inclusive disjunction or of cond1 and cond2, requiring that cond1 or cond2, or both, be satisfied.
• ! (cond): The negation of cond, requiring that the cond condition must not be satisfied.
• exists (followed by a relative path expression): The condition that the target data exists.
• A comparison can be specified in the following formats:
  • A relative path expression followed by a comparison predicate and a JSON scalar value or JSON variable.
  • A JSON scalar value or JSON variable followed by a comparison predicate and a relative path expression.
  • A JSON scalar value followed by a comparison predicate and another JSON scalar value.
• The supported connection predicates are &&, ||, and !. The supported comparison predicates are >, >=, <, <=, ==, and !=. The supported string comparison predicates are has substring and starts with. Other comparison predicates such as like, like_regex, and eq_regex are not supported.

Here are examples of JSON path expressions for JSON data access:

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

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

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

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

$.*[*].name # The values of the "name" fields of all array objects included 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 of an object that is the fourth element of the "fruits" array.

$.fruits[3].*  # The values of all fields of an object that is the fourth element of the "fruits" array.

$.fruits[3].price[0].year # The value of the "year" field of an object that is the first element of an array that is the value of the "price" field of an object that is the fourth element of the "fruits" field.

$.fruits[3].price[0]?(@.year > 2016)  # The first element of the array "price" (field of an object that is the fourth element of the "fruits" array), provided that the value of the "year" field is, or can be converted to, a number greater than 2016.

$.fruits[3].price[0]?(@.year.number() > 2016)  # This expression is similar to the preceding one. The "year" field must use the number() method, and the value returned by the method must be greater than 2016. The method returns a number for both a string and an array. For example, it returns 2017 for both "2017" and 2017.

$.fruits[3].price[0]?(@.year.numberOnly() > 2016)  # This expression is similar to the preceding one. However, the year field uses the numberOnly() method, which excludes the case that the year value is a string such as "2017".

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

JSON path expressions also support syntax relaxation. The relaxed syntax and strict syntax differ in the processing of arrays. In relaxed syntax, the JSON data accessed is automatically wrapped with the array type or automatically unwrapped.

For example, $.fruits in relaxed syntax is equivalent to $.fruits or $[*].fruits in strict syntax, and $.fruits[0].name in relaxed syntax is equivalent to $.fruit[0].name, $.fruit.name, $[*].fruits.name, or $[*].fruits[0].name in strict syntax.

Item methods of a JSON path expression

An item method is the last step of a JSON path expression. It converts the target JSON data to (possibly other) JSON data. A query that uses a path expression (with or without an item method) can return data as an SQL data type that does not support JSON data. The following table describes the item methods supported by the current version of OceanBase Database.

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