Postgres json key count

Question

Given N records that contain a json column

|ID    |Name   |JSON                             
|01    |TEST1  |{"key1" : "value1", "key2": "value2", "key4": "value4"}
|02    |TEST1  |{"key1" : "value1"}
|03    |TEST2  |{"key1" : "value1", "key2": "value2", "key3":"value3"}
...

What would be the best strategy to count the occurrences of each json value for a set of keys, such that for the example above I would restrict to key1, key2, key3 and get:-

|value1|value2|value3|
|3     |2     |1     |

The values will change so I don't really want to look for them explicitly.

Repeat after me: I should NOT use json or hstore or xml or eav or any other semi-structured construct if I actually care about whatever is stored in it. Repeat: NOT use. (Hint: fix your schema, and look into json functions in the meanwhile.) — Denis de Bernardy
– Denis de Bernardy, Commented Jan 8, 2014 at 14:27

2 revs · Accepted Answer · 2014-01-08 15:12:35Z

3

CREATE TABLE test (id INT4 PRIMARY KEY, some_name TEXT, j json);
copy test FROM stdin;
01  TEST1   {"key1" : "value1", "key2": "value2", "key4": "value4"}
02  TEST1   {"key1" : "value1"}
03  TEST2   {"key1" : "value1", "key2": "value2", "key3":"value3"}
\.
with unpacked as (
    SELECT (json_each_text(j)).* FROM test
)
SELECT value, count(*) FROM unpacked WHERE key in ('key1', 'key2', 'key3') group by value;

Returns:

 value  | count 
--------+-------
 value1 |     3
 value3 |     1
 value2 |     2
(3 rows)

Returning it like you showed doesn't strike me as great idea (what would you want to do if there are 4 billion different values?), but you can always pivot in your app, or modify the query to do the pivoting.

Collectives™ on Stack Overflow

Postgres json key count

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related