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

On 03/14/2018 10:12 AM, Adrian Klaver wrote:
> On 03/14/2018 10:02 AM, Alexander Farber wrote:
>> 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?
>
> Do you still have non-arrays in the tile field?:
>
> https://www.postgresql.org/message-id/CAADeyWgYKKaArJb6JK_xEtSO%3D7aeNaYqBu_ef-D5W7s8EFPfpQ%40mail.gmail.com

I should have looked closer before answering, yes there are:

1353 | swap | 656 | 7 | 2018-03-14 17:22:18.430082+01 | "ЙНРР"

>
>
>>
>> Regards
>> Alex
>>
>>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com