代码之家  ›  专栏  ›  技术社区  ›  MWood

Postgres jsonpath用于引用单例元素或元素数组

  •  1
  • MWood  · 技术社区  · 6 月前

    Postgres JSONPath表示法指出 .key 访问器返回具有指定键的对象成员。此运算符似乎对数组和对象都有效。例如,尽管结构不同,但以下查询都返回匹配项。

    select * from (
    select '{"a": [{"b": 2}, {"b": 3}]}'::jsonb as pl
    ) jb where (jsonb_path_exists(jb.pl, '$.a.b ? (@ == 2)'))
    

    select * from (
    select '{"a": {"b": 2}}'::jsonb as pl
    ) jb where (jsonb_path_exists(jb.pl, '$.a.b ? (@ == 2)'))
    

    在第一种情况下,我可能不得不写:

    select * from (
    select '{"a": {"b": 2}}'::jsonb as pl
    ) jb where (jsonb_path_exists(jb.pl, '$.a[*].b ? (@ == 2)'))
    

    但这似乎没有必要。

    我的目标是能够查询像这样的嵌套对象,其中叶子有时可能在数组中,有时不在数组中。看来我可以忽略这种区别,但不确定我看到的是否是预期的行为,因为Postgres文档没有明确指出这是行为。

    假设密钥操作员会像我在这里看到的那样扩展列表,这安全吗?

    1 回复  |  直到 6 月前
        1
  •  1
  •   Zegarek    6 月前

    如果你切换到 strict mode ,表达式将不再尝试将键访问器应用于数组元素,但这也意味着它将不再与结构匹配并引发错误:
    demo at db<>fiddle

    select jsonb_path_query_array(pl,'$.a.b ? (@ == 2)')
    from(values('{"a": [{"b": 2}, {"b": 3}]}'::jsonb))as jb(pl)
    where pl @? '$.a.b ? (@ == 2)';
    
    jsonb_path_query_array
    2.
    select jsonb_path_query_array(pl,'lax $.a.b ? (@ == 2)')
    from(values('{"a": [{"b": 2}, {"b": 3}]}'::jsonb))as jb(pl)
    where pl @? 'lax $.a.b ? (@ == 2)';
    
    jsonb_path_query_array
    2.
    select jsonb_path_query_array(pl,'strict $.a.b ? (@ == 2)')
    from(values('{"a": [{"b": 2}, {"b": 3}]}'::jsonb))as jb(pl)
    where pl @? 'strict $.a.b ? (@ == 2)';
    
    ERROR:  jsonpath member accessor can only be applied to an object
    

    这意味着您将不得不稍微重构JSONPath表达式——您建议的修复效果很好:

    select jsonb_path_query_array(pl,'strict $.a[*].b ? (@ == 2)')
    from(values('{"a": [{"b": 2}, {"b": 3}]}'::jsonb))as jb(pl)
    where pl @? 'strict $.a[*].b ? (@ == 2)';
    
    jsonb_path_query_array
    2.

    如上述示例所示,默认情况下,您处于 lax mode :

    当JSON数据不符合预期的模式时,Lax模式有助于JSON文档和路径表达式的匹配。如果操作数不符合特定操作的要求,则可以将其自动包装为SQL/JSON数组,或者在执行操作之前通过将其元素转换为SQL/JSON序列来展开。此外,比较运算符会在宽松模式下自动解包其操作数,因此您可以开箱即用地比较SQL/JSON数组。大小为1的数组被认为等于其唯一元素。在以下情况下不执行自动展开:

    • 路径表达式包含 type() size() 分别返回数组中元素的类型和数量的方法。

    • 查询的JSON数据包含嵌套数组。在这种情况下,只有最外层的数组被解包,而所有内部数组保持不变。因此,隐式展开只能在每个路径评估步骤中向下一级。

    当你真的 不要 想要匹配接受的替代路径 松懈 模式,但每当提取内容时,在这些模式之间切换也很重要。In 松懈 模式 .** 访问器-如果你想匹配不同的路径和嵌套级别,它可能对你有用-可以匹配 两次 :当它到达正常下降结构的撞击点时,再次尝试通过模式启用的包裹/展开进行下降。

    select jsonb_path_query(pl,'$.**.b?(@==2)')
    from(values('{"a": [{"b": 2}, {"b": 3}]}'::jsonb))as jb(pl);
    
    jsonb_path_query
    2.
    2.
    select jsonb_path_query(pl,'strict $.**.b?(@==2)')
    from(values('{"a": [{"b": 2}, {"b": 3}]}'::jsonb))as jb(pl);
    
    jsonb_path_query
    2.