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

Answer 1

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.