Re: jsonb_array_length: ERROR: 22023: cannot get array length of ascalar - Mailing list pgsql-general

On 03/02/2018 05:52 AM, Alexander Farber wrote:
> Good afternoon,
> 
> in PostgreSQL 10.3 I have the following table with a jsonb column:
> 
> #  \d words_moves;
>                                       Table "public.words_moves"
>   Column |           Type           | Collation | Nullable |             
>      Default
> --------+--------------------------+-----------+----------+------------------------------------------
>   mid    | bigint                   |           | not null | 
> nextval('words_moves_mid_seq'::regclass)
>   action | text                     |           | not null |
>   gid    | integer                  |           | not null |
>   uid    | integer                  |           | not null |
>   played | timestamp with time zone |           | not null |
>   tiles  | jsonb                    |           |          |
>   score  | integer                  |           |          |
> Indexes:
>      "words_moves_pkey" PRIMARY KEY, btree (mid)
> Check constraints:
>      "words_moves_score_check" CHECK (score >= 0)
> Foreign-key constraints:
>      "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES 
> words_games(gid) ON DELETE CASCADE
>      "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES 
> words_users(uid) ON DELETE CASCADE
> Referenced by:
>      TABLE "words_daily" CONSTRAINT "words_daily_mid_fkey" FOREIGN KEY 
> (mid) REFERENCES words_moves(mid) ON DELETE CASCADE
>      TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY 
> (mid) REFERENCES words_moves(mid) ON DELETE CASCADE
> 
> Here are some records (please pardon the non-english chars):
> 
> # select * from words_moves where gid=609 limit 3;
> -[ RECORD 1 
>
]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> mid    | 1040
> action | play
> gid    | 609
> uid    | 1192
> played | 2018-03-02 10:13:57.943876+01
> tiles  | [{"col": 3, "row": 7, "value": 2, "letter": "С"}, {"col": 4, 
> "row": 7, "value": 1, "letter": "О"}, {"col": 5, "row": 7, "value": 2, 
> "letter": "П"}, {"col": 6, "row": 7, "value": 0, "letter": "Л"}, {"col": 
> 7, "row": 7, "value": 3, "letter": "Я"}]
> score  | 10
> -[ RECORD 2 
>
]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> mid    | 1041
> action | play
> gid    | 609
> uid    | 7
> played | 2018-03-02 10:56:58.72503+01
> tiles  | [{"col": 3, "row": 8, "value": 2, "letter": "В"}, {"col": 3, 
> "row": 9, "value": 1, "letter": "И"}, {"col": 3, "row": 10, "value": 2, 
> "letter": "Т"}, {"col": 3, "row": 11, "value": 2, "letter": "К"}, 
> {"col": 3, "row": 12, "value": 1, "letter": "А"}]
> score  | 14
> -[ RECORD 3 
>
]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> mid    | 1043
> action | play
> gid    | 609
> uid    | 1192
> played | 2018-03-02 11:03:58.614094+01
> tiles  | [{"col": 0, "row": 10, "value": 2, "letter": "С"}, {"col": 1, 
> "row": 10, "value": 2, "letter": "М"}, {"col": 2, "row": 10, "value": 1, 
> "letter": "О"}, {"col": 4, "row": 10, "value": 2, "letter": "Р"}]
> score  | 13
> 
> I would like to get the length of the tiles array (because in my word 
> game 7 played tiles mean +15 score bonus) - but that call fails for some 
> reason:
> 
> #  select mid, jsonb_array_length(tiles) from words_moves where gid=609;
> ERROR:  22023: cannot get array length of a scalar
> LOCATION:  jsonb_array_length, jsonfuncs.c:1579
> 
> What am I doing wrong here please?

Are you sure all the values in tiles are correctly formatted because 
when I use jsonb_array_length with the provided data:

test=# select jsonb_array_length( '[{"col": 3, "row": 7, "value": 2, 
"letter": "С"}, {"col": 4, "row": 7, "value": 1, "letter": "О"}, {"col": 
5, "row": 7, "value": 2, "letter": "П"}, {"col": 6, "row": 7, "value": 
0, "letter": "Л"}, {"col": 7, "row": 7, "value": 3, "letter": "Я"}]');
  jsonb_array_length
--------------------
                   5

test=# select jsonb_array_length( '[{"col": 3, "row": 8, "value": 2, 
"letter": "В"}, {"col": 3, "row": 9, "value": 1, "letter": "И"}, {"col": 
3, "row": 10, "value": 2, "letter": "Т"}, {"col": 3, "row": 11, "value": 
2, "letter": "К"}, {"col": 3, "row": 12, "value": 1, "letter": "А"}]');
  jsonb_array_length
--------------------
                   5

test=# select jsonb_array_length('[{"col": 0, "row": 10, "value": 2, 
"letter": "С"}, {"col": 1, "row": 10, "value": 2, "letter": "М"}, 
{"col": 2, "row": 10, "value": 1, "letter": "О"}, {"col": 4, "row": 10, 
"value": 2, "letter": "Р"}]');
  jsonb_array_length 
 

-------------------- 
 

                   4

it works.

The error message would suggest there is data in tiles which is not an 
array but a scalar value.


> 
> Regards
> Alex


-- 
Adrian Klaver
adrian.klaver@aklaver.com