How to count jsonb object keys in Postgresql?

Question

I'm trying to find which of the jsonb keys are the most popular ones in my data field. I was able to get names of all keys with this query:

select jsonb_object_keys(data) as key
from client 
group by key;

When I try to add a count like I normally do:

select jsonb_object_keys(data) as key, count(jsonb_object_keys(data))
from client 
group by key;

I receive this error:

ERROR: aggregate function calls cannot contain set-returning function calls

Is there a way to count distinct keys of jsonb object?

Sample data:

data
{"a": "xyz"}
{"b": "assa", "c": "134323"}
{"c": "123"}
{"c": "12324", "a": "xysaz"}

Desired output:

key count(key)
a   2
b   1  
c   3

Please provide sample data and desired results.

Gordon Linoff
– Gordon Linoff

2020-02-25 11:07:24 +00:00
Commented Feb 25, 2020 at 11:07 — Gordon Linoff
– Gordon Linoff, Commented Feb 25, 2020 at 11:07

Gordon Linoff · Accepted Answer · 2020-02-25 11:08:53Z

6

jsonb_object_keys is a set returning function -- basically a table. You want to refer to it in the FROM clause, so I recommend:

select k as key, count(*)
from client c cross join lateral
     jsonb_object_keys(c.data) k
group by k;

The lateral is actually optional. However, I much prefer including it because otherwise the parsing is awkward (why does c.data resolve correctly?).

answered Feb 25, 2020 at 11:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Mr. T Over a year ago

honestly, this was some black magic. i never new about lateral and this has changed my life. so awesome. thank you!

Collectives™ on Stack Overflow

How to count jsonb object keys in Postgresql?

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