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

Mayur Patel · Accepted Answer · 2017-08-09 05:54:31Z

12

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

answered Aug 9, 2017 at 5:54

Mayur Patel

1,75116 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to retrieve a data from inside a json postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related