From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com>, 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 14:05:36 |
Message-ID: | 0325b36d-f990-deb9-3086-0fb626c95e14@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Farber | 2018-03-02 14:14:48 | Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar |
Previous Message | Alexander Farber | 2018-03-02 13:52:45 | jsonb_array_length: ERROR: 22023: cannot get array length of a scalar |