What's the equivalent of this Supabase query in SQL ( Postgresql )

Question

I have a table "store" and a table "product". Each store has multiple products but a product only has one store ( one to many relationship ). The tables might look something like:

store: id, name
product: id, name, store_id

when querying the store and its products using supabase I simply do:

    .from("store")
    .select(
        id
        name,
        product(name)
    )

which would return

    id: "some_id",
    name: "some_name",
    products: [
        {...},
        {...}
     ] 
}

or something along those lines depending on what data I want. However, I need to run this query in pure SQL and I can't seem to figure it out.

I tried to JOIN the tables together but it leads to a lot of duplicated data since the store's data is in all the rows

Answer 1

This will generate one JSON value per store:

select to_jsonb(s)||jsonb_build_object('products', p.products)
from store s
  join (
     select p.store_id, jsonb_agg(to_jsonb(p) - 'store_id' order by p.store_id) products
     from product p
     group by p.store_id
  ) p on p.store_id = s.id
;

Online example