PostgreSQL – How do I get all the values from a JSONB search on a nested object?

All we need is an easy explanation of the problem, so here it is.

Postgres 12 with the following jsonb field I need to dig into:

{"count":894,
"limit":100000,
"units":"lin",
"observations": [
   {"date":"1947-01-01","value":"21.48","realtime_end":"2021-07-14","realtime_start":"2021-07-14"},
   {"date":"1947-02-01","value":"21.62","realtime_end":"2021-07-14","realtime_start":"2021-07-14"},
   {"date":"1947-03-01","value":"22.0","realtime_end":"2021-07-14","realtime_start":"2021-07-14"}
   {"date":"1947-04-01","value":"122.0","realtime_end":"2021-07-14","realtime_start":"2021-07-14"}

]}

I want to be able to get the entire observations record when doing a select. This gets me somewhat close:

SELECT jsonb_path_query(series_data_point, '$.observations.value[*] ? (@ >= "22.0").double()') as value
from table

but only gives me the value back, and it’s string matching so it’s not returning the last record there. Need to figure out how to match properly on nested values so I can filter based on values, dates, etc. and get a row back per matching observation.

Thank you! New to PostgreSQL and JSON Path syntax..

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You need to move the ? filter onto the observations node, and within that, filter on value.

Also, I think you want 22.0 without quotes, because it is comparing to a double

SELECT jsonb_path_query(
  series_data_point,
  '$.observations ? (@.value[*].double() >= 22.0)') as value
from table

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply