如何從 id 數組中返回多列？

我有兩個表，如下所示：

workshops table
+----+--------------+------------+
| id |     name     | option_ids |
+----+--------------+------------+
|  1 | Conversation | {2,3}      |
+----+--------------+------------+

options table
+----+-----------+---------------+
| id |    day    |     time      |
+----+-----------+---------------+
|  1 | Monday    | 13:00 - 14:00 |
|  2 | Tuesday   | 12:00 - 13:00 |
|  3 | Wednesday | 11:00 - 12:00 |
+----+-----------+---------------+

我想建構一個查詢，在其中檢索車間名稱以及 option_ids 2 和 3 的所有資訊。

我想最終像這樣構造一個JSON：

{
   name: "Conversation",
   options: [
       { day: "Tuesday", time: "12:00 - 13:00" }, 
       { day: "Wednesday", time: "11:00 - 12:00" }
   ]

}

查看 postgresql.org 文件可能與json_object函式有關？

很少有像這個查詢這樣的內置函式：

select json_build_object(
  'name', w.name, 
  'options', (
      select json_agg(json_build_object('day', o.day, 'time', o.time))       
      from options o 
      where o.id =any(w.option_ids)  -- I am assuming option_ids is an array, not string
  )
) as result 
from workshops w;

將建構一個像這樣的json：

{"name" : "Conversation", "options" : [{"day" : "Tuesday", "time" : "12:00 - 13:00"}, {"day" : "Wednesday", "time" : "11:00 - 12:00"}]}

是否可以通過所有列名自動構造“選項”屬性？

是的：

SELECT row_to_json(w) AS workshop
FROM  (
  SELECT w.name, (SELECT json_agg(o) FROM options o WHERE o.id = ANY(w.option_ids)) AS options
  FROM   workshops w
  ) w;

值得注意的是，“all”表示 table 中的所有列options，包括id.

要僅包括原始問題中的選擇列：

SELECT row_to_json(w) AS workshop
FROM  (
  SELECT w.name, (SELECT json_agg(o) FROM (
                     SELECT o.day, o.time FROM options o
                     WHERE  o.id = ANY(w.option_ids)
                     ) o
                 ) AS options
  FROM   workshops w
  ) w

db<>在這裡擺弄

兩者都適用於 Postgres 9.3 或更高版本。json_build_object()與 Postgres 9.4 一起添加。看：

• 選擇 json_agg 中的列
• Postgres多列到json

引用自：https://dba.stackexchange.com/questions/299350