CODEWITHSUNDEEP
jsonpostgresql
SriK
SriK

Reputation: 1121

How to Count the number of times a specific key appears across the whole table within an arbitrary key sized json column

For example if i have

colA
{"a" : 1, "b": 2}
{"b" : 3}
{}

I want my result to be:

key, count
a, 1
b, 2

Upvotes: 0

Views: 45

Answers (1)

user330315
user330315

Reputation:

If you don't need to deal with nested json structures, you can use jsonb_object_keys to iterate over all keys:

select x.ky, count(*)
from the_table t
  cross join jsonb_object_keys(t.col_a) as x(ky)
group by x.ky
order by x.ky;

Upvotes: 1

Related Questions