How do convert an array to an object in PostgreSQL with data like this?

Question

[{name:'a',test :1},{name:'b',test :2}]

to

{ a : {test:1}, b:{test:2} }

xehpuk · Accepted Answer · 2022-11-25 15:37:16Z

1

If your input is valid JSON, you can use the built-in functions and operators and aggregate functions:

SELECT json_object_agg(a.e ->> 'name', a.e - 'name')
FROM jsonb_array_elements('[{"name":"a","test":1},{"name":"b","test":2}]') a(e)

answered Nov 25, 2022 at 4:06

xehpuk

8,3704 gold badges37 silver badges61 bronze badges

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

2 Comments

Or just:

SELECT json_object_agg(a.e ->> 'name', a.e - 'name') FROM   jsonb_array_elements('[{"name":"a","test":1},{"name":"b","test":2}]') a(e)

@ErwinBrandstetter I'm always struggling with set-returning functions. Thanks for the improvement!