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@xxxxxxxxxxx
