Build a Product JSON Object — JSONB Aggregation in SQL

The problem

Brightlane's product detail API needs a single product returned as a structured JSON document.

Write a query to return a JSON object for product id = 7 containing the product's ID, name, and price under the keys 'id', 'name', and 'price'.

Assumptions:

The products table has one row per product with an id, a name, and a price.
Product id = 7 is on record. The output is a single row holding the assembled JSON document.

Output:

A single row with one column, product_doc, containing a JSON object with the keys 'id', 'name', and 'price'.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  JSON_BUILD_OBJECT('id', id, 'name', name, 'price', price) AS product_doc
FROM
  products
WHERE
  id = 7

The shape

json_build_object('id', id, 'name', name, 'price', price) packs the three columns of a single product row into a JSON object whose keys are the literal strings and whose values are the column values. The WHERE id = 7 restricts the input to that one product, so the function runs once and the query returns one row.

Clause by clause

SELECT json_build_object('id', id, 'name', name, 'price', price) AS product_doc lists the keys and values in alternating order: key, value, key, value. PostgreSQL infers each value's JSON type from the column type, so id and price come out as JSON numbers and name comes out as a JSON string. The alias product_doc names the single output column.
FROM products reads the product records.
WHERE id = 7 narrows the input to the one product with id = 7, which is Crest Air at price 1299. With only one row reaching SELECT, the function builds exactly one JSON object and the result set is the single row { "id": 7, "name": "Crest Air", "price": 1299 }.

You practiced json_build_object('key1', value1, 'key2', value2, ...) — assemble a JSON object from alternating key-value arguments; PostgreSQL infers each value's JSON type.

Return a JSON object for product `id = 7` containing the product's ID, name, and price under the keys `'id'`, `'name'`, and `'price'`

The shape

Clause by clause

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.