时间戳

首页 > 解决方案 > 在 mysql json 列中搜索以检查数组同一索引上的多个条件

mysql - 在 mysql json 列中搜索以检查数组同一索引上的多个条件

问题描述

我正在使用MYSql服务器 8.0.17。

我想使用uId= 'UR000001'以及VIEW = 'Y'从安全列（如表中所示）获取记录。

Viewid          Security
VW0000000002    {"security": [{"uId": "UR000001", "edit": "N", "view": "Y"}, {"uId": "UR000002", "edit": "N", "view": "Y"}]}
VW0000000013    {"security": [{"uId": "UR000001", "edit": "N", "view": "N"}, {"uId": "UR000002", "edit": "N", "view": "Y"}]}
VW0000000014    {"security": [{"uId": "UR000001", "edit": "N", "view": "Y"}, {"uId": "UR000002", "edit": "N", "view": "Y"}]}

JSON_SEARCH函数搜索我不想要的记录的所有数组元素。

这是我尝试过的查询，但它返回所有匹配的结果（uID='UR000001' OR View='Y'）

SELECT viewid, 
       Json_search(`security`, 'one', 'UR000001', NULL, '$.security[*].uId'), 
       Json_search(`security`, 'one', 'Y', NULL, '$.security[*].view') 
FROM   vw_viewmaster 
WHERE  Json_search(`security`, 'one', 'UR000001', NULL, '$.security[*].uId') 
       AND Json_search(`security`, 'one', 'Y', NULL, '$.security[*].view');

实际结果：（uID='UR000001' 或 View='Y'）

VW0000000002    "$.security[0].uId" "$.security[0].view"
VW0000000013    "$.security[0].uId" "$.security[1].view"
VW0000000014    "$.security[0].uId" "$.security[0].view"

预期结果：（uID='UR000001' 和 View='Y'）

VW0000000002    "$.security[0].uId" "$.security[0].view"
VW0000000014    "$.security[0].uId" "$.security[0].view"

标签： mysqlsqlarraysjson

解决方案

在 MySQL 8.0 中，您可以使用方便的 JSON 函数json_table()将 json 数组转换为行。然后，您可以搜索结果集。

以下查询为您提供viewid了至少一个数组元素的属性uId等于'UR000001'且属性view为的所有 s 'Y'：

select v.viewid
from vw_viewmaster v
where exists (
    select 1
    from json_table(
        v.security -> '$.security',
        '$[*]'
        columns(
            uid  varchar(50) path '$.uId',
            edit varchar(1)  path '$.edit',
            view varchar(1)  path '$.view'
        )
    ) x
    where x.uid = 'UR000001' and x.view = 'Y'
);

对于您的数据集，这会产生：

| viewid       |
| ------------ |
| VW0000000002 |
| VW0000000014 |

如果您想要匹配数组对象的详细信息，则：

select v.viewid, x.*
from vw_viewmaster v
cross join json_table(
    v.security -> '$.security',
    '$[*]'
    columns(
        rowid for ordinality,
        uid   varchar(50) path '$.uId',
        edit  varchar(1)  path '$.edit',
        view  varchar(1)  path '$.view'
    )
) x
where x.uid = 'UR000001' and x.view = 'Y'

作为奖励，rowid为您提供 JSON 数组中匹配对象的索引（第一个对象的索引为 1）。

这产生：

| viewid       | rowid | uid      | edit | view |
| ------------ | ----- | -------- | ---- | ---- |
| VW0000000002 | 1     | UR000001 | N    | Y    |
| VW0000000014 | 1     | UR000001 | N    | Y    |

但是请注意，如果数组中有多个对象满足条件，则上述查询将在原始表中每行生成多行（这就是我exists在第一个查询中使用的原因）。

推荐阅读