JSON_QUERY

Description

The JSON_QUERY function extracts the JSON value located by a SQL/JSON path from a JSON document and returns it as JSON text (a STRING). This is the SQL-standard way (SQL:2016) to pull an object, array, or scalar fragment out of JSON, and is commonly used to migrate queries from other systems such as Oracle, SQL Server, and Trino. Unlike JSON_TABLE, which produces rows in a FROM clause, JSON_QUERY is an expression that can appear anywhere a value is allowed.

Where JSON_VALUE returns a single scalar (and treats an object or array match as an error), JSON_QUERY returns the matched value serialized as JSON text, whether it is an object, an array, or a scalar.

This implementation supports simple, wildcard-free SQL/JSON paths only. The PASSING clause, path predicates and filters, and explicit lax / strict path modes defined by SQL:2016 are not supported.

Syntax

JSON_QUERY ( json_expr, path
             [ RETURNING data_type ]
             [ wrapper_behavior ]
             [ quotes_behavior ]
             [ empty_behavior ON EMPTY ]
             [ error_behavior ON ERROR ] )

wrapper_behavior
    { WITHOUT [ ARRAY ] WRAPPER
    | WITH [ CONDITIONAL | UNCONDITIONAL ] [ ARRAY ] WRAPPER }

quotes_behavior
    { KEEP QUOTES | OMIT QUOTES }

empty_behavior
    { NULL | ERROR | EMPTY ARRAY | EMPTY OBJECT }

error_behavior
    { NULL | ERROR | EMPTY ARRAY | EMPTY OBJECT }

Parameters

• json_expr

  An expression that evaluates to a STRING containing the JSON document. A NULL input yields NULL directly (it triggers neither the ON EMPTY nor the ON ERROR behavior).

• path

  A SQL/JSON path literal that locates the value, for example '$.a.b' or '$.items[0]'. The path must be wildcard-free; a path containing [*] is rejected at analysis time.

• RETURNING data_type

  The type of the result. It must be a string type; the result is JSON text. If RETURNING is omitted, the result type is STRING.

• wrapper_behavior

  Whether to wrap the result in a JSON array:

  • WITHOUT ARRAY WRAPPER (the default) returns the value unwrapped.
  • WITH UNCONDITIONAL ARRAY WRAPPER (or simply WITH ARRAY WRAPPER) always wraps the value in a one-element array.
  • WITH CONDITIONAL ARRAY WRAPPER wraps the value only when it is a scalar; an object or array is returned unwrapped.

• quotes_behavior

  Whether to keep the surrounding quotes of a scalar string result:

  • KEEP QUOTES (the default) leaves them, so a string is returned as a quoted JSON string.
  • OMIT QUOTES strips them, returning the raw string content. It is a no-op for objects, arrays, and non-string scalars, and cannot be combined with an array wrapper.

• empty_behavior ON EMPTY

  What to produce when path matches nothing:

  • NULL (the default) returns SQL NULL.
  • ERROR raises an error.
  • EMPTY ARRAY returns the JSON text [].
  • EMPTY OBJECT returns the JSON text {}.

• error_behavior ON ERROR

  What to produce when the input is not well-formed JSON. The same four choices as ON EMPTY apply, defaulting to NULL.

A path that matches an explicit JSON null is a present scalar value and returns the JSON text null (it is neither the ON EMPTY nor the ON ERROR case).

Returning a scalar under the default WITHOUT ARRAY WRAPPER is an intentional convenience: the matched scalar is emitted as JSON text (for example, JSON_QUERY('{"id":7}', '$.id') returns 7), whereas strict SQL:2016 treats a scalar without a wrapper as an error. The wrapper clauses behave the standard way: WITH CONDITIONAL ARRAY WRAPPER wraps a scalar in a one-element array (7 becomes [7]) while leaving a single object or array unwrapped, and WITH UNCONDITIONAL ARRAY WRAPPER always wraps.

Examples

-- Extract an object as JSON text
SELECT json_query('{"id":7,"addr":{"city":"NYC"}}', '$.addr');
+---------------------------------------------------+
|json_query({"id":7,"addr":{"city":"NYC"}}, $.addr) |
+---------------------------------------------------+
|{"city":"NYC"}                                     |
+---------------------------------------------------+

-- Extract an array
SELECT json_query('{"tags":["x","y"]}', '$.tags');
+-------------------------------------------+
|json_query({"tags":["x","y"]}, $.tags)     |
+-------------------------------------------+
|["x","y"]                                  |
+-------------------------------------------+

-- Wrap a scalar in an array with WITH ARRAY WRAPPER
-- (WITH ARRAY WRAPPER is a shorthand; the column name shows the canonical
--  WITH UNCONDITIONAL ARRAY WRAPPER form)
SELECT json_query('{"tags":["x","y"]}', '$.tags[0]' WITH ARRAY WRAPPER);
+----------------------------------------------------------------------------+
|json_query({"tags":["x","y"]}, $.tags[0] WITH UNCONDITIONAL ARRAY WRAPPER)  |
+----------------------------------------------------------------------------+
|["x"]                                                                       |
+----------------------------------------------------------------------------+

-- Strip the quotes from a scalar string with OMIT QUOTES
SELECT json_query('{"name":"Ada"}', '$.name' OMIT QUOTES);
+---------------------------------------------------+
|json_query({"name":"Ada"}, $.name OMIT QUOTES)     |
+---------------------------------------------------+
|Ada                                                |
+---------------------------------------------------+

-- A missing path defaults to NULL; supply a fallback with EMPTY ARRAY ON EMPTY
SELECT json_query('{"id":7}', '$.missing' EMPTY ARRAY ON EMPTY);
+---------------------------------------------------------+
|json_query({"id":7}, $.missing EMPTY ARRAY ON EMPTY)     |
+---------------------------------------------------------+
|[]                                                       |
+---------------------------------------------------------+

-- ERROR ON ERROR raises instead of returning a value
SELECT json_query('not json', '$.a' ERROR ON ERROR);
[JSON_QUERY_ON_ERROR.ERROR] ...

Related Statements

• SELECT
• JSON_VALUE
• JSON_TABLE
• Built-in Functions