sql - 如何使用 sql/knexjs 查询迭代 json 数据

Question

我正在使用 postgresql db。我有一个名为“offers”的表，其中有一列“validity”，其中包含以下 JSON 格式的数据：

[{"end_date": "2019-12-31", "program_id": "4", "start_date": "2019-10-27"}, 
{"end_date":"2020-12-31", "program_id": "6", "start_date": "2020-01-01"}, 
{"end_date": "2020-01-01", "program_id": "3", "start_date": "2019-10-12"}]

现在我想获取“有效性”列包含的所有记录：program_id = 4 和 end_date > current_date。

如何编写 SQL 查询或 knexjs 查询来实现这一点？提前致谢

标签： sqljsonpostgresqlknex.js

Answer 1

您可以使用 EXISTS 条件：

select o.*
from offers o
where exists (select *
              from jsonb_array_elements(o.validity) as v(item)
              where v.item ->> 'program_id' = '3'
                and (v.item ->> 'end_date')::date > current_date)

在线示例