How to retrieve a data from inside a json postgres

Question

In my database table I have a column named data and that column's type is jsonb. Here is a sample json of the column.

{"query": {"end-date": "2016-01-31", "start-date": "2016-01-01", "max-results": 1000, "start-index": 1 }}

This is the result in a formal format.

{
  "query":{
    "end-date":"2016-01-31",
    "start-date":"2016-01-01",
    "max-results":1000,
    "start-index":1
  }
}

I need to get the data from the 'start date' inside the 'query' element. How get the data from the start date from a pgsql query

Answer 1

You can use the Postgres in-built function named 'json_extract_path'. Document.

The first parameter in this function is the column name, the second parameter is JSON root element and the third parameter is the key name of which you want to get data.

select json_extract_path(data::json,'query','start-date') as test FROM "schema".tbl_name