Postgres jsonbuildobject() 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.
Function signature
Section titled “Function signature”json_build_object ( VARIADIC "any" ) → jsonjson_build_object example
Section titled “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
Section titled “Advanced examples”Nested objects with json_build_object
Section titled “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
Section titled “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
Section titled “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
Section titled “Additional considerations”Performance and indexing
Section titled “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
Section titled “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.