PostgreSQL 18 reference · JSON Functions
PostgreSQL JSON_TABLE() Function
Turn JSON into a relational table in the FROM clause, with typed columns taken from SQL/JSON paths. Added in PostgreSQL 17.
PostgreSQL 18 Since 17
Syntax
JSON_TABLE(json, path COLUMNS (name type PATH path, ...))
Parameters
| Parameter | Description |
|---|---|
json |
The JSON document. |
path |
The row path, such as '$.items[*]'. |
COLUMNS |
Each column's name, type and path. |
Return value
A table.
Examples
SELECT *
FROM JSON_TABLE('{"items":[{"name":"tea","qty":2},{"name":"cake","qty":1}]}',
'$.items[*]' COLUMNS (name text PATH '$.name', qty int PATH '$.qty')) AS t;
Output
name | qty ------+----- tea | 2 cake | 1 (2 rows)
Run this on your computer — see “Get Started” for setup.
SELECT *
FROM JSON_TABLE('[{"id":1,"tags":["a","b"]}]', '$[*]'
COLUMNS (id int PATH '$.id', NESTED PATH '$.tags[*]' COLUMNS (tag text PATH '$'))) AS t;
Output
id | tag ----+----- 1 | a 1 | b (2 rows)
Run this on your computer — see “Get Started” for setup.
Version history
| Version | Change |
|---|---|
| PostgreSQL 17 | Added |
Related
JSON_VALUE()· Extract one scalar from JSON with an SQL/JSON path and return it as a chosen SQL type. Added in PostgreSQL 17.JSONB_PATH_QUERY()· Return every JSON item matched by an SQL/JSON path expression, such as '$.items[*] ? (@.price > 10)'.JSONB_ARRAY_ELEMENTS()· Expand a JSON array into one row per element. jsonb_array_elements_text() returns the elements as text.- SQL String Functions · tutorial