PostgreSQL count occurrences into json key/value

Question

Given an array of text elements I'd like to calculate the number of occurrences and return them as a son object with a textual key and an integer value. (PostgreSQL 9.4)

Case 1 - without reference set

SELECT ARRAY['3G','2G','2G','3G','3G','3G','3G','4G']

should be transformed to

SELECT '{"2G": 2, "3G": 5, "4G": 1}'::jsonb

Case 2 - using a reference set

given a set of possible entries ARRAY['2G','3G','4G'], the returned json should also contain elements with a zero count.

SELECT ARRAY['3G','2G','2G','3G','3G','3G','3G']

should transform to

SELECT '{"2G": 2, "3G": 5, "4G": 0}'::jsonb

I got quite close on case 1 and 2 by using

-- case 1
SELECT json_object( array_agg(r.a)::text[],array_agg(r.num)::text[] ) 
FROM (
  SELECT a, count(a) as num 
  FROM unnest( ARRAY['3G','2G','2G','3G','3G','3G','3G','4G'] ) a
  GROUP BY a ORDER BY a
) r;

--case 2
SELECT json_object( array_agg(r.ref)::text[],array_agg(r.num)::text[] ) 
FROM (
  SELECT ref, count(a) as num 
  FROM unnest( ARRAY['2G','3G','4G'] ) ref
  LEFT JOIN unnest( ARRAY['3G','2G','2G','3G','3G','3G','3G'] ) a ON (ref = a)
  GROUP BY ref ORDER BY ref
) r;

but, the result returns a textual value, eg:

SELECT '{"2G" : "2", "3G" : "5", "4G" : "0"}'::json   -- case 2

Appreciate your help and feedback!

You didn't include 4G in ARRAY['3G','2G','2G','3G','3G','3G','3G']. — mic4ael
– mic4ael, Commented Jan 8, 2016 at 7:51

Dzmitry Savinkou · Accepted Answer · 2016-01-08 15:19:31Z

4

For your case you can use aggregate json_object_agg() function, query will be simplier:

SELECT json_object_agg(r.ref,r.num) result
FROM (
  SELECT
    ref,
    count(a) AS num
  FROM unnest(ARRAY ['2G', '3G', '4G']) ref
    LEFT JOIN unnest(ARRAY ['3G', '2G', '2G', '3G', '3G', '3G', '3G']) a ON (ref = a)
  GROUP BY ref
  ORDER BY ref
) r;

Result is:

              result              
----------------------------------
 { "2G" : 2, "3G" : 5, "4G" : 0 }
(1 row)

answered Jan 8, 2016 at 15:19

Dzmitry Savinkou

5,2102 gold badges28 silver badges32 bronze badges

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

1 Comment

homat Over a year ago

I was only checking the json functions documentation on postgresql.org/docs/9.4/static/functions-json.html and didn't find json_object_aggthere. Many thanks, @Dmitry!

Collectives™ on Stack Overflow

PostgreSQL count occurrences into json key/value

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