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?

score 1 · Accepted Answer · answered Aug 22 '23 at 05:55

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.*

answered Aug 22 '23 at 05:55

azakharchuk

26
2

score 1 · Answer 2 · answered Aug 22 '23 at 06:51

DB fiddle

To merge 2 json fields you can use this query. We need to convert them to jsonb type first:

with cte_1 as (
  select '{"id": 43, "name": "television", "description": "flat screen"}'::jsonb 
         as jsonb_1
),
cte_2 as (
  select '{
    "products": [{
        "id": 423, 
        "category_id": 43,
        "manufacture": "LG"
    }, {
        "id": 424, 
        "category_id": 43,
        "manufacture": "Sony"
    }, {
        "id": 425, 
        "category_id": 43,
        "manufacture": "Samsung"
    }]
}'::jsonb as jsonb_2
)
select cte_1.jsonb_1 || cte_2.jsonb_2
from cte_1, cte_2;

Also, pgtype_of() results in jsonb type.

Thank you for your reply, and while your answer works with raw `json`, I struggled to get it working using the `category` and `product` table names. — user1107173, Aug 22 '23 at 22:15

PostgreSQL: Combine two JSON objects into a single JSON object

2 Answers2