PostgreSQL 18 reference · JSON Functions

PostgreSQL JSON_QUERY() Function

Extract an object, array or other JSON fragment with an SQL/JSON path, optionally wrapping several items in an array.

PostgreSQL 18 Since 17

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

JSON_QUERY(json, path [WITH WRAPPER] [RETURNING type])

Parameter	Description
`json`	The JSON document.
`path`	An SQL/JSON path.
`WITH WRAPPER` optional	Wrap multiple results in an array.

jsonb (or the requested type).

Extract a fragment

SELECT JSON_QUERY('{"user":{"tags":["sql","json"]}}', '$.user.tags') AS tags;

Output

      tags
-----------------
 ["sql", "json"]
(1 row)

Run this on your computer — see “Get Started” for setup.

Several items

SELECT JSON_QUERY('{"items":[{"n":"tea"},{"n":"cake"}]}', '$.items[*].n' WITH WRAPPER) AS names;

Output

      names
-----------------
 ["tea", "cake"]
(1 row)

Run this on your computer — see “Get Started” for setup.

Version	Change
PostgreSQL 17	Added

JSON_VALUE() · Extract one scalar from JSON with an SQL/JSON path and return it as a chosen SQL type. Added in PostgreSQL 17.
JSON_TABLE() · Turn JSON into a relational table in the FROM clause, with typed columns taken from SQL/JSON paths. Added in PostgreSQL 17.
SQL String Functions · tutorial