sql – jsonb查询数组中的嵌套对象

该表包含两行,包含以下json数据：

第一排：

{
    "id": 1,"name": "foobar","members": {
        "coach": {
            "id": 1,"name": "A dude"
        },"players": [
            {
                "id": 2,"name": "B dude"
            },{
                "id": 3,"name": "C dude"
            },{
                "id": 4,"name": "D dude"
            },{
                "id": 6,"name": "F dude"
            },{
                "id": 7,"name": "G dude"
            }
        ]
    }
}

第二排：

{
    "id": 2,"name": "bazbar","members": {
        "coach": {
            "id": 11,"players": [
            {
                "id": 3,{
                "id": 5,"name": "E dude"
            },"name": "G dude"
            },{
                "id": 8,"name": "H dude"
            }
        ]
    }
}

查询如何获得所需的团队列表？我已经尝试了查询,我会创建成员玩家jsonb_array_elements阵列(JSON – >“成员” – >“玩家”) – >“ID”并加以比较,但所有我能做到结果是任何比较的玩家ID在团队中都可用,而不是全部.

解决方法

>使用复杂的嵌套结构处理jsonb.
>在文档类型上运行等效的关系除法查询.

首先,为jsonb_populate_recordset()注册一个行类型.您可以使用CREATE TYPE永久创建类型,也可以创建临时表以供临时使用(在会话结束时自动删除)：

CREATE TEMP TABLE foo(id int);  -- just "id",we don't need "name"

我们只需要id,所以不要包含名称. Per documentation:

JSON fields that do not appear in the target row type will be omitted from the output

询问

SELECT t.json->>'id' AS team_id,p.players
FROM   teams t,LATERAL (SELECT ARRAY (
         SELECT * FROM jsonb_populate_recordset(null::foo,t.json#>'{members,players}')
         )
       ) AS p(players)
WHERE p.players @> '{3,4,7}';

07.0对于Postgres 9.3中的json(第9.4页尚未提供).

说明

>使用玩家记录提取JSON数组：

t.json#>'{members,players}'

>从这些中,我只删除了带有以下内容的行：

jsonb_populate_recordset(null::foo,players}')

…并立即将它们聚合成Postgres数组,因此我们在基表中每行保留一行：

SELECT ARRAY ( ... )

>所有这些都发生在横向连接中：

,LATERAL (SELECT ... ) AS p(players)

>立即过滤生成的数组,只保留我们要查找的数组 – 使用“contains” array operator @>：

WHERE p.players @> '{3,7}'

瞧.

如果你在一个大表上大量运行这个查询,你可以创建一个假的IMMUTABLE函数,它像上面一样提取数组,并根据这个函数创建函数GIN索引,使这个超快.
“假”因为函数取决于基础行类型,即在目录查找上,并且如果更改则会更改. (所以要确保它不会改变.)与此类似：

> Index for finding an element in a JSON array

在旁边：不要使用像json这样的类型名称作为列名(即使允许这样做),这会引发棘手的语法错误并使错误消息混乱.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!