How to convert json array to json object in PostgreSQL?

Question

This is My Column cmt_json_value which has values which is of type json array.

[{"name": "Pending", "value": "PENDING"}, {"name": "Error", "value": "ERROR"},{"name":"Complete", "value":"COMPLETE"},{"name":"In-Progress", "value":"IN_PROGRESS"}]

I want to Write a Postgresql query to fetch name and value as column from table configuration_matrix.

my existing query is:-

select cmt_json_value ->>'name' as name , cmt_json_value ->> 'value' as value
from configuration_matrix
where
cmt_category = 'LIST_OF_VALUES' and
cmt_key = 'JOB_STATUS'
order by cmt_json_value ->> 'name' asc;

Answer 1

There is a special function for aggregation of name/value pairs: json_object_agg. Just place it inside a subquery or a LATERAL derived table.

select (
    select
      json_object_agg(j->>'name', j->>'value')
    from json_array_elements(cm.cmt_json_value) j
)
from configuration_matrix cm;

If you only want the data as columns you can just laterally join the JSON elements.

select
      j->>'name', j->>'value'
from configuration_matrix cm
cross join json_array_elements(cm.cmt_json_value) j

db<>fiddle

Note that using a set-returning function in the SELECT is pretty weird and should be avoided.

Answer 2

select x.name, x.value 
from configuration_matrix cmt,
json_to_recordset(cmt.cmt_json_value) as x(name text, value text)
where
cmt.cmt_category = 'LIST_OF_VALUES' and
cmt.cmt_key = 'JOB_STATUS'
order by x.name

Even this work no need of sub queries.

Answer 3

select object ->> 'name' as name , object ->> 'value' as value from
(select json_array_elements(cmt_json_value) as object
from configuration_matrix cmt
where
cmt.cmt_category = 'LIST_OF_VALUES' and
cmt.cmt_key = 'JOB_STATUS') as object

I tried this and its working.