Postgres Pro
Downloads
Home   >   mailing lists

Extract elements from JSON array and return them as concatenated string - Mailing list pgsql-general

From Alexander Farber
Subject Extract elements from JSON array and return them as concatenated string
Date
Msg-id CAADeyWhC5Tp=mhPU9g2=-q5Y-bU_5_3wGyKLZQrPHscdkG-puA@mail.gmail.com
Whole thread Raw
Responses Re: Extract elements from JSON array and return them as concatenatedstring  ("Ivan E. Panchenko" <i.panchenko@postgrespro.ru>)
List pgsql-general
Tree view
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

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: JDBC connectivity issue
Next
From: "Ivan E. Panchenko"
Date:
Subject: Re: Extract elements from JSON array and return them as concatenatedstring