在沒有 crosstab() 和 tablefunc 的情況下將 JSON 解壓縮為 14 列？

在 Postgres 12 數據庫中，我有一個包含 JSON 列的簡單模式。JSON 將始終包含一個對象，並且在對象內部始終有 14 個鍵，如下所示：

CREATE TABLE json_unpacking (foo text, bar json);
INSERT INTO json_unpacking VALUES
(
'ziggy',
'{"Baz (1998-03-12)":1.4285714286,"Baz (1998-03-13)":7.1428571429,"Baz (1998-03-14)":10.8571428571,"Baz (1998-03-15)":11.2857142857,"Baz (1998-03-16)":12.0,"Baz (1998-03-17)":18.1428571429,"Baz (1998-03-18)":2.0,"Baz (1998-03-19)":2.5714285714,"Baz (1998-03-20)":12.2857142857,"Baz (1998-03-21)":3.2857142857,"Baz (1998-03-22)":5.5734285714,"Baz (1998-03-23)":4.1428571429,"Baz (1998-03-24)":11.2857142857,"Baz (1998-03-25)":6.0}'
),
(
'zoggy',
'{"Baz (1998-04-12)":1.7285714286,"Baz (1998-04-13)":6.8578886549,"Baz (1998-04-14)":6.8574428571,"Baz (1998-04-15)":21.2857142857,"Baz (1998-04-16)":7.0,"Baz (1998-04-17)":3.1128571429,"Baz (1998-04-18)":5.0,"Baz (1998-04-19)":7.2214285714,"Baz (1998-04-20)":42.6857142857,"Baz (1998-04-21)":2.9857142857,"Baz (1998-04-22)":5.5734285714,"Baz (1998-04-23)":4.1428571429,"Baz (1998-03-24)":6.7537146517,"Baz (1998-04-25)":47.0}'
)
;

我可以很好地查詢這個表，例如：

SELECT *
FROM json_unpacking

產生您期望的輸出：

現在，我想查詢這個表並生成一個總共有 15 列的結構："foo"加上 14 個 like "bar 1", "bar 2", ... "bar 14"。

JSON 結構將始終包含一個字元串模式（包括日期）和一個數值。我不想要鍵值，但我需要將數值轉換為列。

換句話說，我想要的輸出結構是這樣的：

我想我有兩個問題：

1. 這可能嗎？
2. 可以在沒有任何擴展的情況下完成嗎？

一位同事建議可能需要crosstab()tablefunc 擴展中的函式。我嘗試了各種 Postgres JSON 函式，但只成功地獲取了鍵（例如 using json_object_keys()）或 null 值（使用json_to_record()）。

這是問題的 DBfiddle：https ://dbfiddle.uk/?rdbms=postgres_12&fiddle=22e9c99a954eafdadcde657b38f41b3a

您可以使用 JSON 路徑查詢：

select foo, 
      items ->> 0 as bar_1, 
      items ->> 1 as bar_2, 
      items ->> 1 as bar_3, 
      .....
from ( 
 select foo, jsonb_path_query_array(bar::jsonb, '$.*') as items
 from json_unpacking
) as t 

線上範例

---

請注意，建議使用jsonbover json（在這種情況下，它會避免演員表）

使用解壓 JSON 以分隔行

SELECT json_unpacking.foo, 
      values.value, 
      ROW_NUMBER() OVER (PARTITION BY foo) value_number
FROM json_unpacking
CROSS JOIN json_each(json_unpacking.bar) values

然後使用分組和條件聚合將其組合回所需的行結構。

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