在 PostgreSQL 中查詢 JSON 數組

我正在使用的數據位於 JSONB 類型的列（“事務”）中，包含與此類似的文件{"transactions":[{"id":"ABC123", ...}, {"id":"DEF456", ...}, {"id": "GHI789", ...}]}

我正在嘗試獲取不同的事務 ID，但由於該"id"欄位是雜湊的一部分，在數組內部，我正在努力以有用的格式獲取數據。

我有一個解決方案可以給我一些回報，但感覺很笨拙，作為 Postgres 的新手，我覺得我沒有走在正確的軌道上：

我在 Python 應用程序中使用它，正在執行sqlalchemy，但為了掌握數據，我在psql終端中建構了以下內容：

WITH orders AS (SELECT transaction->'transactions' AS t FROM transactions) SELECT jsonb_array_elements(t)->>'id' FROM orders;

這將返回我從嵌套雜湊中提取的 JSON 項的行集。但是，當嘗試在其中執行時，sqlalchemy我得到以下資訊：

>>> query = sqlalchemy.text("WITH orders AS (SELECT transaction->'transactions' AS t FROM transactions) SELECT jsonb_array_elements(t)->>'id' FROM orders")
>>> rowset = engine.execute(query)
>>> rowset.fetchall()
[(u'ABC123',), (u'DEF456',), (u'GHI789',)]

…意味著我需要從sqlalhemy響應中解構更多嵌套類型。總體感覺非常緊密。

有沒有更好的方法來查詢這個嵌套數據？

您可以在沒有 CTE 的情況下更簡單地編寫查詢，這可能會執行得更好。

select (jsonb_array_elements(transaction->'transactions')->'id')::text as trx_id 
from transactions;

SQL alchemy 返回一個元組列表。這是標準行為。PostgreSQL 中的一行表示為一個元組。即使您查詢單個列，它也將是一個包含一個元素的元組。因此(u'ABC123',)代表。

如果您需要第一列的列表，您可以輕鬆地在 python 中轉換您的結果集：

[r[0] for r in rowset.fetchall()]

這應該返回

['ABC123', 'DEF456', 'GHI789']

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