问题:PGSQL JSONB 中带有部分路径的深度 JSON 查询?

给定一些这样的 JSON 文档:

  {
    id: some_id,
    l1: {
      f1: [
        {
          c1: foo,
          c2: bar
        },
        {
          c1: foo1,
          c2: bar1
        },
      ],
      f2: [
        {
          c3: baz,
          c4: bar
        },
      ],    
    }
  }

如何在 PostgreSQL JSONB 中查询 f1....c1: foo1 -- 即 lX 没有给出,也没有给出 c1-c2 子文档的列表位置。

这不是 MySQL 5.7 中带有部分路径的深度 JSON 查询的副本吗?因为那是关于 MySQL 的,而这个是关于 PgSQL JSONB 的。

解答

在这里,您需要遍历路径{l1,f1}#>- 运算符在指定路径处获取 JSON 对象的元素列表;之后,检查是否有任何子文档包含'{"c1":"foo1"}'::jsonb元素 -@>(操作员检查左侧 JSON 值是否包含右侧值):

WITH t(val) AS ( VALUES
  ('{
      "id": "some_id",
      "l1": {
        "f1": [
          {
            "c1": "foo",
            "c2": "bar"
          },
          {
            "c1": "foo1",
            "c2": "bar1"
          }
        ],
        "f2": [
          {
            "c3": "baz",
            "c4": "bar"
          }
        ]
      }
  }'::JSONB)
)
SELECT f1_array_element AS output
FROM
  t,jsonb_array_elements(t.val#>'{l1,f1}') AS f1_array_element
WHERE f1_array_element @> '{"c1":"foo1"}'::JSONB;

结果:

            output            
------------------------------
 {"c1": "foo1", "c2": "bar1"}
(1 row)

更新

如果我们不知道确切的lX位置,我们需要遍历每个子文档,然后遍历每个fX;查询将如下:

SELECT count(*)
FROM
  t,
  jsonb_each(t.val#>'{l1}') AS fX_sub_doc,
  jsonb_array_elements(fX_sub_doc.value) AS cX_sub_doc
WHERE
  cX_sub_doc @> '{"c1":"foo1"}';