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

Question

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.

John · Accepted Answer · 2022-10-01 21:52:01Z

38

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 jsonb_object_keys(myJsonField)) nbr_keys FROM myTable;

Edit: there was a typo in the second example.

edited Oct 1, 2022 at 21:52

John

1,36115 silver badges27 bronze badges

answered Feb 8, 2018 at 17:44

Le Droid

4,7843 gold badges40 silver badges34 bronze badges

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

3 Comments

ericn Over a year ago

I'm getting

function json_object_keys(character varying) does not exist  Hint: No function matches the given name and argument types. You may need to add explicit type casts.;

Le Droid Over a year ago

@ericn I used pg v12 i think, which version do you use?

Le Droid Over a year ago

@ericn Do "select version();" on Amazon Redshift to get PG version. In the doc they say it doesn't support all 9.x feature and Json implementation is one of them. Ref: docs.aws.amazon.com/redshift/latest/dg/r_VERSION.html

hruske · Accepted Answer · 2016-03-23 08:28:48Z

14

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.

answered Mar 23, 2016 at 8:28

hruske

2,24319 silver badges27 bronze badges

1 Comment

ericn Over a year ago

I'm getting

function json_object_keys(character varying) does not exist  Hint: No function matches the given name and argument types. You may need to add explicit type casts.;

Joshua Burns · Accepted Answer · 2016-10-02 00:39:07Z

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

mindex · Accepted Answer · 2017-01-27 07:18:02Z

5

While a sub select must be used to convert the JSON keys set to rows, the following tweaked query might run faster by skipping building the temporary array:

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

and it's a bit shorter ;)

To make it a function:

CREATE OR REPLACE FUNCTION public.count_jsonb_keys(j jsonb)
  RETURNS bigint
  LANGUAGE sql
AS $function$
SELECT count(*) from (SELECT jsonb_object_keys(j)) v;
$function$

edited Jan 27, 2017 at 7:18

answered Jan 24, 2017 at 6:50

mindex

1,64613 silver badges18 bronze badges

Comments

Tom Yeh · Accepted Answer · 2023-08-24 12:49:46Z

0

To simplify the effort, you can define a function as follows:

CREATE OR REPLACE FUNCTION json_map_length(json) RETURNS integer AS
$$ SELECT array_length(array_agg(A.key), 1) FROM (
  SELECT json_object_keys($1) as key
) A; $$
LANGUAGE sql IMMUTABLE;

answered Aug 24, 2023 at 12:49

Tom Yeh

2,0173 gold badges16 silver badges23 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

3 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related