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