1

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

0

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
1
  • Thank you! I felt I was close and dancing around it but just didn't have the syntax quite right.
    – Chad Day
    Jul 15 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.