Use Neon as your dev environment for AWS RDS: accelerate your workflow while reducing costs
Neon
13.6kLog InSign Up
Postgres guides/Functions/JSON functions

Postgres json_build_object() function

Builds a JSON object out of a variadic argument list

json_build_object is used to construct a JSON object from a set of key-value pairs, creating a JSON representation of a row or set of rows. This has potential performance benefits compared to converting query results to JSON on the application side.

Try it on Neon!

Neon is Serverless Postgres built for the cloud. Explore Postgres features and functions in our user-friendly SQL editor. Sign up for a free account to get started.

Sign Up

Function signature

json_build_object ( VARIADIC "any" ) → json

json_build_object example

Let's consider a scenario where we have a table storing information about users:

users

| id |   name   | age |   city
|----|----------|-----|----------
| 1  | John Doe |  30 | New York |
| 2  | Jane Doe |  25 | London   |

Create the users table and insert some data into it:

CREATE TABLE users (
 id SERIAL PRIMARY KEY,
 name TEXT NOT NULL,
 age INTEGER,
 city TEXT
);

INSERT INTO users (name, age, city)
VALUES ('John Doe', 30, 'New York'),
      ('Jane Doe', 25, 'London');

Use json_build_object to create a JSON structure with user information:

SELECT id,
 json_build_object(
   'name', name,
   'age', age,
   'city', city
 ) AS user_data
FROM users;

This query returns the following results:

| id |                       user_data
|----|--------------------------------------------------------
| 1  | {"name" : "John Doe", "age" : 30, "city" : "New York"}
| 2  | {"name" : "Jane Doe", "age" : 25, "city" : "London"}

Advanced examples

Nested objects with json_build_object

Let’s say we have a table of products with an attributes column containing JSON data:

products

| id |    name    | price |            description            | category |                     attributes
|----|------------|-------|-----------------------------------|----------|----------------------------------------------------
| 1  | T-Shirt    | 25.99 | A comfortable cotton T-Shirt      | Clothing | {"size": "Medium", "color": "Blue", "rating": 4.5}
| 2  | Coffee Mug | 12.99 | A ceramic mug with a funny design | Kitchen  | {"size": "Small", "color": "White", "rating": 3.8}
| 3  | Sneakers   | 49.99 | Sporty sneakers for everyday use  | Footwear | {"size": "10", "color": "Black", "rating": 4.2}

Create the products table and insert some data into it:

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
   ));

Use json_build_object to build a nested JSON object that represents the details of individual products:

SELECT
   id,
   name,
   price,
   json_build_object(
       'category', category,
       'description', description,
       'attributes', json_build_object(
           'color', attributes->>'color',
           'size', attributes->>'size'
       )
   ) AS details
FROM products;

This query returns the following results:

| id |    name     | price |                                                               details
|----|-------------|-------|-------------------------------------------------------------------------------------------------------------------------------------
| 1  | T-Shirt     | 25.99 | {"category" : "Clothing", "description" : "A comfortable cotton T-Shirt", "attributes" : {"color" : "Blue", "size" : "Medium"}}
| 2  | Coffee Mug  | 12.99 | {"category" : "Kitchen", "description" : "A ceramic mug with a funny design", "attributes" : {"color" : "White", "size" : "Large"}}

Order json_build_object output

Combine json_build_object with ORDER BY to sort the results based on a specific attribute within the JSON structure.

For example, you can build a JSON structure with json_build_object from the contents of the above products table, and then order the results based on rating.

SELECT
   id,
   name,
   price,
   json_build_object(
       'category', category,
       'description', description,
       'attributes', json_build_object(
           'color', attributes->>'color',
           'size', attributes->>'size',
           'rating', attributes->>'rating'
       )
   ) AS details
FROM products_with_rating
ORDER BY (attributes->>'rating')::NUMERIC DESC;

ORDER BY was to order the results based on the descending order of rating.

This query returns the following results:

| id |    name    | price |                                                                        details
|----|------------|-------|-------------------------------------------------------------------------------------------------------------------------------------------------------
| 1  | T-Shirt    | 25.99 | {"category" : "Clothing", "description" : "A comfortable cotton T-Shirt", "attributes" : {"color" : "Blue", "size" : "Medium", "rating" : "4.5"}}
| 3  | Sneakers   | 49.99 | {"category" : "Footwear", "description" : "Sporty sneakers for everyday use", "attributes" : {"color" : "Black", "size" : "10", "rating" : "4.2"}}
| 2  | Coffee Mug | 12.99 | {"category" : "Kitchen", "description" : "A ceramic mug with a funny design", "attributes" : {"color" : "White", "size" : "Small", "rating" : "3.8"}}

Grouped json_build_object output

To create a JSON object that groups the total price for each category of products in the products table:

SELECT
   category,
   json_build_object(
       'total_price', sum(price)
   ) AS category_total_price
FROM products
GROUP BY category;

This query returns the following results:

| category |  category_total_price
|----------|-------------------------
| Kitchen  | {"total_price" : 12.99}
| Clothing | {"total_price" : 25.99}

Additional considerations

Performance and indexing

The performance of the json_build_object depends on various factors including the number of key-value pairs, nested levels (deeply nested objects can be more expensive to build). Consider using JSONB data type with jsonb_build_object for better performance.

If your JSON objects have nested structures, indexing on specific paths within the nested data can be beneficial for targeted queries.

Alternative functions

Depending on your requirements, you might want to consider similar functions:

  • json_object - Builds a JSON object out of a text array.
  • json_agg - Aggregates values, as a JSON array.
  • row_to_json - Returns a row as a JSON object.
  • json_object_agg - Aggregates key-value pairs into a JSON object.

Resources

Last updated on

Previousjsonb_array_elements
Nextjson_each
Edit this page
Was this page helpful?