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

On 03/02/2018 10:43 AM, Alexander Farber wrote:
> # 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
> 
> #  select column_name, data_type from information_schema.columns where 
> table_name='words_moves';
>   column_name |        data_type
> -------------+--------------------------
>   mid         | bigint
>   action      | text
>   gid         | integer
>   uid         | integer
>   played      | timestamp with time zone
>   tiles       | jsonb
>   score       | integer
> (7 rows)
> 
> #  select jsonb_array_length(tiles) from words_moves where gid=609 limit 3;
>   jsonb_array_length
> --------------------
>                    5
>                    5
>                    4
> (3 rows)
> 
> BUT:
> 
> #  select 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
> 
> 
> Which means only some data is bad, but how to find it please?

What is? :

select count(*) from words_moves where gid=609;

A simplistic approach would be:

select mid, jsonb_array_length(tiles) from words_moves where gid=609 
order by mid limit x;

where you  increment x until you trigger the error.

A more sophisticated approach would be to use plpgsql EXCEPTION handling:

https://www.postgresql.org/docs/10/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

and create a function that loops through:

select jsonb_array_length(tiles) from words_moves where gid=609 ;

and RAISES a NOTICE for each incorrect value along with its mid value.



> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com