Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

On 03/02/2018 06:14 AM, Alexander Farber wrote:
> Hi Adrian, thank you for the reply -
>

> #  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
>
>
> I fill that table with the following stored function (please pardon the
> huge listing):

The little gray cells are not awake enough to work through the below:)
If it where me I would first confirm there was malformed data by looking
at the data itself. If there are not that many records for gid = 609
maybe a simple select of tiles would be sufficient. Otherwise maybe a
simple plpgsql function that loops through the records applying
jsonb_array_length and raising a notice on the error. In any case the
point is to identify the presence of malformed data and if present the
nature of the malformation. That would help reverse engineer any issues
with below.

>
> CREATE OR REPLACE FUNCTION words_play_game(
>                 in_uid   integer,
>                 in_gid   integer,
>                 in_tiles jsonb
>         ) RETURNS table (
>                 out_uid  integer, -- the player to be notified
>                 out_fcm  text,
>                 out_apns text,
>                 out_adm  text,
>                 out_body text
>         ) AS

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com