PostgreSQL - 如何從嵌套對象的 JSONB 搜尋中獲取所有值？

Postgres 12 帶有我需要深入研究的以下 jsonb 欄位：

{"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"}

]}

我希望能夠在進行選擇時獲得整個觀察記錄。這讓我有點接近：

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

但只給我返回值，它是字元串匹配，所以它不會返回那裡的最後一條記錄。需要弄清楚如何在嵌套值上正確匹配，以便我可以根據值、日期等進行過濾，並根據匹配的觀察結果返回一行。

謝謝！PostgreSQL 和 JSON 路徑語法的新功能..

**您需要將?過濾器移動到observations節點上，**然後在其中過濾value.

另外，我認為您想要22.0不帶引號，因為它與double

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

引用自：https://dba.stackexchange.com/questions/295722