Postgresql->JSON陣列到行

我的表中的一列有 JSONArray 資料。我在 jsonb 列中使用了 jsonb_agg() 來查看視圖。現在視圖中的資料如下所示

[
  {
    "Android": 2,
    "Windows": 1,
    "Macintosh": 1
  },
  {
    "iOS": 1,
    "Android": 2,
    "Windows": 2,
    "Macintosh": 2
  },
  {},
  {
    "Android": 1,
    "Windows": 1
  },
  {
    "Android": 1
  },
  {
    "iOS": 1,
    "Android": 2
  },
  {
    "iOS": 2,
    "Android": 1
  },
  {
    "iOS": 2
  },
  {
    "Android": 1
  },
  {
    "iOS": 2,
    "Windows": 1
  },
  {
    "Android": 5
  },
  {},
  {},
  {
    "iOS": 1,
    "Android": 1
  },
  {},
  {},
  {
    "Windows": 3
  }
]

但是，我需要產生以下結果

{
  "Android": 16,
  "Windows": 8,
  "Macintosh": 3,
  "iOS": 9
}

PostgreSQL 中有沒有辦法實作這一點？

uj5u.com熱心網友回復：

是的，有辦法。這里是。

select to_jsonb(t.*) from
(
 select 
    sum((j->>'Android')::numeric) "Android", 
    sum((j->>'Windows')::numeric) "Windows", 
    sum((j->>'Macintosh')::numeric) "Macintosh", 
    sum((j->>'iOS')::numeric) "iOS" 
 from jsonb_array_elements('[{"Android": 2, "Windows": 1, "Macintosh": 1}, {"iOS": 1, "Android": 2, "Windows": 2, "Macintosh": 2}, {}, {"Android": 1, "Windows": 1}, {"Android": 1}, {"iOS": 1, "Android": 2}, {"iOS": 2, "Android": 1}, {"iOS": 2}, {"Android": 1}, {"iOS": 2, "Windows": 1}, {"Android": 5}, {}, {}, {"iOS": 1, "Android": 1}, {}, {}, "Windows": 3}]'::jsonb) as j
) as t;

作為引數化查詢：

select to_jsonb(t.*) from
(
 select 
    sum((j->>'Android')::numeric) "Android", 
    sum((j->>'Windows')::numeric) "Windows", 
    sum((j->>'Macintosh')::numeric) "Macintosh", 
    sum((j->>'iOS')::numeric) "iOS" 
 from jsonb_array_elements(?::jsonb) as j
) as t;

uj5u.com熱心網友回復：

您可以組合許多json/jsonb函式來獲得結果

SELECT
    jsonb_agg(json_build_object(key, sum))
FROM (
    SELECT
        key,
        sum(value)
    FROM (
        SELECT
            (arr).key,
            (arr).value::int
        FROM (
            SELECT
                jsonb_each(j) arr
            FROM (
                SELECT
                    jsonb_array_elements('[{"Android": 2, "Windows": 1, "Macintosh": 1}, {"iOS": 1, "Android": 2, "Windows": 2, "Macintosh": 2}, {}, {"Android": 1, "Windows": 1}, {"Android": 1}, {"iOS": 1, "Android": 2}, {"iOS": 2, "Android": 1}, {"iOS": 2}, {"Android": 1}, {"iOS": 2, "Windows": 1}, {"Android": 5}, {}, {}, {"iOS": 1, "Android": 1}, {}, {}, {"Windows": 3}]
'::jsonb) AS j) AS jpart) AS x) sub
    GROUP BY
        1) AS sub2

output: [{"Windows": 8}, {"Android": 16}, {"iOS": 9}, {"Macintosh": 3}]

uj5u.com熱心網友回復：

您可以使用動態鍵，這樣的值：

with data as (
  select
    je.key,
    sum(je.value::int)
  from 
    json_array_elements('[{"Android": 2, "Windows": 1, "Macintosh": 1}, {"iOS": 1, "Android": 2, "Windows": 2, "Macintosh": 2}, {}, {"Android": 1, "Windows": 1}, {"Android": 1}, {"iOS": 1, "Android": 2}, {"iOS": 2, "Android": 1}, {"iOS": 2}, {"Android": 1}, {"iOS": 2, "Windows": 1}, {"Android": 5}, {}, {}, {"iOS": 1, "Android": 1}, {}, {}, {"Windows": 3}]') d
    cross join json_each_text(d) as je
  group by 1
)
select json_object_agg(d.key, d.sum)
from data d

或者，如果您有表和列，則可以像這樣使用此示例格式：

with data as (
  select
    je.key,
    sum(je.value::int)
  from 
    your_table t join
    json_array_elements(t.your_json_coulmn) d on true
    cross join json_each_text(d) as je
  group by 1
)
select json_object_agg(d.key, d.sum)
from data d

標籤：json PostgreSQL

上一篇：如何匹配復合型別陣列中的元素？

下一篇：如何正確撰寫PostgreSQL百分比查詢？