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.

SELECT t.data.name FROM fruit t;

NAME
----
"orange"

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.