JSON_QUERY Function

The JSON_QUERY SQL/JSON function selects one or more values from JSON data and returns those values. You can use JSON_QUERY to retrieve fragments of a JSON document.

The JSON_QUERY function has two required arguments, and it accepts some optional clauses.

  • The first argument to JSON_QUERY is a SQL expression that returns an instance of a SQL data type that contains JSON data, which can be any of these types: BLOB, CLOB, JSON, or VARCHAR2.

  • The second argument to JSON_QUERY is a SQL/JSON path expression followed by optional clauses RETURNING, PASSING, WRAPPER, QUOTES,ON ERROR, and ON EMPTY. The path expression can target any number of JSON values.

The type of the returned value can be specified through the optional RETURNING clause, which can be any of these data types: BLOB, CLOB, JSON, or VARCHAR2.

The RETURNING clause can also specify to allow or disallow scalar JSON values. You can use the DISALLOW SCALAR keywords to match the JSON standards before IETF RFC8259. These JSON standards only allowed JSON objects and arrays at the top level.

If there is no RETURNING clause, then the default return type depends on the input data type. If the input type is JSON, then JSON is also the default return type. Otherwise, VARCHAR2(4000) is the default return type.

The value returned always contains well-formed JSON data. This includes ensuring that non-ASCII characters in string values are escaped as needed. For example, an ASCII tab—Unicode character "CHARACTER TABULATION" (U+0009)—is escaped as \t. The FORMAT JSON keywords are not needed (or available) for JSON_QUERY—JSON formatting is implicit for the return value.

You can use one or more filter expressions in the path expression to select documents based on their content. If the path expression contains a filter, then the data that matches must also satisfy the filter in order for JSON_QUERY to return the value.

The filter expression may refer to SQL/JSON variables, whose values are passed from SQL by binding them with the PASSING clause. The following SQL data types are supported for such variables: BINARY_DOUBLE, DATE, NUMBER, TIMESTAMP, and VARCHAR2. An error is returned if, instead of a constant, you attempt to bind a column reference.

The WRAPPER clause determines the form of the returned string value.

If the JSON value returned by JSON_QUERY is a string and the returning data type is textual—not JSON type, then the JSON string-delimiting double-quotation marks are included in the return value. In this context, you must use the OMIT QUOTES keywords if you want to omit the double-quotation marks. For example, if the return type is VARCHAR2, then the JSON string hello is returned as "hello"—a seven-character VARCHAR2 value.

Note:

You cannot use an array wrapper with the OMIT QUOTES clause for JSON_QUERY. An error is returned if you do that.

The ON ERROR clause determines errors behavior. By default, the following errors return SQL NULL:

  • The first argument is not well-formed JSON data.

  • There is no match in the JSON data for evaluated path expression in the second argument.

  • The return value data type is not large enough to hold the return string.

  • The return value is a single scalar value and the DISALLOW SCALARS clause in use.

  • There are multiple match values and the DISALLOW SCALARS clause is specified but the WRAPPER clause is not.

See also:

JSON_QUERY in Oracle TimesTen In-Memory Database SQL Reference

Example 3-16 Selecting JSON Values Using JSON_QUERY

This example uses JSON_QUERY with an array wrapper. For each document, the function returns a VARCHAR2 value whose contents represent a JSON array with phone type elements, in an unspecified order. 

SELECT JSON_QUERY(po_document, '$.ShippingInstructions.Phone[*].type'
    WITH WRAPPER)
  FROM j_purchaseorder;
The query returns this output, given the JSON data inserted into the j_purchaseorder table in Example 2-2.
< ["Office"] >
< ["Office","Mobile"] >
2 rows found.

JSON_QUERY as JSON_TABLE

The JSON_QUERY function can be viewed as a special case of the JSON_TABLE function. In particular, if you use JSON_QUERY more than once—or use it in a combination with JSON_EXISTS or JSON_VALUE (which you can express using JSON_TABLE)—to access the same data, then a single use of JSON_TABLE has the advantage of parsing the data only once.

Example 3-17 JSON_QUERY Expressed Using JSON_TABLE

The queries in this example are equivalent. Both SELECT statements have the same effect. 

SELECT JSON_QUERY(po_document, '$.LineItems[*]'
                  WITH WRAPPER
                  RETURNING VARCHAR2
                  ERROR ON ERROR)
  FROM j_purchaseorder;

SELECT jt.line_items FROM j_purchaseorder,
  JSON_TABLE(po_document, '$' ERROR ON ERROR
    COLUMNS("line_items" VARCHAR2 FORMAT JSON WITH WRAPPER 
            PATH '$.LineItems[*]')) AS "JT";
Both queries return this output, given the JSON data inserted into the j_purchaseorder table in Example 2-2.
< [{"ItemNumber":1,"Part":{"Description":"Gummo","UnitPrice":27.95,"UPCCode":794043523625}
,"Quantity":8},{"ItemNumber":2,"Part":{"Description":"Sirens","UnitPrice":19.95,"UPCCode":
717951001931},"Quantity":7},{"ItemNumber":3,"Part":{"Description":"Karaoke: Favorite Duets
 1","UnitPrice":19.95,"UPCCode":13023025295},"Quantity":9}] >
< [{"ItemNumber":1,"Part":{"Description":"One Magic Christmas","UnitPrice":19.95,"UPCCode"
:13131092899},"Quantity":9},{"ItemNumber":2,"Part":{"Description":"Lethal Weapon","UnitPri
ce":19.95,"UPCCode":85391628927},"Quantity":5}] >
2 rows found.