How to convert json array to json object in PostgreSQL?



  • 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;
    


  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2