CODEWITHSUNDEEP
postgresqljsonb
oldhomemovie
oldhomemovie

Reputation: 15129

Converting varchar array to array with jsonb objects

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;

Upvotes: 3

Views: 272

Answers (1)

S-Man
S-Man

Reputation: 23756

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
  1. Expand the array elements into one record each with unnest()
  2. Create the JSON objects using jsonb_object_build() to create the key/value structure your are expecting
  3. re-aggregate these objects into one new JSON array using jsonb_agg()

Upvotes: 4

Related Questions