使用PostgreSQL10根據行中的值分層聚合JSON

我有一個用作“字典”的 PostgreSQL 10 表，其結構如下：

現在我想知道是否有一種方法可以使用表中的值構建 JSON，它會根據“key”的值構建層次結構？就像是：

style --> selection --> color 和 style --> line --> color

以 JSON 結尾：

{
  style: [
    selection: {
      color: "...",
      weight: "..."
    },
    line: {
      color: "...",
      weight: "..."
    }
  ]
}

這樣的壯舉可以實作嗎？如果是這樣，我會怎么做？

是否可以這樣做，以便無論我的表中有什么鍵，它總是回傳正確構建的 JSON？

提前致謝

uj5u.com熱心網友回復：

PosGres 10 及更高版本的作業解決方案

我建議你一個通用的解決方案，將key資料轉換為text[]型別，以便它可以jsonpath在標準jsonb_set()函式中使用。

但是由于我們將迭代jsonb_set()函式，我們首先需要創建一個與該aggregate函式關聯的函式：

CREATE AGGREGATE jsonb_set_agg(p text[], z jsonb, b boolean)
( sfunc = jsonb_set
, stype = jsonb
, initcond = '{}'
)

然后我們將key資料轉換為text[]并自動生成串列，jsonpath這將允許逐步和迭代地構建最終jsonb資料：

 SELECT i.id
      , max(i.id) OVER (PARTITION BY t.key) AS id_max
      , p.path[1 : i.id] AS jsonbpath
      , to_jsonb(t.value) AS value
   FROM mytable AS t
  CROSS JOIN LATERAL string_to_array(t.key, '_') AS p(path)
  CROSS JOIN LATERAL generate_series(1, array_length(p.path, 1)) AS i(id)

最終查詢如下所示：

WITH list AS
( SELECT i.id
       , max(i.id) OVER (PARTITION BY t.key) AS id_max
       , p.path[1 : i.id] AS jsonpath
       , to_jsonb(t.value) AS value
   FROM mytable AS t
  CROSS JOIN LATERAL string_to_array(t.key, '_') AS p(path)
  CROSS JOIN LATERAL generate_series(1, array_length(p.path, 1)) AS i(id)
)
SELECT jsonb_set_agg( l.jsonpath
                    , CASE 
                        WHEN l.id = l.id_max THEN l.value
                        ELSE '{}' :: jsonb
                      END
                    , true 
                    ORDER BY l.id
                    )
  FROM list AS l

結果與您的預期略有不同（頂級 json 陣列被 json 物件替換），但對我來說聽起來更符合邏輯：

{"style": {"line": {"color": "C"
                   , "weight": "D"
                   }
          , "selection": {"color": "A"
                         , "weight": "B"
                         }
          }
}

dbfiddle中的完整測驗結果。

uj5u.com熱心網友回復：

好吧，我不確定 Postgres 版本，希望這適用于您的版本，我在版本 11 上嘗試過。

        ;WITH dtbl as (
    select split_part(tbl.col, '_', 1) as style,
           split_part(tbl.col, '_', 2) as cls,
           split_part(tbl.col, '_', 3) as property_name,
           tbl.val
    from (
             select 'style_selection_color' as col, 'red' as val
             union all
             select 'style_selection_weight', '1rem'
             union all
             select 'style_line_color', 'gray'
             union all
             select 'style_line_weight', '200'
             union all
             select 'stil_line_weight', '200'
         ) as tbl
),
     classes as (
         select dtbl.style,
                dtbl.cls,

                (
                    SELECT json_object_agg(
                                   nested_props.property_name, nested_props.val
                               )
                    FROM (
                             SELECT dtbl2.property_name,
                                    dtbl2.val
                             FROM dtbl dtbl2
                             where dtbl2.style = dtbl.style
                               and dtbl2.cls = dtbl.cls
                         ) AS nested_props
                ) AS properties

         from dtbl
         group by dtbl.style, dtbl.cls),
     styles as (
         select style
         from dtbl
         group by style
     )
        ,
     class_obj as (
         select classes.style,
                classes.cls,
                json_build_object(
                        classes.cls, classes.properties) as cls_json
         from styles
                  join classes on classes.style = styles.style
     )
select json_build_object(
               class_obj.style,
               json_agg(class_obj.cls_json)
           )
from class_obj
group by style
    ;

如果您更改查詢的第一部分以匹配您的表名和列名，這應該可以作業。這個想法是構建嵌套的 json 物件，但你不能一次完成，因為它不允許你嵌套json_agg函式，這就是為什么我們必須使用超過 1 個查詢。首先構建線條和選擇物件，然后將它們聚合到樣式物件中。對不起命名，這是我能做的最好的。

EDIT1：這是該查詢的輸出。

"{""stil"" : [{""line"" : [{""weight"" : ""200""}]}]}"
"{""style"" : [{""selection"" : [{""color"" : ""red""}, {""weight"" : ""1rem""}]}, {""line"" : [{""color"" : ""gray""}, {""weight"" : ""200""}]}]}"

看看這個輸出，它不是你想要的，你有一個屬性物件陣列:) 你想要 {"color":"red", "weight": "1rem"} 但輸出是 [{"color ":"red"}, {"weight": "1rem"}]

EDIT2：嗯，json_object_agg 是解決方案，所以我結合 json_object_agg 來構建道具物件，現在我認為這可能會變得更簡單。這是查詢的新輸出。

"{""stil"" : [{""line"" : { ""weight"" : ""200"" }}]}"
"{""style"" : [{""selection"" : { ""color"" : ""red"", ""weight"" : ""1rem"" }}, {""line"" : { ""color"" : ""gray"", ""weight"" : ""200"" }}]}"

標籤：

上一篇：獲取另一列中每個值的最新和第二個最新日期

下一篇：根據創建日期選擇不同的最新記錄