Search code examples
postgresqljsonb

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

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.
Solution

  • 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);