如何在WHERE子句中使用JSON数组列作为查询条件

不同数据库对JSON数组的查询语法存在差异，以下是主流数据库的具体实现方式：

SQL Server #

你尝试的JSON_VALUE写法可正常生效，但需注意显式数据类型转换——JSON_VALUE默认返回字符串类型，直接与数字比较可能触发不符合预期的隐式转换，修正后的语句：

SELECT * 
FROM student 
WHERE CAST(JSON_VALUE(marks, '$.0.marks') AS INT) > 4

若数组包含多个元素，需筛选任意元素满足条件的行，可通过OPENJSON展开数组后判断：

SELECT s.*
FROM student s
CROSS APPLY OPENJSON(s.marks) WITH (marks INT '$.marks') j
WHERE j.marks > 4
GROUP BY s.ID, s.Students, s.Date, s.marks

MySQL #

使用JSON_EXTRACT或简化的->>操作符提取JSON值，同样需显式转换类型：

SELECT * 
FROM student 
WHERE JSON_UNQUOTE(JSON_EXTRACT(marks, '$[0].marks')) + 0 > 4
-- 更简洁的写法
SELECT * 
FROM student 
WHERE marks->>'$[0].marks' + 0 > 4

处理多元素数组时，借助JSON_TABLE展开筛选：

SELECT s.*
FROM student s
JOIN JSON_TABLE(
    s.marks,
    '$[*]' COLUMNS(marks INT PATH '$.marks')
) j
WHERE j.marks > 4
GROUP BY s.ID, s.Students, s.Date, s.marks

PostgreSQL #

通过->>提取文本后转换为整数类型：

SELECT * 
FROM student 
WHERE (marks->0->>'marks')::INT > 4

针对多元素数组，使用jsonb_array_elements展开去重：

SELECT DISTINCT s.*
FROM student s
JOIN jsonb_array_elements(s.marks::jsonb) j
WHERE (j->>'marks')::INT > 4

你最初的语句未生效，大概率是隐式类型转换导致的异常，显式转换数据类型后即可正确筛选出目标行。

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