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

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
>