1

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;

3 Answers 3

2

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.

3
  • 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 Feb 23, 2022 at 3:07
  • 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# Feb 23, 2022 at 3:13
  • That, too, was before the behavior had been sanitized. The more relevant quote would be "This ends the traditionally odd behavior." Feb 23, 2022 at 3:34
1
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.

2
  • 1
    Please combine your two answers into one answer, leaving the one that best answers your original question. Feb 22, 2022 at 13:48
  • 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.
    – Community Bot
    Feb 27, 2022 at 13:41
-2
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.