PostgreSQL: Combine two JSON objects into a single JSON object

Question

I am using PostgreSQL 15 and have the following two statements in PL/pgSQL:

-- Statement 1
SELECT to_json(T) 
FROM
(SELECT *
FROM category
WHERE category.id = 43) T)

-- Statement 2
SELECT json_build_object('products', JSON_AGG(T))
FROM
(SELECT *
FROM product
WHERE category_id = 43) T

The results:

Statement 1:

{"id": 43, "name": "television", "description": "flat screen"}

Statement 2:

{
    "products": [{
        "id": 423, 
        "category_id": 43, // Foreign Key
        "manufacture": "LG"
    }, {
        "id": 424, 
        "category_id": 43,
        "manufacture": "Sony"
    }, {
        "id": 425, 
        "category_id": 43,
        "manufacture": "Samsung"
    }]
}

I need to combine both of the JSONs and get the following desired result:

{
    "id": 43,
    "name": "television",
    "description": "flat screen",
    "products": [{
        "id": 423, 
        "category_id": 43,
        "manufacture": "LG"
    }, {
        "id": 424, 
        "category_id": 43,
        "manufacture": "Sony"
    }, {
        "id": 425, 
        "category_id": 43,
        "manufacture": "Samsung"
    }]
}

The JSON above is inserting the second JSON into the first one.

I tried ||, but that resulted in an array. I need a JSON/JSONB object, not an array.

I also tried SELECT jsonb_build_object, but that requires a key in front of the first JSON so that each JSON will have a key in front - not the desired result.

How can I achieve the desired result?

Answer 1

It works for me:

SELECT to_json(c.*)::jsonb || json_build_object('products', json_agg(p.*))::jsonb
FROM category c
INNER JOIN product p on p.category_id = c.id
WHERE c.id = 43
GROUP BY c.*