使用json_array_elements()用JSON陣列的元素分解行

我有一個以下格式的表格，只有一列。總共有大約 700 個條目，這里有 5 個示例：

{"year":"2021","category":"chemistry","laureates":[{"id": "1002", "firstname": "Benjamin", "surname": "List", "motivation": "\"for the development of asymmetric organocatalysis\"", "share": "2"}, {"id": "1003", "firstname": "David", "surname": "MacMillan", "motivation": "\"for the development of asymmetric organocatalysis\"", "share": "2"}],"reason":null}
{"year":"2021","category":"economics","laureates":[{"id": "1007", "firstname": "David", "surname": "Card", "motivation": "\"for his empirical contributions  to labour economics\"", "share": "2"}, {"id": "1008", "firstname": "Joshua", "surname": "Angrist", "motivation": "\"for their methodological contributions to the analysis  of causal relationships\"", "share": "4"}, {"id": "1009", "firstname": "Guido", "surname": "Imbens", "motivation": "\"for their methodological contributions to the analysis  of causal relationships\"", "share": "4"}],"reason":null}
{"year":"2021","category":"literature","laureates":[{"id": "1004", "firstname": "Abdulrazak", "surname": "Gurnah", "motivation": "\"for his uncompromising and compassionate penetration of the effects of colonialism and the  fate of the refugee in the gulf between cultures and continents\"", "share": "1"}],"reason":null}
{"year":"2021","category":"peace","laureates":[{"id": "1005", "firstname": "Maria", "surname": "Ressa", "motivation": "\"for their efforts to safeguard freedom of expression, which  is a precondition for democracy and lasting peace\"", "share": "2"}, {"id": "1006", "firstname": "Dmitry", "surname": "Muratov", "motivation": "\"for their efforts to safeguard freedom of expression, which  is a precondition for democracy and lasting peace\"", "share": "2"}],"reason":null}
{"year":"2021","category":"physics","laureates":[{"id": "999", "firstname": "Syukuro", "surname": "Manabe", "motivation": "\"for the physical modelling of Earth’s climate, quantifying variability and reliably predicting global warming\"", "share": "4"}, {"id": "1000", "firstname": "Klaus", "surname": "Hasselmann", "motivation": "\"for the physical modelling of Earth’s climate, quantifying variability and reliably predicting global warming\"", "share": "4"}, {"id": "1001", "firstname": "Giorgio", "surname": "Parisi", "motivation": "\"for the discovery of the interplay of disorder and fluctuations in physical systems from atomic to planetary scales\"", "share": "2"}],"reason":"for groundbreaking contributions to our understanding of complex physical systems"}

我想以這種格式列印輸出：

我能夠通過以下查詢獲得上面的輸出：

SELECT tuple->'year' AS year, tuple->'category' AS category,json_array_elements(tuple->'laureates') FROM prizes LIMIT 5;

我想要的是使用如下查詢輸出表中的所有值：

SELECT tuple->'year' AS year, tuple->'category' AS category,json_array_elements(tuple->'laureates') FROM prizes;

當我運行此查詢時，我收到此錯誤：

cannot call json_array_elements on a scalar

我不明白為什么會發生這種情況以及我將如何輸出所有可能的元素。

uj5u.com熱心網友回復：

如果您使用的是 PostGreSQL 版本 12 或更高版本，那么您可以使用jsonb_path_query而不是json_array_elements這樣：

SELECT tuple->>'year'
     , tuple->>'category'
     , jsonb_path_query((tuple->'laureates') :: jsonb, '$[*]')
  FROM prizes

jsonb_path_query當它是一個 json 陣列時會崩潰，tuple->'laureates'但當它不是一個 json 陣列時不會失敗tuple->'laureates'。

在dbfiddle中查看測驗結果。

請參閱手冊中的 SQL/JSON 路徑語言。

uj5u.com熱心網友回復：

由于json_array_elements()只能用于 JSON陣列、拆分案例和UNION ALL：

SELECT tuple->'year' AS year, tuple->'category' AS category, json_array_elements(tuple->'laureates') AS r
FROM   prizes
WHERE  json_typeof(tuple->'laureates') = 'array'
UNION ALL
SELECT tuple->'year' AS year, tuple->'category' AS category, tuple->'laureates'
FROM   prizes
WHERE  json_typeof(tuple->'laureates') IS DISTINCT FROM 'array';

手冊：

json_typeof( json) → text
[...]

以文本字串形式回傳頂級 JSON 值的型別。可能的型別是object、array、string、number、boolean和null。（null結果不應與 SQL NULL 混淆；[...]）

注意IS DISTINCT FROM在第二條腿中的使用。運算子<>將消除帶有(tuple->'laureates') IS NULL.

jsonb_path_query() 在 Postgres 12 或更高版本中

jsonb_path_query()我們不需要拆分案例（就像Edouard 建議的那樣）。但是要包含沒有 的行laureate，我們需LEFT JOIN要這樣做：

SELECT p.id, p.tuple->>'year' AS year, p.tuple->>'category' AS category, l.r
FROM   prizes p
LEFT   JOIN LATERAL jsonb_path_query((tuple->'laureates')::jsonb, '$[*]', '{"foo":"bar"}') AS l(r) ON true;

db<>在這里擺弄

看：

• LATERAL JOIN 和 PostgreSQL 中的子查詢有什么區別？

標籤：

上一篇：使用Pandas將CSV塊附加到資料庫

下一篇：找到另一個表中的匹配項時更新列