90

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.

Joshua Burns
  • 8,268
  • 4
  • 48
  • 61
  • 1
    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 Oct 09 '15 at 15:31
  • 2
    Must be a misunderstanding. [The manual says](http://www.postgresql.org/docs/current/interactive/functions-json.html): `json_array_length(json) jsonb_array_length(jsonb)`. – Erwin Brandstetter Oct 09 '15 at 15:35
  • 1
    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 Oct 09 '15 at 15:35
  • 1
    WOW you're right. I mis-read the documentation, I didn't notice the difference between the names of the functions themselves. – Joshua Burns Oct 09 '15 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 Feb 16 '22 at 21:34

1 Answers1

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

or

SELECT json_array_length('["question","solved"]') AS length;
Ryabchenko Alexander
  • 10,057
  • 7
  • 56
  • 88
Jorge André Pereira
  • 1,761
  • 1
  • 11
  • 6
  • For me, the function name had to be caps for it to work (windows, perhaps?) JSONB_ARRAY_LENGTH/JSON_ARRAY_LENGTH – Dakusan May 18 '17 at 17:22
  • 1
    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: https://stackoverflow.com/a/19426902/997940 – Yoav Feuerstein Sep 21 '22 at 08:44
  • In my case I had to intitally convert to jsonb or json respectively. – Daniil Nov 11 '22 at 08:39