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

Shortest:

SELECT count(*) FROM jsonb_object_keys('{"a": 1, "b": 2, "c": 3}'::jsonb);

Returns 3

If you want all json number of keys from a table, it gives:

SELECT (SELECT COUNT(*) FROM json_object_keys(myJsonField)) nbr_keys FROM myTable;

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.

Related