这个Supabase查询在SQL（PostgreSQL）中的等价语句是什么？

Question

我有一张名为“store”的表格和一张名为“product”的表格。每个商店拥有多个产品，但一个产品只属于一个商店（一对多的关系）。表格可能如下所示：

store: id，name product: id，name，store_id

当使用Supabase查询商店及其产品时，我只需要这样做：

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

这将返回

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

根据我需要的数据，我希望能够运行类似这样的查询。然而，我需要在纯SQL中运行此查询，但我似乎无法解决它。

我尝试了将表连接起来，但由于商店的数据在所有行中都存在，导致了大量重复的数据。

- Pierre

---

你需要一个能将结果转化为JSON值的SQL查询。你是想要一个带有所有商店和产品的巨大的JSON数组，还是每个商店都对应一行（JSON）数据？ - user330315

@a_horse_with_no_name 每个商店ID都是唯一的（因此只有一个商店）。我想要这样的东西：{id: ..., name: "name", products: [{...}, {...}]} - Redy

每个商店只有一个 JSON 值？ - user330315

Answer 1

这将为每个商店生成一个JSON值：

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

- user330315

Answer 2

这不是一个答案。

在MySql中，您可以使用类似以下的查询：

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

如果您插入了数据，例如：

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

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

你将会得到：

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

注意事项：

1. 数据已经过筛选，只针对商店1。您可以删除 AND (s.id=1) 或进行修改。
2. 已在MySql4上进行了测试（您包含了MySql标签）。

在PostgreSQL中，您需要使用 ARRAY_TO_STRING() 和 ARRAY_AGG() 函数来代替 MySql 中的 GROUP_CONCAT() 函数。但我没有 PostgreSQL 进行测试。

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

在8.4版本之前，您必须在使用ARRAY_AGG函数之前定义它：

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

我在网站上找到了这个。

- Ehab

---

array_to_string(array_agg(...)) 可以简化为 string_agg(...)。 - user330315

谢谢！这应该可以工作，但我得到了“function array_agg(unknown, uuid, unknown, text, unknown) does not exist”的错误。当使用string_agg（...）时，我也会遇到这个错误，并且我正在运行PostgreSQL的14.1版本。我尝试将参数转换为::text，但它仍然显示“function array_agg(text, text, text...) does not exist”。 - Pierre

@Pierre：根据手册记录，string_agg()只接受两个参数，而不是五个。 - user330315