Re: Extract elements from JSON array and return them as concatenated string

14.03.2018 20:02, Alexander Farber пишет:
> 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(dot)panchenko(at)postgrespro(dot)ru <mailto:i(dot)panchenko(at)postgrespro(dot)ru>> wrote:
>
> 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='play' 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?
Yes, here x is the alias for the record, not for the json field. So you
need to write the query like

select string_agg(x->>'letter', ' ')
from (
   select jsonb_array_elements(tiles) x
   from words_moves
   where gid=656 and action='play'
   order by played desc limit 5
) y;

>
> Regards
> Alex
>
>
Regards,
Ivan