Extract elements from JSON array and return them as concatenated string

Question

A PostgreSQL 10 table contains JSON data like (here an SQL Fiddle):

[
    {
        "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"
    }
]

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?

UPDATE:

Got a tip at the very helpful PostgreSQL mailing list too:

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;

klin · Accepted Answer · 2018-03-14 16:27:49Z

1

Use jsonb_array_elements() and string_agg():

with my_table(json_data) as (
values(
'[
    {
        "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"
    }
]'::jsonb)
)
select string_agg(value->>'letter', '')
from my_table
cross join lateral jsonb_array_elements(json_data)

 string_agg 
------------
 ABCD
(1 row)

answered Mar 14, 2018 at 16:27

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alexander Farber Over a year ago

Thank you... Why wouldn't the following work?

select string_agg( jsonb_array_elements( '[{"col": 7, "row": 12, "value": 3, "letter": "A"}, {"col": 8, "row": 12, "value": 10, "letter": "B"}]'::jsonb ) ->> 'letter', '' );

klin Over a year ago

It's a limitation of using a set returning function as an argument of an aggregate function. In cases like that use SRF in the FROM clause,

Collectives™ on Stack Overflow

Extract elements from JSON array and return them as concatenated string

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related