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

so you want a SQL query that turns the result into a JSON value. Do you want one gigantic JSON array with all stores and products, or one row (JSON) per store? — , Nov 05 '22 at 09:33
@a_horse_with_no_name every store id is unique ( so just a single store ). I want something like that: `{id: ..., name: "name", products: [{...}, {...}]}` — Redy, Nov 05 '22 at 20:25

score 2 · Accepted Answer · answered Nov 05 '22 at 21:27

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

Ehab · Answer 2 · 2022-11-05T22:21:45.970

This is not an Answer.

In MySql you can use query like that :

SELECT 
  CONCAT("{id:'", s.id, "', Name:'" , s.`Name`, "', products: [", 
  GROUP_CONCAT(CONCAT("{id:'",p.id,"', Name:'", p.`Name`, "'} "))
  ,"]}")
FROM
  store AS s, product AS p
WHERE
  (s.id = p.store_id) AND (s.id=1)
GROUP BY
  s.Id

If you inserted data like :

INSERT INTO store VALUES 
(1, 'Store1'), 
(2, 'Store2');

INSERT INTO product VALUES 
(1, 'P1', 1), 
(2, 'P2', 1), 
(3, 'P3', 1), 
(4, 'P4', 2);

you will get :

{id:'1', Name:'Store1', products: [{id:'1', Name:'P1'},{id:'2', Name:'P2'},{id:'3', Name:'P3'}]}

Notes:

Data filtered for store 1. You can remove AND (s.id=1) or modify it.
Tested on MySql4 (you included MySql tag).

In PostgreSQL you use ARRAY_TO_STRING() and ARRAY_AGG() functions instead of GROUP_CONCAT() in MySql. I don't have PostgreSQL to test.

SELECT 
  CONCAT(
    "{id:'", s.id, "', Name:'" , s.`Name`, "', products: [", 
    ARRAY_TO_STRING(
      ARRAY_AGG("{id:'",p.id,"', Name:'", p.`Name`, "'} ")
    )
    ,"]}"
  )
FROM
  store AS s, product AS p
WHERE
  (s.id = p.store_id) AND (s.id=1)
GROUP BY
  s.Id

Prior to version 8.4, you would have had to define the function ARRAY_AGG before using it:

CREATE AGGREGATE ARRAY_AGG (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);

I found this on this Site

`array_to_string(array_agg(...))` can be simplified to `string_agg(...)` — , Nov 05 '22 at 08:44
Thanks! This should work but I get `function array_agg(unknown, uuid, unknown, text, unknown) does not exist`. I also get that error when using `string_agg(...)`, and I am running version 14.1 of PostgreSQL. I tried to cast the parameters to `::text` but it still says `function array_agg(text, text, text...) does not exist` — Pierre, Nov 05 '22 at 17:00
@Pierre: [as documented in the manual](https://www.postgresql.org/docs/current/functions-aggregate.html#id-1.5.8.27.5.2.4.18.1.1.1) `string_agg()` only takes two arguments not 5 — , Nov 05 '22 at 21:28

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

2 Answers2

This is not an Answer.