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

["Ivan E. Panchenko" <i.panchenko@xxxxxxxxxxxxxx>]

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;

Regards,

Ivan Panchenko
Postgres Professional
the Russian PostgreSQL Company

14.03.2018 19:27, Alexander Farber пишет:
> Good afternoon,
>
> A PostgreSQL 10.3 table contains JSON data like:
>
> [{"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"}]
>
> Please suggest, how to extract only the "letter" values and 
> concatenate them to a string like "ABCD"?
>
> I suppose at the end I should use the ARRAY_TO_STRING function, but 
> which JSON function to use for extracting the "letter" values to an array?
>
> I keep looking at 
> https://www.postgresql.org/docs/10/static/functions-json.html but 
> haven't found a good one yet
>
> Thank you
> Alex