Postgresql的jsonb遍历

Question

我对PG的jsonb字段非常陌生。 例如，我有一个包含以下内容的jsonb字段

{
"RootModule": {
  "path": [
    1
  ],
  "tags": {
    "ModuleBase1": {
      "value": 40640,
      "humanstring": "40640"
    },
  "ModuleBase2": {
    "value": 40200,
    "humanstring": "40200"
    }
  },
"children": {
  "RtuInfoModule": {
    "path": [
      1,
      0
    ],
    "tags": {
      "in0": {
        "value": 11172,
        "humanstring": "11172"
      },
      "in1": {
        "value": 25913,
        "humanstring": "25913"
      }  
etc....

有没有一种方法可以查询X个级别并搜索“标签”键以查找特定的键？

比如说我想要“ModuleBase2”和“in1”，我想要获取它们的值？

基本上，我正在寻找一个查询，它将遍历jsonb字段直到找到一个键并返回值，而不必知道结构。

在Python或JS中，一个简单的循环或递归函数可以轻松地遍历json对象（或字典）直到找到一个键。

PG有没有内置函数来做到这一点？

最终我想在Django中实现这一点。

编辑： 我看到我可以做一些东西。

SELECT data.key AS key, data.value as value 
FROM trending_snapshot, jsonb_each(trending_snapshot.snapshot-
>'RootModule') AS data
WHERE key = 'tags';

但我必须指定这些级别。

- Michaela Ervin

Answer 1

您可以使用递归查询来展开嵌套的jsonb，参见此答案。修改查询以查找特定键的值（在where子句中添加条件）：

with recursive flat (id, path, value) as (
    select id, key, value
    from my_table,
    jsonb_each(data)
union
    select f.id, concat(f.path, '.', j.key), j.value
    from flat f,
    jsonb_each(f.value) j
    where jsonb_typeof(f.value) = 'object'
)
select id, path, value
from flat
where path like any(array['%ModuleBase2.value', '%in1.value']);

 id |                       path                       | value 
----+--------------------------------------------------+-------
  1 | RootModule.tags.ModuleBase2.value                | 40200
  1 | RootModule.children.RtuInfoModule.tags.in1.value | 25913
(2 rows)    

Test it in SqlFiddle.

- klin

---

哦，哇。那真是太酷了。非常感谢你。递归 SQL 的思路对我来说也是新的。效果很棒。我想编辑 WHERE 子句可以轻松地得出不同的期望结果。 - Michaela Ervin

是的，您可以通过修改条件来控制结果。 - klin

你真是个救星。 - Michaela Ervin