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

user_891055 0
Viewed 19026

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.

postgresql sql json
4 Answers

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;
user_14310790

Alternately, you could simply return the upper bounds of the keys when listed as an array:

SELECT
    ARRAY_UPPER( -- Grab the upper bounds of the array
        ARRAY( -- Convert rows into an array.
            SELECT JSONB_OBJECT_KEYS(obj)
        ),
        1 -- The array's dimension we're interested in retrieving the count for
    ) AS count
FROM
    xxx

Using '{"a": 1, "b": 2, "c": 3}'::jsonb as obj, count would result in a value of three (3).

Pasteable example:

SELECT
    ARRAY_UPPER( -- Grab the upper bounds of the array
        ARRAY( -- Convert rows into an array.
             SELECT JSONB_OBJECT_KEYS('{"a": 1, "b": 2, "c": 3}'::jsonb)
        ),
        1 -- The array's dimension we're interested in retrieving the count for
    ) AS count
user_2532540
Related