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.

This query selects the value of field PONumber from the po_document JSON type column 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.

However, JSON values are generally not so useful in SQL. Instead of returning JSON data, you may want to return an instance of a SQL scalar data type. 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 NUMBER SQL 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. Consider the following:

  • A dot-notation query with an item method always returns a SQL scalar value. It has the effect of using the JSON_VALUE SQL/JSON function 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 the JSON_QUERY SQL/JSON function (or JSON_TABLE with a column that has JSON_QUERY semantics).

Example 3-1 shows equivalent dot-notation and JSON_VALUE queries. Example 3-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_QUERY query with a RETURNING clause that returns a scalar SQL type—the type that is indicated by the item method.

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

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.

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 JSON_VALUE and JSON_QUERY SQL/JSON functions, 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)

  • 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 JSON scalar 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 followed by dot or period (.), the name of the JSON type column, and one or more pairs of a dot or period (.) and a json_field or a json_field followed by an 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. 

table_alias.column_name.json_field[array_step]{.json_field[array_step] ...}

Each json_field must have the syntax of a valid SQL identifier, and the column must be of JSON data type. If either of these rules is not respected then an error is raised at query compile time.

For JSON dot-notation queries, unquoted identifiers (after the column name) are case sensitive, just as if they were quoted. In other words, you can use JSON field names as identifiers without quoting them. For example, you can use po.po_document.PONumber instead of po.po_document."PONumber"—the meaning is the same. This also means that if a JSON field name is uppercase or pascal case, such as PONumber, then you must use po.po_document.PONumber, not po.po_document.ponumber.

Here are some examples of dot-notation syntax. All examples use po as table alias for the j_purchaseorder table and refer to the po_document JSON type column in such table.

  • po.po_document.PONumber: Queries the value of the PONumber field. The query returns an instance of JSON type.

  • po.po_document.PONumber.number(): Queries the value of the PONumber field. The number() item method ensures that the query returns a NUMBER SQL value.

  • po.po_document.LineItems[1]: Queries the value of the second element in LineItems array. The query returns an instance of JSON type.

  • po.po_document.LineItems[*]: Queries all the elements of the LineItems array. The query returns an instance of JSON type.

  • po.po_document.ShippingInstructions.name: Queries the value of the name field, a child of the JSON object that is the value of the ShippingInstructions field. The query returns an instance of JSON type.

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 type 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 the po_document JSON type column corresponds to the path-expression context item, then the po_document.ShippingInstructions expression corresponds to the $.ShippingInstructions path expression and po_document.ShippingInstructions.name corresponds to $.ShippingInstructions.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 ShippingInstructions field. The value of the ShippingInstructions field 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 the implied use of a wildcard for array elements (see SQL/JSON Path Expression Syntax Relaxation) and 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: po.po_document.LineItems.*.Description

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

Example 3-1 JSON Dot-Notation Query Compared With JSON_VALUE

Given the data from Example 2-2, each of these queries returns 1599 and 1600 as JSON numbers. 

SELECT po.po_document.PONumber FROM j_purchaseorder po;

SELECT JSON_VALUE(po_document, '$.PONumber') FROM j_purchaseorder;

Each of these queries returns 1599 and 1600 as NUMBER SQL values. 

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

SELECT JSON_VALUE(po_document, '$.PONumber.number()') FROM j_purchaseorder;

Example 3-2 JSON Dot-Notation Query Compared With JSON_QUERY

Given the data from Example 2-2, each of these queries returns an array of JSON objects representing phone numbers. 

SELECT po.po_document.ShippingInstructions.Phone FROM j_purchaseorder po;

SELECT JSON_QUERY(po_document, '$.ShippingInstructions.Phone') FROM j_purchaseorder;

Each of these queries returns an array of JSON strings representing phone types.

SELECT po.po_document.ShippingInstructions.Phone.type FROM j_purchaseorder po;

SELECT JSON_QUERY(po_document, '$.ShippingInstructions.Phone.type' WITH WRAPPER)
  FROM j_purchaseorder;