選擇 json_agg 中的列

我有一個像這樣的查詢：

SELECT a.id, a.name, json_agg(b.*) as "item"
 FROM a
 JOIN b ON b.item_id = a.id
GROUP BY a.id, a.name;

如何選擇JSON 對像中b沒有的列？b.item_id

我已經閱讀過ROW，但它返回一個 JSON 對象，如：

{"f1": "Foo", "f2": "Bar"}

一旦獲取到匹配正確的列鍵，我將需要重新映射 JSON 對象。我想避免這種情況並保留原始列名。

不幸的是，SQL 語法中沒有規定說*“除這一列之外的所有列”*。您可以通過在行類型表達式中拼出剩餘的列列表來實現您的目標：

SELECT a.id, a.name
    , json_agg(**(b.col1, b.col2, b.col3)**) AS item
FROM   a
JOIN   b ON b.item_id = a.id
GROUP  BY a.id, a.name;

這是更明確形式的縮寫：.ROW(b.col1, b.col2, b.col3)

但是，列名稱不會保留在行類型表達式中。您可以通過這種方式在 JSON 對像中獲得通用鍵名。我看到 3 個選項來保留原始列名：

1. 轉換為註冊類型

轉換為眾所周知的（註冊的）行類型。為每個現有的表或視圖或使用顯式CREATE TYPE語句註冊類型。您可以將臨時表用於臨時解決方案（在會話期間有效）：

CREATE TEMP TABLE x (col1 int, col2 text, col3 date);  -- use adequate data types!

SELECT a.id, a.name
    , json_agg(**(b.col1, b.col2, b.col3)::x**) AS item
FROM   a
JOIN   b ON b.item_id = a.id
GROUP  BY a.id, a.name;

2.使用子選擇

使用子選擇構造一個派生表並****作為一個整體引用該表。這也帶有列名。它更冗長，但您不需要註冊類型：

SELECT a.id, a.name
    , json_agg((**SELECT x FROM (SELECT b.col1, b.col2, b.col3) AS x**)) AS item
FROM   a
JOIN   b ON b.item_id = a.id
GROUP  BY a.id, a.name;

3.json_build_object()在 Postgres 9.4 或更高版本中

SELECT a.id, a.name
    , json_agg(**json_build_object('col1', b.col1, 'col2', b.col2, 'col3', b.col3)**) AS item
FROM   a
JOIN   b ON b.item_id = a.id
GROUP  BY a.id, a.name;

有關的：

• 在 SQL (Postgres) 中作為 JSON 對像數組返回

**jsonb**與各自的功能類似jsonb_agg()和jsonb_build_object()。

對於Postgres 9.5或更高版本，還請參閱a_horse 的答案，其中包含一個新的更短的語法變體：Postgres 添加了減號運算符-來jsonb表示*“all keys except this one key”*。

由於Postgres 10 *“除了幾個鍵”*是使用作為第二個操作數的相同運算符實現的text[]- 就像 mlt 評論的那樣。

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