怎样用SQL JSON函数查询数组中的指定元素

为啥JSON_CONTAINS查不到数组值？关键在于字段是不是JSON类型，以及路径有没有写'$[*]'。一定要确保字段是JSON类型，并且明确指定路径，比如JSON_CONTAINS(col, "val", '$[*]')，如果是数字，就不需要引号啦。

JSON_CONTAINS 查不到数组里的值？先确认字段类型和路径写法

MySQL的JSON_CONTAINS在查询数组时，为何常常失效？并非函数本身有问题，而是大家经常踩两个坑：一是字段未定义为JSON类型，仅用VARCHAR存了个字符串；二是路径参数漏写了$[*]。该函数默认仅匹配顶层结构，不会自动遍历数组元素。

• 确保字段类型是 JSON，不是 VARCHAR 或 TEXT；否则 JSON_CONTAINS 返回 NULL，条件永远不成立
• 查数组中是否含某个值，必须显式指定路径：JSON_CONTAINS(json_col, '"value"', '$[*]') —— 注意引号嵌套和 $[*] 通配符
• 如果值是数字（比如 42），不要加引号：JSON_CONTAINS(json_col, 42, '$[*]')

想取数组里第 N 个元素？用 JSON_EXTRACT + 数组下标

JSON_EXTRACT 支持标准数组下标访问，但注意 MySQL 数组下标从 0 开始，且越界返回 NULL，不会报错。

• 取第一个元素：JSON_EXTRACT(data, '$[0]')
• 取最后一个（需配合 JSON_LENGTH）：JSON_EXTRACT(data, CONCAT('$[', JSON_LENGTH(data) - 1, ']'))
• 结果带双引号（字符串）或无引号（数字/布尔），如需去引号用 JSON_UNQUOTE()，例如：JSON_UNQUOTE(JSON_EXTRACT(data, '$[0]'))
• 如果字段可能为空或不是数组，JSON_EXTRACT 返回 NULL，建议用 COALESCE 做兜底

要筛选出数组里包含某值的所有记录？别只靠 JSON_CONTAINS

当需要 WHERE 条件匹配数组内容时，JSON_CONTAINS 可用，但性能差、无法利用索引；更稳的方式是结合 JSON_TABLE（MySQL 8.0+）做展开再过滤。

• JSON_CONTAINS 写法简单但全表扫描：WHERE JSON_CONTAINS(tags, '"urgent"', '$[*]')
• 用 JSON_TABLE 展开后可走索引（需对生成列建索引）：

  SELECT t.* FROM orders t,
  JSON_TABLE(t.tags, '$[*]' COLUMNS(tag VARCHAR(20) PATH '$')) AS jt
  WHERE jt.tag = 'urgent'

• 注意 JSON_TABLE 要求输入是合法 JSON 数组，空值或非数组会报错，建议加 JSON_VALID(t.tags) 安全校验

PostgreSQL 怎么查 JSONB 数组？操作符比 MySQL 更直观

PostgreSQL 的 jsonb 类型原生支持数组操作，不用记路径表达式，常用 @>（包含）、?（键存在）、@@（全文检索）等操作符。

• 查数组是否含字符串：tags @> '["urgent"]'::jsonb
• 查数组是否含某个键（用于对象数组）：items @> '[{"type":"error"}]'::jsonb
• 提取数组元素并展开成行：SELECT jsonb_array_elements(tags) FROM logs
• 如果数组元素是对象，想按字段过滤：WHERE tags @? '$[*] ? (@.status == "failed")'（使用 JSONPath）

实际用的时候，数组深度、空值、类型混杂（比如 [1,"2",true]）最容易导致意外结果。MySQL 不做类型隐式转换，PostgreSQL 也要求严格匹配，别指望它自动帮你转字符串。