Aggregate JSON arrays within table rows / Apply format function to postgres JSON column

Question

I have a postgres table that contains a jsonb-row like this:

MyTable.refs
`[{"url": "url1", "name": "name1"}, {"url": "url2", "name": "name2"}]`
`[]`
`[{"url": "url3", "name": "name3"}`

I would like to write a postgres function that creates a new column like this:

result
`['<a href="url1">name1</a>', '<a href="url2">name2</a>']`
`[]`
`['<a href="url3">name3</a>'}`

I already have something:

SELECT
       jsonb_agg(format('<a href="%s">%s</a>', (elem ->> 'url'), (elem ->> 'name')))
FROM jsonb_array_elements('[{"url": "url1", "name": "name1"}, {"url": "url2", "name": "name2"}]'::jsonb) elem;

Which returns:

["<a href="url1">name1</a>", "<a href="url2">name2</a>"]

How do I apply this transformation to the whole column?

That looks like you already have the solution. What exactly is your question then? — user330315
– user330315, Commented Jul 2, 2021 at 13:29
I can only do it on one jsonb element, but am too stupid to apply it to a column/table. I'm sure it's ridiculously easy, so please enlighten me! — MrTomRod
– MrTomRod, Commented Jul 2, 2021 at 22:16
The title should be improved to reflect the actual problem. Maybe "Aggregate JSON arrays within table rows"? — cstork
– cstork, Commented Oct 25, 2021 at 8:22

cstork · Accepted Answer · 2021-10-25 08:09:42Z

1

I guess you're looking for a nested select statement:

with mytable (refs) as (
  values
    ('[{"url": "url1", "name": "name1"}, {"url": "url2", "name": "name2"}]'::jsonb),
    ('[]'),
    ('[{"url": "url3", "name": "name3"}]')
)
select (select coalesce(jsonb_agg(format('<a href="%s">%s</a>',
                                         elem->>'url', elem->>'name')),
                        '[]')
        from jsonb_array_elements(refs) elem) result
from mytable;

(BTW, references is a reserved keyword in PostgreSQL which can lead to confusing error messages.)

answered Oct 25, 2021 at 8:09

cstork

5864 silver badges9 bronze badges

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

1 Comment

MrTomRod Over a year ago

Thanks, that solves the problem! (I didn't actually use "references" in reality, only for this example. Changed everything to "refs".)

Collectives™ on Stack Overflow

Aggregate JSON arrays within table rows / Apply format function to postgres JSON column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related