Converting varchar array to array with jsonb objects

Question

I have an array of strings, like this:

SELECT ARRAY['[email protected]', '[email protected]', '[email protected]'];

How do I convert (map?) it into a jsonb array of jsonb objects like this?:

SELECT [{"email": "[email protected]"}, {"email": "[email protected]"}, {"email": "[email protected]"}]::jsonb;

S-Man · Accepted Answer · 2019-08-29 07:56:53Z

4

demo:db<>fiddle

SELECT
    jsonb_agg(jsonb_build_object('email', elems))
FROM (
    SELECT ARRAY['[email protected]', '[email protected]', '[email protected]'] AS a
) s,
unnest(a) AS elems

Expand the array elements into one record each with unnest()
Create the JSON objects using jsonb_object_build() to create the key/value structure your are expecting
re-aggregate these objects into one new JSON array using jsonb_agg()

answered Aug 29, 2019 at 7:56

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

Converting varchar array to array with jsonb objects

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related