JSON_QUERY()

Returns the result of applying the SQL/JSON path_expression to the context_item. By default, the result is returned as a value of type jsonb, though the RETURNING clause can be used to return as some other type to which it can be successfully coerced. If the path expression may return multiple values, it might be necessary to wrap those values using the WITH WRAPPER clause to make it a valid JSON string, because the default behavior is to not wrap them, as if WITHOUT WRAPPER were specified. The WITH WRAPPER clause is by default taken to mean WITH UNCONDITIONAL WRAPPER, which means that even a single result value will be wrapped. To apply the wrapper only when multiple values are present, specify WITH CONDITIONAL WRAPPER. Getting multiple values in result will be treated as an error if WITHOUT WRAPPER is specified. If the result is a scalar string, by default, the returned value will be surrounded by quotes, making it a valid JSON value. It can be made explicit by specifying KEEP QUOTES. Conversely, quotes can be omitted by specifying OMIT QUOTES. To ensure that the result is a valid JSON value, OMIT QUOTES cannot be specified when WITH WRAPPER is also specified. The ON EMPTY clause specifies the behavior if evaluating path_expression yields an empty set. The ON ERROR clause specifies the behavior if an error occurs when evaluating path_expression, when coercing the result value to the RETURNING type, or when evaluating the ON EMPTY expression if the path_expression evaluation returns an empty set. For both ON EMPTY and ON ERROR, specifying ERROR will cause an error to be thrown with the appropriate message. Other options include returning an SQL NULL, an empty array (EMPTY [ARRAY]), an empty object (EMPTY OBJECT), or a user-specified expression (DEFAULT expression) that can be coerced to jsonb or the type specified in RETURNING. The default when ON EMPTY or ON ERROR is not specified is to return an SQL NULL value. Examples: JSON_QUERY(jsonb '{"a": "[1, 2]"}', 'lax $.a' RETURNING int[] OMIT QUOTES ERROR ON ERROR) →

SELECT JSON_QUERY(jsonb '[1,[2,3],null]', 'lax $[*][$off]' PASSING 1 AS off WITH CONDITIONAL WRAPPER);

3

SELECT JSON_QUERY(jsonb '{"a": "[1, 2]"}', 'lax $.a' OMIT QUOTES);

[1, 2]

Want to run this against your own PostgreSQL server from an iPhone, iPad or Mac? These are ours — every connection goes straight from the device to your database, with no account and no server of ours in between.

Postgres & FriendsPostgreSQL and the open-source engines around it

Signature and description adapted from the PostgreSQL manual, used under the PostgreSQL License. PostgreSQL is a registered trademark of the PostgreSQL Community Association of Canada. This page is not affiliated with the PostgreSQL Global Development Group.