Postgresql -> JSON Array to rows-CodePudding

One of the columns in my table, has JSONArray data. I have used jsonb_agg() in a jsonb column to a view. Now the data from view looks like below

[
  {
    "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
  }
]

However, I need to product the below result

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

Is there a way within PostgreSQL to achieve this?

CodePudding user response：

Yes there is a way. Here it is.

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;

As a parameterized query:

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;

CodePudding user response：

You can combine many json/jsonb functions to get the result

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}]

CodePudding user response：

You can use dynamic key, value like that:

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

Or if you have table and column you can use this sample format like that:

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