PostgreSQL: json object where keys are unique array elements and values are the count of times they appear in the array

Question

I have an array of strings, some of which may be repeated. I am trying to build a query which returns a single json object where the keys are the distinct values in the array, and the values are the count of times each value appears in the array.

I have built the following query;

WITH items (item) as (SELECT UNNEST(ARRAY['a','b','c','a','a','a','c']))
SELECT json_object_agg(distinct_values, counts) item_counts
FROM (
  SELECT
    sub2.distinct_values,
    count(items.item) counts
  FROM (
    SELECT DISTINCT items.item AS distinct_values
    FROM items
  ) sub2
  JOIN items ON items.item = sub2.distinct_values
  GROUP BY sub2.distinct_values, items.item
) sub1

DbFiddle

Which provides the result I'm looking for: { "a" : 4, "b" : 1, "c" : 2 }

However, it feels like there's probably a better / more elegant / less verbose way of achieving the same thing, so I wondered if any one could point me in the right direction.

For context, I would like to use this as part of a bigger more complex query, but I didn't want to complicate the question with irrelevant details. The array of strings is what one column of the query currently returns, and I would like to convert it into this JSON blob. If it's easier and quicker to do it in code then I can, but I wanted to see if there was an easy way to do it in postgres first.

Terry Spotts · Accepted Answer · 2022-11-23 16:49:08Z

1

I think a CTE and json_object_agg() is a little bit of a shortcut to get you there?

WITH counter AS (
    SELECT UNNEST(ARRAY['a','b','c','a','a','a','c']) AS item, COUNT(*) AS item_count
    GROUP BY 1
    ORDER BY 1
 )
 SELECT json_object_agg(item, item_count) FROM counter

Output:

{"a":4,"b":1,"c":2}

answered Nov 23, 2022 at 16:49

Terry Spotts

4,1851 gold badge11 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sauntimo Over a year ago

That's much neater! Great work, thank you very much.

Collectives™ on Stack Overflow

PostgreSQL: json object where keys are unique array elements and values are the count of times they appear in the array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related