From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar |
Date: | 2018-03-02 18:52:32 |
Message-ID: | 5a8262d5-ba14-bff8-fe92-4958d11f0d92@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Farber | 2018-03-02 18:58:37 | Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar |
Previous Message | David G. Johnston | 2018-03-02 18:52:03 | Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar |