我有一张名为大师的邮寄桌子.
ID | Reference | CustomFieldsJson |
---|---|---|
1 | John | [ { "ID": 17,"NumericValue": 1234},{ "ID":21,"DateValue":"2023-06-14T00:00:00Z"},{ "ID": 24, "BooleanValue": false } |
2 | Smith | [ { "ID": 17,"NumericValue": 1234},{ "ID":12,"DateValue":"2023-04-05T00:00:00Z"},{ "ID": 15, "ListItemValue": "india" } |
CustomFieldsJson列数据类型为jsonb. 我想通过custom_fields列键Date Value过滤行.为此,我使用了Postgres Documentation中描述的json路径表达
select id,reference,custom_fields_json from master.masters
where custom_fields_json @? '$[*] ? (@.DateValue.datetime() > "2022-06-22T03:09:37Z".datetime())'
但过滤不起作用.查询不返回任何行.我认为日期格式存在问题.在文档中,他们显示了日期格式为"2015-08-12"的示例