如何使用MySQL的JSON_SEARCH基于多属性检索JSON数组元素？

要从你的JSON变量中同时匹配fromUnit和toUnit两个属性并提取对应元素，推荐使用JSON_TABLE函数将JSON数组转换为关系型临时表，再通过常规SQL条件筛选，这种方式直观且灵活，适合多条件匹配场景。

方法一：使用JSON_TABLE（推荐） #

-- 定义JSON变量
SET @unitConversions = '{
    "unitConversions": [
    {
    "fromUnit": "ounce",
    "toUnit": "cup",
    "amount": "10"
    },
    {
    "fromUnit": "ounce",
    "toUnit": "pound",
    "amount": "16"
    },
    {
    "fromUnit": "teaspoon",
    "toUnit": "ounce",
    "amount": "4"
    }
    ]
}';

-- 检索匹配的JSON元素
SELECT JSON_OBJECT(
    'fromUnit', j.fromUnit,
    'toUnit', j.toUnit,
    'amount', j.amount
) AS matched_conversion
FROM JSON_TABLE(
    @unitConversions,
    '$.unitConversions[*]' COLUMNS(
        fromUnit VARCHAR(50) PATH '$.fromUnit',
        toUnit VARCHAR(50) PATH '$.toUnit',
        amount VARCHAR(50) PATH '$.amount'
    )
) j
WHERE j.fromUnit = 'ounce' AND j.toUnit = 'pound';

执行后会直接返回目标元素：

{
    "fromUnit": "ounce",
    "toUnit": "pound",
    "amount": "16"
}

方法二：结合JSON_SEARCH与JSON_EXTRACT（适合简单场景） #

如果不想展开数组，也可以通过JSON_SEARCH定位符合toUnit条件的元素路径，再验证fromUnit匹配性后提取元素：

SELECT JSON_EXTRACT(
    @unitConversions,
    JSON_UNQUOTE(
        JSON_SEARCH(
            @unitConversions,
            'one', -- 仅返回第一个匹配项，需所有匹配则改为'all'
            'pound',
            NULL,
            '$.unitConversions[*].toUnit'
        ) REPLACE('.toUnit', '')
    )
) AS matched_conversion
WHERE JSON_CONTAINS(
    @unitConversions,
    JSON_OBJECT('fromUnit', 'ounce'),
    JSON_UNQUOTE(
        JSON_SEARCH(
            @unitConversions,
            'one',
            'pound',
            NULL,
            '$.unitConversions[*].toUnit'
        ) REPLACE('.toUnit', '')
    )
);

补充说明 #

• JSON_TABLE是MySQL 8.0及以上版本支持的函数，能将JSON数组拆解为行数据，让你可以像操作普通表一样添加多条件筛选，扩展性更强。
• 若使用低于8.0的MySQL版本，可通过JSON_CONTAINS结合路径遍历实现，但逻辑会更繁琐。

内容的提问来源于stack exchange，提问作者Asp net core developer