如何将JSON数组字段与IN子句结合实现多元素匹配？

你可以通过两种方式实现匹配JSON数组中任意元素的需求：

方法1：使用JSON_SEARCH函数 #

直接检查数组中是否存在目标值，只要有一个匹配就返回记录：

SELECT *
FROM housedata
WHERE JSON_SEARCH(properties->'$.Type', 'one', 'House') IS NOT NULL
   OR JSON_SEARCH(properties->'$.Type', 'one', 'Flat') IS NOT NULL;

JSON_SEARCH的第二个参数'one'表示找到第一个匹配项就停止搜索，只要返回结果不为NULL，就说明数组里存在目标值。

方法2：用JSON_TABLE拆分数组（更灵活） #

把JSON数组转换成行数据，再通过子查询判断是否有匹配元素，适合目标值较多的场景：

SELECT *
FROM housedata h
WHERE EXISTS (
    SELECT 1
    FROM JSON_TABLE(
        h.properties->'$.Type',
        '$[*]' COLUMNS(type_val VARCHAR(255) PATH '$')
    ) AS jt
    WHERE jt.type_val IN ('House', 'Flat')
);

JSON_TABLE会把$.Type对应的数组拆成多行，每行对应一个数组元素，之后通过EXISTS子查询检查这些元素里是否有在目标列表中的值，只要有一个匹配就会返回原表的记录。

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