Calculate JSONB Array Length Using PostgreSQL 9.4

Question

I'm running the latest version of PostgreSQL 9.4.5-1.pgdg14.04+1, and am attempting to calculate the length of a JSONB array using the JSON_ARRAY_LENGTH function as described in the PostgreSQL 9.4 Documentation

Here is the exact query I'm attempting to run:

SELECT JSON_ARRAY_LENGTH('["hi","ho"]'::jsonb) AS length

When I run that query, I would expect to be returned a value of 2, but instead am encountering the error: ERROR: function json_array_length(jsonb) does not exist

Am I missing something very obvious in the documentation? It specifically states you may call JSON_ARRAY_LENGTH passing either a json or jsonb data-type. I'm explicitly casting to jsonb so I'm at a bit of a loss.

Has anyone else encountered this problem, or would someone point out what I'm doing wrong here?

UPDATE: I Mis-Read The Documentation

I should have been calling JSONB_ARRAY_LENGTH, not JSON_ARRAY_LENGTH. Notice the "B" after "JSON". Thanks guys.

I cannot find where "It specifically states you may call JSON_ARRAY_LENGTH passing either a json or jsonb data-type" - could you cite it literally?. — klin
– klin, Commented Oct 9, 2015 at 15:31
Must be a misunderstanding. The manual says: json_array_length(json) jsonb_array_length(jsonb). — Erwin Brandstetter
– Erwin Brandstetter, Commented Oct 9, 2015 at 15:35
as @klin says, I think you are misreading the documentation, it shows that you need to call either json_ or jsonb_ versions of the functions depending upon datatype — Doon
– Doon, Commented Oct 9, 2015 at 15:35
WOW you're right. I mis-read the documentation, I didn't notice the difference between the names of the functions themselves. — Joshua Burns
– Joshua Burns, Commented Oct 9, 2015 at 15:43
Is there a different function for when the column type is 'jsonb[]'? the values look like "{1,2}" and the column type is jsonb[]. — Brian D
– Brian D, Commented Feb 16, 2022 at 21:34

Ryabchenko Alexander · Accepted Answer · 2020-05-28 10:06:04Z

193

SELECT jsonb_array_length('["question","solved"]') AS length;

or

SELECT json_array_length('["question","solved"]') AS length;

edited May 28, 2020 at 10:06

Ryabchenko Alexander

12.7k9 gold badges74 silver badges105 bronze badges

answered Nov 25, 2015 at 4:15

Jorge André Pereira

2,0311 gold badge13 silver badges6 bronze badges

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

3 Comments

Dakusan Over a year ago

For me, the function name had to be caps for it to work (windows, perhaps?) JSONB_ARRAY_LENGTH/JSON_ARRAY_LENGTH

Yoav Feuerstein Over a year ago

Note that this won't work if the array is null. See here for more details about how to handle (or filter out) such cases: stackoverflow.com/a/19426902/997940

Daniil Over a year ago

In my case I had to intitally convert to jsonb or json respectively.

Collectives™ on Stack Overflow

Calculate JSONB Array Length Using PostgreSQL 9.4

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related