PostgreSQL 18 reference · JSON Functions

PostgreSQL JSONB_EACH() Function

Expand a JSON object into one row per key, with key and value columns. jsonb_each_text() returns text values.

PostgreSQL 18 Since 9.4

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

Syntax

SELECT * FROM jsonb_each(jsonb)  |  jsonb_each_text(jsonb)

Parameter	Description
`jsonb`	A `jsonb` (or `json`) value.

Rows of key text, value jsonb.

Walk an object

SELECT key, value FROM jsonb_each('{"a":1,"b":"two","c":[3]}');

Output

 key | value
-----+-------
 a   | 1
 b   | "two"
 c   | [3]
(3 rows)

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

As text

SELECT key, value FROM jsonb_each_text('{"theme":"dark","lang":"en"}') ORDER BY key;

Output

  key  | value
-------+-------
 lang  | en
 theme | dark
(2 rows)

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

Version	Change
PostgreSQL 9.4	Added

JSONB_ARRAY_ELEMENTS() · Expand a JSON array into one row per element. jsonb_array_elements_text() returns the elements as text.
JSONB_OBJECT_AGG() · Aggregate key/value pairs from a group into one JSON object, such as a name-to-price lookup.
SQL String Functions · tutorial