PostgreSQL jsonb遍历 [英] Postgresql jsonb traversal

问题描述

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

I am very new to the PG jsonb field. I have for example a jsonb field containing the following

{
"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级并在"tags"键中搜索特定键.

Is there a way to query X levels deep and search the "tags" key for a certain key.

说我想要"ModuleBase2"和"in1"，我想获取它们的值吗?

Say I want "ModuleBase2" and "in1" and I want to get their values?

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

Basically I am looking for a query that will traverse a jsonb field until it finds a key and returns the value without having to know the structure.

在Python或JS中，简单的循环或递归函数可以轻松地遍历json对象(或字典)，直到找到密钥为止.

In Python or JS a simple loop or recursive function could easily traverse a json object (or dictionary) until it finds a key.

有内置的功能PG必须这样做吗?

Is there a built in function PG has to do that?

最终我想在Django中做到这一点.

Ultimately I want to do this in django.

我看到我可以做类似的事情

I see I can do stuff like

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

但是我必须指定级别.

推荐答案

您可以使用递归查询来展平嵌套的jsonb，请参见 修改查询以查找特定键的值(在where子句中添加条件):

You can use a recursive query to flatten a nested jsonb, see this answer. Modify the query to find values for specific keys (add a condition in where clause):

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)    

在 SqlFiddle中进行测试.

这篇关于PostgreSQL jsonb遍历的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！