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

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

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

Tags:

2

Answers


  1. 0

    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:

    1. Data filtered for store 1. You can remove AND (s.id=1) or modify it.
    2. 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

    Login or Signup to reply.
  2. 0

    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

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

Back To Top
Search