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

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.

CodePudding user response：

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}