Question 235405 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL: Combine two JSON objects into a single JSON object

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?

Tags:

2

Answers


  1. 0

    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.*
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search