如何取消嵌套和分組 JSON 數組的元素？

給定band表格，其中有一json列包含一個數組：

id | people
---+-------------
1  | ['John', 'Thomas']
2  | ['John', 'James']
3  | ['James', 'George']

如何列出每個名字所屬的樂隊數量？

期望的輸出：

name   | count
-------+------------
John   | 2
James  | 2
Thomas | 1
George | 1

列的數據類型people是json，結果也是json_array_elements(people)。=並且數據類型沒有相等運算符 ( ) json。所以你也不能GROUP BY在上面執行。更多的：

• 如何從 JSON 中刪除已知元素$$ $$PostgreSQL中的數組？

jsonb有一個相等運算符，因此您的答案中的“解決方法”是轉換為jsonb並使用等效的jsonb_array_elements(). 演員表增加了成本：

jsonb_array_elements(people::jsonb)

從 Postgres 9.4 開始，我們也json_array_elements_text(json)將數組元素返回為text. 有關的：

• 如何將 JSON 數組轉換為 Postgres 數組？

所以：

SELECT p.name, count(*) AS c
FROM   band b, json_array_elements_text(b.people) p(name)
GROUP  BY p.name;

text將名稱作為對象而不是jsonb對象（在文本表示中雙引號）似乎更方便，並且您的“所需輸出”表明您希望/需要text以結果開頭。

GROUP BYon textdata 也比 on 便宜jsonb，因此這種替代“解決方法”應該更快，原因有兩個。（用 測試EXPLAIN (ANALYZE, TIMING OFF)。）

作為記錄，您的原始答案沒有任何問題。逗號 ( ,) 與 . 一樣“正確” CROSS JOIN LATERAL。之前在標準 SQL 中定義過並不會使其遜色。看：

• 做什麼$$ FROM x, y $$在 Postgres 中是什麼意思？

它對於其他 RDBMS 也不是更可移植的，並且因為jsonb_array_elements()或json_array_elements_text()不能移植到其他 RDBMS 開始，這也是無關緊要的。IMO的簡短查詢並沒有變得更清楚CROSS JOIN LATERAL，但最後一點只是我個人的看法。

我使用了更明確的表和列別名p(name)以及表限定引用p.name來防止可能的重複名稱。name是一個很常見的詞，它也可能在基礎表中作為列名彈出band，在這種情況下，它會默默地解析為band.name. 簡單的形式json_array_elements_text(people) name只附加了一個表別名，列名仍然是value，從函式返回。但在列表中使用時name會解析為單列。它恰好按預期工作。但是一個真正的列名（如果應該存在）將首先綁定。雖然在給定的範例中這不會咬人，但在其他情況下它可以是一個裝載的腳槍。value``SELECT``name``band.name

不要使用通用的“名稱”作為開頭的標識符。也許這只是為了簡單的測試案例。

---

如果該列people可以包含除普通JSON 數組之外的任何內容，則任一查詢都會觸發異常。如果您不能保證數據完整性，您可能需要使用以下方法進行防禦json_typeof()：

SELECT p.name, count(*) AS c
FROM   band b, json_array_elements_text(b.people) p(name)
**WHERE json_typeof(b.people) = 'array'**
GROUP  BY 1; -- optional short syntax since you seem to prefer short syntax

從查詢中排除違規行。

有關的：

• 我們什麼時候可以在 PostgreSQL 中使用標識符號而不是其名稱？

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