PostgreSQL Array datatype to JSON object

Question

I'm attempting to output a postgreSQL (ver 11.4) table to a JSON object within a view and one of the columns is an INT array that needs some special formatting. The array can be of 4 to 10 elements.

If the column (col_arr in the code) contains {2, 4, 6, 8, 9},

I need to output to look like {"option_1" : 2, "option_2" : 4, "option_3" : 6, "option_4" : 8, "option_5" : 9}

I tried about fifty different combinations of function, and the code below is what I came up with that actually produces what I need. It looks hideous, and I know there's got to be a simpler solution, but I'm at a loss to figure out what it would be. Any suggestions would be appreciated.

SELECT cast('{ ' || string_agg('"' || propname || '" : ' || propval,' , ') || ' }' as json) as col_arr
  FROM (SELECT t.table_id, a.elem as propval, 'option_' || a.nr as propname
        FROM   t_table AS t
        LEFT   JOIN LATERAL unnest(col_arr)
        WITH ORDINALITY AS a(elem, nr) ON true) pt 
GROUP BY table_id

klin · Accepted Answer · 2019-10-04 00:19:01Z

2

You can use json_object_agg():

select table_id, json_object_agg(format('option_%s', ord), value) as col_arr
from t_table as t
cross join unnest(col_arr) with ordinality as a(value, ord)
group by table_id

Db<>fiddle.

answered Oct 4, 2019 at 0:19

klin

123k15 gold badges240 silver badges262 bronze badges

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

2 Comments

Bergi Over a year ago

Maybe even simpler with a subquery instead of the grouping

klin Over a year ago

My query seems simpler to me, but your solution may be a bit faster indeed.

Collectives™ on Stack Overflow

PostgreSQL Array datatype to JSON object

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