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.

• Erwin Brandstetter: Your advise is, well, ill-advised. Craig's answer you are linking to is the state from 8 years ago. The behavior of set-returning functions in the SELECT list has been sanitized in Postgres 10. See: stackoverflow.com/a/39864815/939860
• Charlieface: It's not the behaviour per se, I think both versions are weird syntax-wise, it just doesn't make sense to me to put a set function inside a list of scalar SELECT values. Single Responsibility is a thing in language design also. But everyone can do as they see fit, it's just my opinion. You yourself say "A single set-returning function is OK (but still cleaner in the FROM list), but multiple in the same SELECT list is discouraged now. This was a useful feature before we had LATERAL joins. Now it's merely historical ballast." and I think that applies even in the newer versions#
• Erwin Brandstetter: That, too, was before the behavior had been sanitized. The more relevant quote would be "This ends the traditionally odd behavior."

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.

• Brendan McCaffrey: Please combine your two answers into one answer, leaving the one that best answers your original question.
• Community: Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

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.