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

Question

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

Answer 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

• Chad Day: Thank you! I felt I was close and dancing around it but just didn't have the syntax quite right.