Extract elements from JSON array and return them as concatenated string

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Extract elements from JSON array and return them as concatenated string
Date: 2018-03-14 16:27:08
Message-ID: CAADeyWhC5Tp=mhPU9g2=-q5Y-bU_5_3wGyKLZQrPHscdkG-puA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ivan E. Panchenko 2018-03-14 16:34:55 Re: Extract elements from JSON array and return them as concatenated string
Previous Message Adrian Klaver 2018-03-14 15:44:11 Re: JDBC connectivity issue