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

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

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.klaver@aklaver.com

On 03/02/2018 06:42 AM, Alexander Farber wrote:
> Hi Adrian, I 100% agree that nobody except me should debug my huge 
> stored function, but if you look at my PostgreSQL 10.3 log -
> 

Which proves what has already been proven, that at least some of the 
data is correct. The issue is data that is not correct as evidenced by 
the error message:

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

This is not going to get solved until you identify the 'bad' tiles data.


> 2018-03-02 15:30:33.646 CET [16693] LOG:  duration: 0.110 ms  parse 
> <unnamed>: SELECT out_uid  AS uid,  out_fcm  AS fcm,  out_apns AS apns, 
> out_adm  AS adm,  out_body AS body  FROM words_play_game($1::int, 
> $2::int, $3::jsonb)
> 2018-03-02 15:30:33.646 CET [16693] LOG: duration: 0.058 ms  bind 
> <unnamed>: SELECT out_uid  AS uid, out_fcm  AS fcm,  out_apns AS apns, 
> out_adm  AS adm,  out_body AS body FROM words_play_game($1::int, 
> $2::int, $3::jsonb)
> 2018-03-02 15:30:33.646 CET [16693] DETAIL:  parameters: $1 = '7', $2 = 
> '609', $3 = 
> '[{"col":0,"letter":"К","row":3,"value":2},{"col":0,"letter":"И","row":4,"value":1}]'
> 2018-03-02 15:30:33.646 CET [16693] LOG:  execute <unnamed>: SELECT 
> out_uid AS uid,  out_fcm  AS fcm,  out_apns AS apns, out_adm  AS adm,  
> out_body AS body  FROM words_play_game($1::int, $2::int, $3::jsonb)
> 2018-03-02 15:30:33.646 CET [16693] DETAIL:  parameters: $1 = '7', $2 = 
> '609', $3 = 
> '[{"col":0,"letter":"К","row":3,"value":2},{"col":0,"letter":"И","row":4,"value":1}]'
> 
> I just pass as the 3rd argument in_tiles to my stored function: 
> '[{"col":0,"letter":"К","row":3,"value":2},{"col":0,"letter":"И","row":4,"value":1}]'
> 
> and then take the in_tiles and store it unchanged in the words_moves table:
> 
>          INSERT INTO words_moves (
>                  action,
>                  gid,
>                  uid,
>                  played,
>                  tiles
>          ) VALUES (
>                  'play',
>                  in_gid,
>                  in_uid,
>                  CURRENT_TIMESTAMP,
>                  in_tiles
>          ) RETURNING mid INTO STRICT _mid;
> 
> Does anybody happen to see what could I do wrong there?
> 
> Thank you for any hints
> Alex
> 
> P.S: Here my stored fuinction: 
> https://gist.github.com/afarber/88a832a1b90a8940764ad69b2b761914
>          Here my table: 
> https://gist.github.com/afarber/06cc37114ff8dd14f05077f312904361
>          And here is how I call the stored function from Java:
> 
>     String SQL_PLAY_GAME            =
>              "SELECT " +
>                  "out_uid  AS uid,  " +
>                  "out_fcm  AS fcm,  " +
>                  "out_apns AS apns, " +
>                  "out_adm  AS adm,  " +
>                  "out_body AS body  " +
>              "FROM words_play_game(?::int, ?::int, ?::jsonb)";
> 
>      private void handlePlay(int gid, String tiles) throws SQLException, 
> IOException {
>          LOG.info("handlePlay: {} -> {} {}", mUid, gid, tiles);
>          try (Connection db = DriverManager.getConnection(DATABASE_URL, 
> DATABASE_USER, DATABASE_PASS);
>                  PreparedStatement st = 
> db.prepareStatement(SQL_PLAY_GAME)) {
>              st.setInt(1, mUid);
>              st.setInt(2, gid);
>              st.setString(3, tiles);
>              runPlayerAction(st, gid);
>          }
>      }


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 03/02/2018 06:42 AM, Alexander Farber wrote:
> Hi Adrian, I 100% agree that nobody except me should debug my huge 
> stored function, but if you look at my PostgreSQL 10.3 log -
> 

Another thought, in :

2018-03-02 15:30:33.646 CET [16693] LOG:  duration: 0.058 ms  bind 
<unnamed>: SELECT out_uid  AS uid,  out_fcm  AS fcm,  out_apns AS apns, 
out_adm  AS adm,  out_body AS body  FROM words_play_game($1::int, 
$2::int, $3::jsonb)

where is $3::jsonb coming from?


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 03/02/2018 06:42 AM, Alexander Farber wrote:

2018-03-02 15:30:33.646 CET [16693] LOG:  duration: 0.058 ms  bind <unnamed>: SELECT out_uid  AS uid,  out_fcm  AS fcm,  out_apns AS apns, out_adm  AS adm,  out_body AS body  FROM words_play_game($1::int, $2::int, $3::jsonb)

where is $3::jsonb coming from?

On 03/02/2018 10:04 AM, David G. Johnston wrote:
> On Friday, March 2, 2018, Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 03/02/2018 06:42 AM, Alexander Farber wrote:
> 
> 
> 
>     2018-03-02 15:30:33.646 CET [16693] LOG:  duration: 0.058 ms  bind
>     <unnamed>: SELECT out_uid  AS uid,  out_fcm  AS fcm,  out_apns AS
>     apns, out_adm  AS adm,  out_body AS body  FROM
>     words_play_game($1::int, $2::int, $3::jsonb)
> 
>     where is $3::jsonb coming from?
> 
> 
> Java prepared statement I think, using setString.

I was thinking more about the ultimate source of the data. The 
words_play_game() function, AFAICT, just passes the jsonb from input 
into the word_moves table. If that is the case the problem may occur 
further upstream where the jsonb array is actually built.

> 
> Not at computer right now but what does:
> 
> '[1,2,3]'::text::jsonb result in compared to '[1,2,3]'::jsonb ?
> 
> Because the system think your array-looking string is actually just a 
> scalar that happens to have a leading [ and a trailing ]
> 
> David J.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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

On 03/02/2018 10:58 AM, Alexander Farber wrote:
> I see, thank you for your comments, David and Adrian.
> 
> In the "tiles" column actually save either the JSON array of tiles - 
> when the user plays them
> 
> Or a string (which is jsonb too) concatenated of letters - when the user 
> swaps the letters.
> 
> Maybe I should rethink my table structure (I just want to "log" all 
> plays, swaps, skips, resigns in the words_moves table)...

Or make the string the value of an array:

[{"swap": "ТСНЦУЭ"}]

so you are not changing the inner JSON in the field.

> 
> Or maybe I should always check for the "action" column first (acts as 
> enum) - before accessing "tiles" column....
> 
> Regrads
> Alex
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com