SQL Academy
Premium
Function Reference

JSONB_BUILD_OBJECT

Builds a JSON object from key-value pairs.

PostgreSQL 17.5
JSONB_BUILD_OBJECT(key, value[, ...])

Parameters

keytext
Object key, converted to text
value
Value of any type, converted to JSON

Return value

A jsonb object; if a key repeats, the last value is kept

Examples

Object from different values

PostgreSQL 17.5
SELECT JSONB_BUILD_OBJECT('a', 1, 'b', NULL, 'c', ARRAY [1, 2]) AS obj;
obj
{"a":1,"b":null,"c":[1,2]}

Family member as JSON

PostgreSQL 17.5
SELECT JSONB_BUILD_OBJECT(
		'id',
		member_id,
		'name',
		member_name,
		'status',
		status
	) AS member_json
FROM FamilyMembers;
member_json
{"id":1,"name":"Headley Quincey","status":"father"}
{"id":2,"name":"Flavia Quincey","status":"mother"}
{"id":3,"name":"Andie Quincey","status":"son"}
{"id":4,"name":"Lela Quincey","status":"daughter"}
{"id":5,"name":"Annie Quincey","status":"daughter"}
{"id":6,"name":"Ernest Forrest","status":"father"}
{"id":7,"name":"Constance Forrest","status":"mother"}
{"id":8,"name":"Wednesday Addams","status":"daughter"}

Details

The number of arguments must be even, otherwise it is an error. NULL becomes JSON null, and an array becomes a JSON array. To collect the objects of all rows into one array, use JSONB_AGG(JSONB_BUILD_OBJECT(...)).

See also

->->>ARRAY_AGG