PostgreSQL 13.6中如何获取jsonb路径查询结果的属性路径

在PostgreSQL 13.6中，数据库表包含polnum字段和jsonb类型的payload字段，payload内多处存在名为Code的属性。目前通过jsonb_path_query(payload, 'strict $.**.Code')可获取所有Code属性的值，但需要同时获取每个Code对应的JSON路径，尝试使用jsonb_extract_path未成功。

解决方案 #

可以利用PostgreSQL jsonb_path_query函数的wrapper参数，该参数会将匹配到的节点包装成包含value（节点值）和path（节点路径）的JSON对象，之后只需提取这两个字段即可。

示例查询语句 #

WITH json_table AS (
        SELECT 'PL123456' as polnum, '
        {
        "node1": { 
            "node2": { 
                "node3": [ 
                    {
                        "node4": { 
                            "node5": { 
                                "Code": "34",
                                "Image": "ffffffff-ffff-ffff-ffff-ffffffffffff",
                                "System": "2",
                                "PercentageAddup": "True"
                            }
                        },
                        "node6": { 
                            "RoleID": "00000000-0000-0000-0000-000000000033",
                            "UserID": "WebServices",
                            "PartyID": "cc6ef1d8-d0ad-4044-9bd8-6c34c16eec5f",
                            "Percentage": "1.00"
                        }
                    },
                    {
                        "node4": { 
                            "node5": { 
                                "Code": "32",
                                "Image": "ffffffff-ffff-ffff-ffff-ffffffffffff",
                                "System": "2",
                                "PercentageAddup": "False"
                            }
                        },
                        "node6": { 
                            "RoleID": "00000000-0000-0000-0000-000000000118",
                            "UserID": "WebServices",
                            "PartyID": "10d8e781-a4d7-4a17-a4a0-eb7ac71b75b4",
                            "Percentage": "1"
                        }
                    }
                ]
            },
            "node7": [ 
                {
                    "node8": { 
                        "node9": { 
                            "Code": "8",
                            "Image": "ffffffff-ffff-ffff-ffff-ffffffffffff",
                            "System": "2",
                            "PercentageAddup": "True"
                        }
                    },
                    "node10": { 
                        "RoleID": "00000000-0000-0000-0000-000000000143",
                        "UserID": "WebServices",
                        "PartyID": "10d8e781-a4d7-4a17-a4a0-eb7ac71b75b4",
                        "Relationship": "Self"
                    }
                },
                {
                    "node8": { 
                        "node9": { 
                            "Code": "31",
                            "Image": "ffffffff-ffff-ffff-ffff-ffffffffffff",
                            "System": "2",
                            "PercentageAddup": "False"
                        }
                    },
                    "node10": { 
                        "RoleID": "00000000-0000-0000-0000-000000000156",
                        "UserID": "WebServices",
                        "PartyID": "10d8e781-a4d7-4a17-a4a0-eb7ac71b75b4",
                        "Relationship": "Self"
                    }
                }
            ],
            "node11": { 
                "node12": { 
                    "node13": { 
                        "Code": "38",
                        "Image": "ffffffff-ffff-ffff-ffff-ffffffffffff",
                        "System": "2",
                        "PercentageAddup": "False"
                    }
                },
                "node14": { 
                    "RoleID": "00000000-0000-0000-0000-000000000170",
                    "UserID": "WebServices",
                    "Percentage": "1"
                }
            }
        }
    }
    '::JSONB AS payload
    )
SELECT 
    polnum,
    (jq ->> 'value') AS code_value,
    (jq ->> 'path') AS code_path
FROM json_table,
     jsonb_path_query(payload, 'strict $.**.Code', '{"wrapper": true}') AS jq;

查询结果 #

polnum   | code_value | code_path
---------|------------|---------------------------------------
PL123456 | "34"       | "$.node1.node2.node3[0].node4.node5.Code"
PL123456 | "32"       | "$.node1.node2.node3[1].node4.node5.Code"
PL123456 | "8"        | "$.node1.node7[0].node8.node9.Code"
PL123456 | "31"       | "$.node1.node7[1].node8.node9.Code"
PL123456 | "38"       | "$.node1.node11.node12.node13.Code"

补充说明 #

• jsonb_path_query的第三个参数'{"wrapper": true}'是关键，它会将每个匹配的节点包装成包含value和path的JSON对象。
• 若需要更简洁的路径格式，可对code_path字段进行字符串处理，比如去掉开头的$.等。

内容的提问来源于stack exchange，提问作者adbdkb