如何用SQL从JSON数组中筛选指定条件的特定值

针对不同SQL数据库，这里提供几种实用的实现方式：

SQL Server #

利用OPENJSON将JSON数组展开为行数据，再筛选PersonCat = '2'的记录，取对应的Name。如果没有匹配项，子查询会自动返回NULL：

-- 直接针对JSON字符串的查询
SELECT 
    (SELECT TOP 1 Name 
     FROM OPENJSON('{ "Persons": [{"PersonCat":"1","Name":"John"},{"PersonCat":"2","Name":"Henry"}]}','$.Persons')
     WITH (
         PersonCat VARCHAR(10) '$.PersonCat',
         Name VARCHAR(50) '$.Name'
     )
     WHERE PersonCat = '2') AS SelectedPerson;

-- 如果是表中JSON列的查询（假设表为PeopleTable，JSON列名为PersonData）
SELECT 
    (SELECT TOP 1 Name 
     FROM OPENJSON(pt.PersonData,'$.Persons')
     WITH (
         PersonCat VARCHAR(10) '$.PersonCat',
         Name VARCHAR(50) '$.Name'
     )
     WHERE PersonCat = '2') AS SelectedPerson
FROM PeopleTable pt;

MySQL #

可以用JSON_TABLE展开数组后筛选，或者结合JSON_SEARCH定位目标路径再提取：

方法1：使用JSON_TABLE #

SELECT 
    COALESCE(
        (SELECT Name 
         FROM JSON_TABLE(
             '{ "Persons": [{"PersonCat":"1","Name":"John"},{"PersonCat":"2","Name":"Henry"}]}',
             '$.Persons[*]' COLUMNS (
                 PersonCat VARCHAR(10) PATH '$.PersonCat',
                 Name VARCHAR(50) PATH '$.Name'
             )
         ) AS jt
         WHERE PersonCat = '2'),
        NULL
    ) AS SelectedPerson;

方法2：使用JSON_SEARCH+JSON_EXTRACT #

SELECT 
    JSON_UNQUOTE(
        JSON_EXTRACT(
            '{ "Persons": [{"PersonCat":"1","Name":"John"},{"PersonCat":"2","Name":"Henry"}]}',
            REPLACE(JSON_UNQUOTE(JSON_SEARCH('{ "Persons": [{"PersonCat":"1","Name":"John"},{"PersonCat":"2","Name":"Henry"}]}', 'one', '2', NULL, '$.Persons[*].PersonCat')), 'PersonCat', 'Name')
        )
    ) AS SelectedPerson;

PostgreSQL #

借助jsonb_to_recordset将JSON数组转换为行集后筛选：

-- 直接针对JSON字符串的查询
SELECT 
    COALESCE(
        (SELECT Name 
         FROM jsonb_to_recordset('{ "Persons": [{"PersonCat":"1","Name":"John"},{"PersonCat":"2","Name":"Henry"}]}'::jsonb->'Persons')
         AS jt(PersonCat VARCHAR(10), Name VARCHAR(50))
         WHERE PersonCat = '2'),
        NULL
    ) AS SelectedPerson;

-- 表中JSON列的查询（假设表为people_table，JSON列名为person_data）
SELECT 
    COALESCE(
        (SELECT Name 
         FROM jsonb_to_recordset(person_data::jsonb->'Persons')
         AS jt(PersonCat VARCHAR(10), Name VARCHAR(50))
         WHERE PersonCat = '2'),
        NULL
    ) AS SelectedPerson
FROM people_table;

核心逻辑都是先将JSON数组转换为关系型的行数据，再通过常规的WHERE条件筛选目标类别，获取对应的姓名。如果没有匹配的类别，查询会返回NULL，完全符合需求。

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