Thank you, Ivan! I am trying to apply your suggestion to my table -
On Wed, Mar 14, 2018 at 5:34 PM, Ivan E. Panchenko <i.panchenko@xxxxxxxxxxxxxx> wrote:
# select * from words_moves where gid=656 order by played desc limit 3;
mid | action | gid | uid | played | tiles | score
------+--------+-----+------+-------------------------------+------------------------------------------------------------------------------------------------------+-------
1353 | swap | 656 | 7 | 2018-03-14 17:22:18.430082+01 | "ЙНРР" | ¤
1352 | play | 656 | 1199 | 2018-03-14 16:55:45.898379+01 | [{"col": 4, "row": 7, "value": 2, "letter": "С"}, {"col": 5, "row": 7, "value": 3, "letter": "У"}] | 19
1351 | play | 656 | 7 | 2018-03-14 16:38:48.132546+01 | [{"col": 9, "row": 11, "value": 5, "letter": "Ж"}, {"col": 9, "row": 13, "value": 2, "letter": "М"}] | 16
(3 rows)
Hi Alex,
SELECT string_agg(x->>'letter','') FROM json_array_elements(
'[{"col": 7, "row": 12, "value": 3, "letter": "A"}, {"col": 8, "row": 12, "value": 10, "letter": "B"}, {"col": 9, "row": 12, "value": 1, "letter": "C"}, {"col": 10, "row": 12, "value": 2, "letter": "D"}]'::json
) x;
# select * from words_moves where gid=656 order by played desc limit 3;
mid | action | gid | uid | played | tiles | score
------+--------+-----+------+-------------------------------+------------------------------------------------------------------------------------------------------+-------
1353 | swap | 656 | 7 | 2018-03-14 17:22:18.430082+01 | "ЙНРР" | ¤
1352 | play | 656 | 1199 | 2018-03-14 16:55:45.898379+01 | [{"col": 4, "row": 7, "value": 2, "letter": "С"}, {"col": 5, "row": 7, "value": 3, "letter": "У"}] | 19
1351 | play | 656 | 7 | 2018-03-14 16:38:48.132546+01 | [{"col": 9, "row": 11, "value": 5, "letter": "Ж"}, {"col": 9, "row": 13, "value": 2, "letter": "М"}] | 16
(3 rows)
by trying the following:
# select string_agg(x->>'letter', ' ') from (select jsonb_array_elements(tiles) from words_moves where gid=656 and action="" order by played desc limit 5) x;
ERROR: 42883: operator does not exist: record ->> unknown
LINE 1: select string_agg(x->>'letter', ' ') from (select jsonb_arra...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
LOCATION: op_error, parse_oper.c:728
# select string_agg(x->>'letter', ' ') from (select jsonb_array_elements(tiles) from words_moves where gid=656 and action="" order by played desc limit 5) x;
ERROR: 42883: operator does not exist: record ->> unknown
LINE 1: select string_agg(x->>'letter', ' ') from (select jsonb_arra...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
LOCATION: op_error, parse_oper.c:728
I am probably missing something obvious?
Regards
Alex
