How to map a jsonb array of numbers to a jsonb array of strings?

Question

I have a jsonb array of numbers or strings, e.g. '[1, "2", 3]'::jsonb

I want to map all values to strings, so that I can end up with '["1", "2", "3"]'::jsonb

I tried this:

select jsonb_agg(jsonb_array_elements_text('[1, 2, 3]'::jsonb))

But it is complaining

LINE 1: select jsonb_agg(jsonb_array_elements_text('[1, 2, 3]'::json...
                         ^
HINT:  You might be able to move the set-returning function into a LATERAL FROM item.

user330315 · Accepted Answer · 2022-03-15 15:29:35Z

2

Do what the error message suggests: use the set returning function jsonb_array_elements_text() like a table:

select jsonb_agg(element)
from jsonb_array_elements_text('[1, 2, 3]'::jsonb) as x(element);

answered Mar 15, 2022 at 15:29

user330315

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

2 Comments

Kevin Pauli Over a year ago

thanks it works! I could tell from my research that the answer was going to look something like this, but I couldn't get the proper incantation. if you don't mind a follow-up, what is the meaning of the "x()" in your answer?

user330315 Over a year ago

It's a table alias

Collectives™ on Stack Overflow

How to map a jsonb array of numbers to a jsonb array of strings?

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