Simple Dot-Notation Access to JSON Data

14 Simple Dot-Notation Access to JSON Data

Dot notation is designed for easy, general use and common use cases of querying JSON data. For simple queries it is a handy alternative to using SQL/JSON query functions.

Just as for SQL/JSON query functions, the JSON column that you query must be known to contain only well-formed JSON data. That is, (1) it must be of data type JSON, VARCHAR2, CLOB, or BLOB, and (2) if the type is not JSON then the column must have an is json check constraint.

This query selects the value of field PONumber from JSON column po_document and returns it as a JSON value:

SELECT po.po_document.PONumber FROM j_purchaseorder po;

The returned value is an instance of JSON data type if the column is of JSON type; otherwise, it is a VARCHAR2(4000) value.

But JSON values are generally not so useful in SQL. In particular, you can't use them with SQL ORDER BY or GROUP BY, and you can't use them in comparison or join operations — JSON data is not comparable.^Foot 1

Instead of returning JSON data, you typically want to return an instance of a (non-JSON) SQL scalar data type, which is comparable. You do that by applying an item method to the targeted data. This query, like the previous one, selects the value of field PONumber, but it returns it as a SQL NUMBER value:

SELECT po.po_document.PONumber.number() FROM j_purchaseorder po;

An item method transforms the targeted JSON data, The transformed data is then processed and returned by the query in place of that original data. When you use dot-notation syntax you generally want to use an item method.

A dot-notation query with an item method always returns a (non-JSON) SQL scalar value. It has the effect of using SQL/JSON function json_value to convert a JSON scalar value to a SQL scalar value.

A dot-notation query without an item method always returns JSON data. It has the effect of using SQL/JSON function json_query (or json_table with a column that has json_query semantics).

Example 14-1 shows equivalent dot-notation and json_value queries. Example 14-2 shows equivalent dot-notation and json_query queries.

Dot Notation With an Item Method

A dot-notation query that uses an item method is equivalent to a json_value query with a RETURNING clause that returns a scalar SQL type — the type that is indicated by the item method.

For example: if item method number() is applied to JSON data that can be transformed to a number then the result is a SQL NUMBER value; if item method date() is applied to data that is in a supported ISO 8601 date or date-time format then the result is a SQL DATE value; and so on.

Note:

If a query result includes a JSON string, and if the result is serialized, then the string appears in textual form. In this form, its content is enclosed in double-quote characters ("), some characters of the content might be escaped, and so on.

Be aware that serialization is implicit in some cases — for example, when you use a client such as SQL*Plus.

Suppose that column t.jcol is of data type JSON, with content {"name":"orange"}. This SQL*Plus query prints its result, a JSON string of data type JSON, using double-quote characters:

SELECT t.data.name FROM fruit t;

NAME
----
"orange"

You can convert the JSON string to a SQL string having the same content, by using item method string(). SQL*Plus serializes (prints) the result without surrounding (single- or double-) quote characters:

SELECT t.data.name.string() FROM fruit t;

NAME.STRING()
-------------
orange

Dot Notation Without an Item Method

If a dot-notation query does not use an item method then a SQL value representing JSON data is returned. In this case:

If the queried data is of type JSON then so is the returned data.
Otherwise, the queried data is textual (type VARCHAR2, CLOB, or BLOB), and the returned data is of type VARCHAR2(4000).

If a dot-notation query does not use an item method then the returned JSON data depends on the targeted JSON data, as follows:

If a single JSON value is targeted, then that value is returned, whether it is a JSON scalar, object, or array.
If multiple JSON values are targeted, then a JSON array, whose elements are those values, is returned. (The order of the array elements is undefined.)

This behavior contrasts with that of SQL/JSON functions json_value and json_query, which you can use for more complex queries. They can return NULL or raise an error if the path expression you provide them does not match the queried JSON data. They accept optional clauses to specify the data type of the return value (RETURNING clause), whether or not to wrap multiple values as an array (wrapper clause), how to handle errors generally (ON ERROR clause), and how to handle missing JSON fields (ON EMPTY clause).

When a single value JSON value is targeted, the dot-notation behavior is similar to that of function json_value for a scalar JSON value, and it is similar to that of json_query for an object or array value.

When multiple values are targeted, the behavior is similar to that of json_query with an array wrapper.

Dot Notation Syntax

The dot-notation syntax is a table alias (mandatory) followed by a dot, that is, a period (.), the name of a JSON column, and one or more pairs of the form . json_field or . json_field followed by array_step, where json_field is a JSON field name and array_step is an array step expression as described in Basic SQL/JSON Path Expression Syntax.

Each json_field must have the syntax of a valid SQL identifier^Foot 2, and the column must be of JSON data type or have an is json check constraint, which ensures that it contains well-formed JSON data. If either of these rules is not respected then an error is raised at query compile time. (If the column is not of data type JSON then the check constraint must be present to avoid raising an error; however, it need not be active. If you deactivate the constraint then this error is not raised.)

For the dot notation for JSON queries, unlike the case generally for SQL, unquoted identifiers (after the column name) are treated case sensitively, that is, just as if they were quoted. This is a convenience: you can use JSON field names as identifiers without quoting them. For example, you can write t.jcolumn.friends instead of t.jcolumn."friends" — the meaning is the same. This also means that if you query a JSON field whose name is uppercase, such as FRIENDS, then you must write t.jcolumn.FRIENDS, not t.jcolumn.friends.

Here are some examples of dot notation syntax. All of them refer to JSON column po_document of a table that has alias po.

po.po_document.PONumber – The value of field PONumber as a JSON value. The value is returned as an instance of JSON type if column po_document is JSON type; otherwise, it is returned as a SQL VARCHAR2(4000) value.
po.po_document.PONumber.number() – The value of field PONumber as a SQL NUMBER value. Item method number() ensures this.
po.po_document.LineItems[1] – The second element of array LineItems (array positions are zero-based), returned as JSON data (JSON type or VARCHAR2(4000), depending on the column data type).
po.po_document.LineItems[*] – All of the elements of array LineItems (* is a wildcard), as JSON data.
po.po_document.ShippingInstructions.name – The value of field name, a child of the object that is the value of field ShippingInstructions, as JSON data.

Matching of a JSON dot-notation expression against JSON data is the same as matching of a SQL/JSON path expression, including the relaxation to allow implied array iteration (see SQL/JSON Path Expression Syntax Relaxation). The JSON column of a dot-notation expression corresponds to the context item of a path expression, and each identifier used in the dot notation corresponds to an identifier used in a path expression.

For example, if JSON column jcolumn corresponds to the path-expression context item, then the expression jcolumn.friends corresponds to path expression $.friends, and jcolumn.friends.name corresponds to path expression $.friends.name.

For the latter example, the context item could be an object or an array of objects. If it is an array of objects then each of the objects in the array is matched for a field friends. The value of field friends can itself be an object or an array of objects. In the latter case, the first object in the array is used.

Note:

Other than (1) the implied use of a wildcard for array elements (see SQL/JSON Path Expression Syntax Relaxation) and (2) the explicit use of a wildcard between array brackets ([*]), you cannot use wildcards in a path expression when you use the dot-notation syntax. This is because an asterisk (*) is not a valid SQL identifier.

For example, this raises a syntax error: mytable.mycolumn.object1.*.object2.

Dot-notation syntax is a handy alternative to using simple path expressions; it is not a replacement for using path expressions in general.