如何在PostgreSQL中从多层嵌套JSON数组中查询指定条件的子对象

嘿，我来帮你搞定PostgreSQL里多层嵌套JSON数组的查询问题！针对你给出的嵌套菜单结构，这里有两种实用的方案，能精准定位到menuId为AOIEnquiryActivity且isEnabled为yes的子对象：

这种方法逻辑清晰，不管你的菜单嵌套多少层都能处理，还方便后续扩展筛选条件。假设你的表名为menu_table，存储JSON数据的字段是menu_data（推荐用jsonb类型，比json支持更多操作且性能更好），查询语句如下：

WITH RECURSIVE menu_recursive AS (
    -- 初始步骤：拆分最外层的JSON数组，得到第一层菜单对象
    SELECT 
        jsonb_array_elements(menu_data) AS menu_item
    FROM menu_table
    
    UNION ALL
    
    -- 递归步骤：逐层拆分每个菜单的subMenu数组，直到没有子菜单
    SELECT 
        jsonb_array_elements(menu_item->'subMenu') AS menu_item
    FROM menu_recursive
    -- 只有当subMenu是非空数组时才继续递归
    WHERE jsonb_typeof(menu_item->'subMenu') = 'array' AND menu_item->'subMenu' != '[]'::jsonb
)
-- 最终筛选符合条件的子对象
SELECT menu_item
FROM menu_recursive
WHERE 
    menu_item->>'menuId' = 'AOIEnquiryActivity'
    AND lower(menu_item->>'isEnabled') = 'yes'; -- 转小写避免大小写不匹配（比如示例里的"Yes"和"yes"）

代码说明： #

• 递归CTE的menu_recursive会先拆解最外层数组，然后不断深入每个菜单的subMenu，把所有层级的菜单对象都拉平成一行行数据。
• 最后通过WHERE子句精准筛选目标对象，用lower()处理isEnabled是为了兼容大小写不一致的情况，避免漏匹配。

如果你用的是PostgreSQL 12及以上版本，jsonb_path_query可以用JSON路径语法直接穿透多层嵌套，代码更简洁：

如果能确定嵌套层数（比如你示例里是3层），可以写明确的路径：

SELECT jsonb_path_query(
    menu_data,
    '$[*].subMenu[*].subMenu[*] ? (@.menuId == "AOIEnquiryActivity" && lower(@.isEnabled) == "yes")'
) AS target_menu
FROM menu_table;

如果嵌套层数不确定（可能有更多层级），用递归路径表达式匹配所有层级的subMenu：

SELECT jsonb_path_query(
    menu_data,
    '$..subMenu[*] ? (@.menuId == "AOIEnquiryActivity" && lower(@.isEnabled) == "yes")'
) AS target_menu
FROM menu_table;

代码说明： #

• $..subMenu[*]表示遍历JSON结构中所有层级的subMenu数组元素；
• ? (...)是筛选条件，@代表当前遍历到的元素，直接判断menuId和isEnabled的条件即可。

小提示： #

如果你的字段是json类型而非jsonb，可以先做类型转换：menu_data::jsonb，不过还是建议把字段改成jsonb类型，长期使用更高效。

内容的提问来源于stack exchange，提问作者Maly Mohsem Ahmed