MySQL中如何从JSON列指定层级查询含特定值的行？

我有一个JSON列users.roles，结构如下：

{
    "tenant-foo":
    [
        "role-aaa",
        "role-bbb"
    ],
    "tenant-bar":
    [
        "role-bbb",
        "role-ccc"
    ],
    "tenant-buz":
    [
        "role-aaa",
        "role-ccc"
    ],
    ...
    "tenant-...": [
        "role-...",
        ...
    ]
}

语义说明：多租户系统中的用户角色，一个用户可被分配至多个租户，且在每个租户下拥有不同角色。

需求：仅筛选出roles列中存在role-aaa的用户行，能否仅通过SELECT语句结合JSON系列函数实现？如果可以，具体如何操作？

可以实现，以下是主流关系型数据库的具体实现方式：

MySQL 方案 #

方法1：精准匹配第二层数组 #

利用JSON_TABLE将JSON对象的键（租户名）展开为行，再通过JSON_CONTAINS检查对应租户下的角色数组是否包含目标值：

SELECT u.*
FROM users u
JOIN JSON_TABLE(
    JSON_KEYS(u.roles),
    '$[*]' COLUMNS(tenant VARCHAR(255) PATH '$')
) AS tenant_rows
WHERE JSON_CONTAINS(u.roles->>CONCAT('$.', tenant_rows.tenant), '"role-aaa"');

方法2：快速全局查找 #

如果无需严格限制仅在第二层数组（比如确认JSON结构不会在其他层级出现role-aaa），可以直接用JSON_SEARCH判断是否存在目标值：

SELECT *
FROM users
WHERE JSON_SEARCH(roles, 'all', 'role-aaa') IS NOT NULL;

注：JSON_SEARCH会遍历整个JSON结构查找匹配值，若JSON存在其他层级可能出现该值的情况，优先用方法1。

PostgreSQL 方案 #

方法1：展开JSON对象筛选 #

通过jsonb_each将JSON对象拆分为租户名和对应角色数组的行，再用@>操作符检查数组是否包含目标角色：

SELECT DISTINCT u.*
FROM users u
JOIN jsonb_each(u.roles::jsonb) AS tenant_roles(tenant, role_list)
WHERE role_list @> '["role-aaa"]'::jsonb;

方法2：JSON路径查询 #

使用jsonb_path_exists通过JSON路径表达式直接匹配第二层数组包含目标值的情况：

SELECT *
FROM users
WHERE jsonb_path_exists(u.roles::jsonb, '$.values() ? (@ contains "role-aaa")');

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