vincentlcy vincentlcy - 2 months ago 11
JSON Question

How to count setof / number of keys of JSON in postgresql?

I have a column in

jsonb
storing a map, like
{'a':1,'b':2,'c':3}
where the number of keys is different in each row.

I want to count it -- jsonb_object_keys can retrieve the keys but it is in
setof


Are there something like this?

(select count(jsonb_object_keys(obj) from XXX )

(this won't work as `ERROR: set-valued function called in context that cannot accept a set`)


Postgres JSON Functions and Operators Document

json_object_keys(json)
jsonb_object_keys(jsonb)

setof text Returns set of keys in the outermost JSON object.
json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')

json_object_keys
------------------
f1
f2


Crosstab isn't feasible as the number of key could be large.

Answer

You could convert keys to array and use array_length to get this:

select array_length(array_agg(A.key), 1) from (
    select json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}') as key
) A;

If you need to get this for the whole table, you can just group by primary key.

Comments