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

Using `json_build_object` to join nested properties – Postgresql

Suppose the following,

create table product(id serial primary key);
create table product_image(id serial primary key, product_id bigint references product(id));
create table chat(id serial primary key, product_id bigint references product(id));

insert into product values(1), (2), (3);
insert into product_image values(30, 1), (31, 1);
insert into chat values(50, 1), (51, 2);

I’m expecting the following result:

 chat_id |  product                                 |
---------+------------------------------------------+
      50 |  {"id":1,"images":[{"id":30},{"id":31}]} |
      51 |  {"id":2,"images":[]}                    |

Here’s what I’ve tried,

select
  c.id as chat_id,
  json_build_object(
    'id', p.id,
    'images', to_jsonb(
        select * from product_image pi
        join product p on p.id = pi.product_id
    )
  ) as product
from chat c
join product p on p.id = c.product_id
join product_image pi on pi.product_id = p.id

I feel like I’m close, but I keep catching unexpected results.

What am I doing wrong here?

Tags:

2

Answers


  1. 0

    Your subquery is not joined to your outer query. The imo easiest and most straightforward way to achieve this is using only subqueries, using WHERE conditions to select only those rows that match the outer object:

    SELECT
  c.id as chat_id,
  (
    SELECT jsonb_build_object(
      'id', p.id,
      'images', ARRAY(
        SELECT jsonb_build_object(
          'id', pi.id
        )
        FROM product_image pi
        WHERE pi.product_id = p.id
      )
    )
    FROM product p
    WHERE p.id = c.product_id
  ) AS product
FROM chat c

    (online demo)

    Instead of the ARRAY(…) subquery syntax, you can also use jsonb_agg (demo), but it returns null instead of an empty array when there are no elements.

    Login or Signup to reply.
  2. 0

    You can do it using json_build_object and json_agg as follows :

      select
  c.id as chat_id,
  json_build_object(
    'id', p.id,
     'images',  COALESCE(json_agg(
                             json_build_object(
                                  'id', pi.id
                             ) ) FILTER (WHERE pi.id IS NOT NULL), '[]'
                        )
  ) as productt
  from chat c
  left join product p on p.id = c.product_id
  left join product_image pi on pi.product_id = p.id
  group by c.id, p.id

    Demo here

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

Back To Top
Search