我正在尝试运行以下查询,除非我误解了文档,否则它看起来应该是可能的。
SELECT id, profile
FROM end_user
WHERE profile @? '$.first_segment_exited_at ? (@.datetime() < "2023-05-25".datetime())'::jsonpath;
这是文档,它使我认为我应该能够实现这一点:
string . datetime()→ datetime_type(参见注解)
从字符串转换的日期/时间值
jsonb_path_query('[“2015-8-1”,“2015-08-12”]','$[*]?(@.datetime()<“2015-08-2”.datetime())')→“2015-8-1”
示例配置文件数据,注意日期是有效的ISO格式;
{
"first_segment_exited_at": "2023-05-22T03:09:37.825Z",
}
下面的查询返回结果,但我希望切换到jsonpath语法,以便利用索引
select id, profile
from end_user
where '2023-05-25'::timestamptz > (profile->>'first_segment_exited_at')::timestamptz
我正在运行Postgres v14。
2条答案
按热度按时间z9zf31ra1#
表列中的JSON日期时间格式非法。这个方法的作用是:
fiddle
第3行是唯一具有法律的格式的。
(The筛选器表达式也需要有效。)
您没有收到错误消息,因为,引用手册:
jsonpath
运算符@?
和@@
抑制以下错误:缺少对象字段或数组元素、意外的JSON项类型、日期时间和数值错误。[...]在搜索不同结构的JSON文档集合时,此行为可能会有所帮助。若要调试输入字符串,请使用不抑制错误的函数进行测试。如
jsonb_path_query_first()
:但这只会飞:
此外,您可以为
.datetime()
方法提供模板模式,如Laurenz在他的补充回答中所演示的。(前方还有更多障碍……)kcrjzv8t2#
问题是您的日期不是
datetime()
识别的格式,您必须指定一个带有datetime()
的模板字符串。使用以下jsonpath
:我不得不使用(我相信没有文档)PostgreSQL扩展的双引号文本格式字符串来匹配字符串中的
T
和Z
。