PostgreSQL 將未定義的 JSONB 欄位動態轉換為一行

我有一個類型為欄位的表jsonb，我想將欄位 jsonb 的列拆分為所有 json 鍵。此列缺少架構。例如：

從

CREATE TABLE v(id,jsonb)
 AS VALUES
   (1,'{"a":"4", "b":"5"}'::jsonb),
   (2,'{}'),
   (3,'{"a":"8", "c":"9", "d":"9"}');

到

id  |  a   |   b   |  c  |  d

1   |  4   |   5   |     |  
3   |  8   |       |  9  |  9

對於這種特定情況，一種解決方案是

select * from table, json_to_record(optional) as x("a" text, "b" text, "c" text, d text)

如您所見，鍵可能會有所不同，並且在大型數據庫中很難將所有鍵放入我的實際問題中，我有 31 個鍵，另一方面，如果我想在其他表中重用此腳本，我必須手動填寫鍵。

有沒有辦法在不手動指定鍵的情況下對 jsonb 的所有鍵進行選擇？

我的問題：有沒有辦法在不手動指定鍵的情況下對 jsonb 的所有鍵進行選擇？

不，因為沒有辦法讓查詢返回未定義的結果集。但是，如果表不接受新查詢，您可以生成動態 sql 語句。

SELECT FORMAT(
 $$ SELECT * FROM %I.%I CROSS JOIN LATERAL jsonb_to_record(%I) AS rs(%s); $$,
 'public',
 'v',
 'jsonb',
 array_to_string(
               (SELECT ARRAY(SELECT DISTINCT col FROM v CROSS JOIN LATERAL jsonb_object_keys(jsonb) AS t(col) ORDER BY col)), ' text , '
 ) || ' text'
);

然後執行該查詢，或\gexec在 psql 中執行。

                                                   format                                                    
--------------------------------------------------------------------------------------------------------------
 SELECT * FROM public.v CROSS JOIN LATERAL jsonb_to_record(jsonb) AS rs(a text , b text , c text , d text); 
(1 row)

test=# \gexec
id |             jsonb              | a | b | c | d 
----+--------------------------------+---+---+---+---
 1 | {"a": "4", "b": "5"}           | 4 | 5 |   | 
 2 | {}                             |   |   |   | 
 3 | {"a": "8", "c": "9", "d": "9"} | 8 |   | 9 | 9
(3 rows)

您可能希望也可能不希望建立一些jsonb_typeof返回 pg 類型的類型推斷：請記住，您永遠不能返回整數或其他東西，但您應該能夠將數字儲存為double precision.

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