🌐
Neon
neon.com › docs › functions › json_build_object
Postgres json_build_object() function - Neon Docs
CREATE TABLE products ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, price DECIMAL(5, 2) NOT NULL, description TEXT, category TEXT, attributes JSON ); INSERT INTO products (name, price, description, category, attributes) VALUES ('T-Shirt', 25.99, 'A comfortable cotton T-Shirt', 'Clothing', json_build_object( 'color', 'Blue', 'size', 'Medium', 'rating', 4.5 )), ('Coffee Mug', 12.99, 'A ceramic mug with a funny design', 'Kitchen', json_build_object( 'color', 'White', 'size', 'Small', 'rating', 3.8 )), ('Sneakers', 49.99, 'Sporty sneakers for everyday use', 'Footwear', json_build_object( 'color', 'Black', 'size', '10', 'rating', 4.2 ));
🌐
CommandPrompt Inc.
commandprompt.com › education › how-does-the-json_build_object-function-work-in-postgresql
How Does the JSON_BUILD_OBJECT() Function Work in PostgreSQL — CommandPrompt Inc.
August 14, 2023 - How Does the JSON_BUILD_OBJECT() Function Work in PostgreSQL? The JSON_BUILD_OBJECT() function is used to create and retrieve a JSON object from a variadic parameter list of key-value pairs. Use the below-stated syntax to build a new JSON Object from the specified values: ... It accepts mandatory argument “values” that can be of any data type. ... This example demonstrates the usage of the PostgreSQL JSON_BUILD_OBJECT() function to create a new JSON object from the given values:
Address: 2950 Newmarket ST STE 101 - 231, 98226, Bellingham
🌐
Neon
neon.com › postgresql › json-functions › jsonb_build_object
PostgreSQL jsonb_build_object() Function
Here’s the basic syntax of the jsonb_build_object() function: ... In this syntax, you pass a list of alternative keys and values. The list must have an even number of elements or you’ll get an error. The function will coerce the key to text and value to a JSON value using the to_jsonb() function.
🌐
Sqliz
sqliz.com › postgresql-ref › json_build_object
PostgreSQL json_build_object() Function
The json_build_object() function evaluates each argument in the variadic parameter list, where parameters as keys are coerced to text, and parameters as values ​​are converted to JSONB values ​​according to to_jsonb(). PostgreSQL will give an error if the number of arguments in the variadic parameter list is not even. This example shows how to use the PostgreSQL json_build_object() function to build a JSONB object.
🌐
Mitchellgritts
mitchellgritts.com › snippets › postgres-json-build-object
jsonb_build_object - mitchellgritts.com
For example, the PostGIS extension includes a method to convert geometries to GeoJSON. The follow takes advantage of that built in conversion: SELECT jsonb_build_object('coords', ST_GeomFromText('POINT(-71.064544 42.28787)')); -- output: -- { -- "coords": { -- "type": "Point", -- "coordinates": ...
🌐
CommandPrompt Inc.
commandprompt.com › education › how-to-use-jsonb_build_object-function-in-postgresql
How to Use jsonb_build_object() Function in PostgreSQL — CommandPrompt Inc.
November 22, 2023 - In PostgreSQL, the jsonb_build_object() function takes a heterogeneous list of parameters and converts them into JSON objects.
🌐
pgPedia
pgpedia.info › j › json_build_object.html
json_build_object() - pgPedia - a PostgreSQL Encyclopedia
postgres=# SELECT * FROM json_build_object('foo', 1, 2, row(3,'bar')); json_build_object ---------------------------------------- {"foo" : 1, "2" : {"f1":3,"f2":"bar"}} (1 row)
🌐
ThatSoftwareDude.com
thatsoftwaredude.com › content › 14125 › how-to-use-jsonbuildobject-in-postgresql-to-aggregate-data
How to Use json_build_object in PostgreSQL to Aggregate Data - ThatSoftwareDude.com
June 5, 2025 - PostgreSQL’s `json_build_object` is your go-to when you want to return structured JSON data from SQL queries, especially for APIs or for log...
🌐
Medium
medium.com › worldsensing-techblog › fancy-uses-of-json-on-postgresql-f60a7fd7b85f
Fancy uses of JSON on PostgreSQL
December 10, 2018 - You just have to pass the keys and values and you will receive a JSON object. SELECT json_build_object('id', id, 'json_data', my_json_field::jsonb, 'type', 'my_type')::jsonb FROM my_data;
Find elsewhere
🌐
GitHub
github.com › pgexperts › json_build
GitHub - pgexperts/json_build: Functions to help build JSON objects in PostgreSQL · GitHub
They can be called nested and combined, to build up complex tree structured JSON. VARIADIC "any" means that the functions will accept any number of arguments of any type, and Postgres will accept the call, although the functions themselves do enforce certain rules. If called with no arguments an empty object or array is returned.
🌐
Medium
medium.com › @e850506 › note-more-nested-json-5f3c1e4a87e
Build Nested JSON Using json_build_object and json_agg in PostgreSQL | by Unknowntpo | Medium
March 5, 2023 - select json_build_object( 'persons', ( SELECT json_agg( json_build_object( 'person_id',id, 'cars', ( SELECT json_agg( json_build_object( 'car_id', car.id, 'wheels', ( SELECT json_agg( json_build_object( 'wheel_id', wheel.id, 'whichone', wheel.whichone, 'serialnumber', wheel.serialnumber, 'car_id', wheel.carid ) ) FROM wheel WHERE wheel.carid = car.id ) ) ) FROM car WHERE id = person.id ) ) ) FROM person ) ); Small Dataset playground · Query Plan · With Left Join · With Subquery in Select List · ## Large dataset · playground · Postgresql ·
🌐
pgPedia
pgpedia.info › j › jsonb_build_object.html
jsonb_build_object() - pgPedia - a PostgreSQL Encyclopedia
postgres=# SELECT * FROM jsonb_build_object('foo', 1, 2, row(3,'bar')); jsonb_build_object ----------------------------------------- {"2": {"f1": 3, "f2": "bar"}, "foo": 1} (1 row)
🌐
PostgreSQL
postgresql.org › docs › current › functions-json.html
PostgreSQL: Documentation: 18: 9.16. JSON Functions and Operators
August 13, 2026 - To provide native support for JSON data types within the SQL environment, PostgreSQL implements the SQL/JSON data model. This model comprises sequences of items. Each item can hold SQL scalar values, with an additional SQL/JSON null value, and composite data structures that use JSON arrays and objects.
🌐
Medium
medium.com › @alemachadoaponte › transforming-sql-data-into-json-objects-in-postgresql-a-step-by-step-guide-d98f336efc7f
Transforming SQL Data into JSON Objects in PostgreSQL: A Step-by-Step Guide | by Alexandra | Medium
June 27, 2024 - The json_build_object function in PostgreSQL constructs a JSON object out of a set of key-value pairs. Each key-value pair is specified as an argument to the function. This function is particularly useful when you want to convert rows of a table ...
🌐
Neon
neon.com › docs › functions › json_object
Postgres json_object() function - Neon Docs
You could use json_object to create nested JSON objects for representing more complex data. However, since json_object only expects text values for each key, we will need to combine it with other JSON functions like json_build_object. For example:
🌐
YugabyteDB
docs.yugabyte.com › stable › api › ysql › datatypes › type_json › functions-operators › jsonb-build-object
jsonb_build_object() and json_build_object() | YugabyteDB Docs
October 15, 2024 - create function f(variadic_array_elements in text) returns jsonb language plpgsql as $body$ declare stmt text := ' select jsonb_build_object('||variadic_array_elements||')'; j jsonb; begin execute stmt into j; return j; end; $body$; -- Relies on "type t as (a int, b text)" created above.
🌐
SingleStore
docs.singlestore.com › helios › reference › sql reference › json functions › json_build_object
JSON_BUILD_OBJECT · SingleStore Helios Documentation
July 10, 2026 - If the same key is specified a multiple number of times with different values, only the last value is applied for the corresponding key value pair, while building the JSON object. ... OBJECT()does not support the following data types: BIT, SET, ENUM, and non-scalar data types (for example, an PSQL array or a ROW() function without typecasting).
🌐
Bytefish
bytefish.de › blog › postgresql_json.html
JSON Generation with PostgreSQL - bytefish.de
March 8, 2015 - So json_build_object was introduced in PostgreSQL 9.4 and it can be used to build arbitrarily complex json trees.
Top answer
1 of 3
94

Postgres 10+

Convert the whole row to a jsonb object and eliminate a single key (pg 9.5+) or an array of keys (pg 10+) with the - operator before aggregating:

SELECT val2, jsonb_agg(to_jsonb(t.*) - '{id, val2}'::text[]) AS js_34
FROM   tbl t
GROUP  BY val2;

The explicit cast in '{id, val2}'::text[] is necessary to disambiguate from the overloaded function taking a single key as text.

See:

  • Select columns inside jsonb_agg

Postgres 9.4+

jsonb_build_object() or json_build_object().

SELECT val2, jsonb_agg(jsonb_build_object('val3', val3, 'val4', val4)) AS js_34
FROM   tbl 
GROUP  BY val2;

The manual:

Builds a JSON object out of a variadic argument list. By convention, the argument list consists of alternating keys and values.

Postgres 9.3+

to_jsonb() (or to_json) with a ROW expression does the trick. (Or row_to_json() with optional line feeds):

SELECT val2, jsonb_agg(to_jsonb((val3, val4))) AS js_34
FROM   tbl
GROUP  BY val2;

But you lose original column names. A cast to a registered row type avoids that. (The row type of a temporary table serves for ad hoc queries, too.)

CREATE TYPE foo AS (val3 text, val4 text);  -- once in the same session
SELECT val2, jsonb_agg((val3, val4)::foo) AS js_34
FROM   tbl
GROUP  BY val2;

Or use a subselect instead of the ROW expression. More verbose, but without type cast:

SELECT val2, jsonb_agg(to_jsonb((SELECT t FROM (SELECT val3, val4) t))) AS js_34
FROM   tbl
GROUP  BY val2;

to_jsonb() is an optional addition to add (insignificant) line breaks in the JSON document.

More in Craig's related answer:

  • Using row_to_json() with nested joins

fiddle
Old sqlfiddle

2 of 3
0

to_json with array_agg with composite type

begin;
create table  mytable(
id bigint, value_two text, value_three text, value_four  text);
insert into mytable(id,value_two, value_three,value_four)
values
 ( 1, 'a',       'A',           'AA'),
  (2, 'a'    ,     'A2'  ,       'AA2'),
  (3, 'b'  ,       'A3',         'AA3'),
 ( 4, 'a'   ,      'A4',          'AA4'),
  (5, 'b' ,        'A5',          'AA5');
commit;
create type mytable_type as (value_three text, value_four text);

select value_two,
       to_json( array_agg(row(value_three,value_four)::mytable_type))
from mytable
group by 1;