JSON 包含查询是什么
判断 JSON 文档里是否包含某个值或结构：MySQL 用 JSON_CONTAINS 与 JSON_CONTAINS_PATH，PostgreSQL 用 jsonb 的 @> 包含运算符与 ? 系列键存在判断。它适合标签、属性这类半结构化数据，但索引必须配套才不至于全表扫描。
基本信息
| MySQL 包含 | JSON_CONTAINS(target, candidate[, path]) |
|---|---|
| MySQL 路径判断 | JSON_CONTAINS_PATH(doc, 'one' 或 'all', '$.a', '$.b') |
| MySQL 数组成员 | value MEMBER OF (json_array)，或 JSON_CONTAINS |
| PostgreSQL 包含 | tags @> '["vip"]'::jsonb |
| PostgreSQL 键判断 | ? 键存在、?| 任一键存在、?& 全部键存在 |
| 索引 | MySQL 多值索引（8.0.17+）；PG 用 GIN |
MySQL 写法
MySQL 写法
-- 数组里是否包含字符串 vip：candidate 是 JSON 文本，字符串要带双引号
SELECT * FROM users WHERE JSON_CONTAINS(tags, '"vip"');
-- 路径判断：all 表示两个路径都必须存在
SELECT * FROM users WHERE JSON_CONTAINS_PATH(doc, 'all', '$.a', '$.b');
-- 8.0.17+ 的多值索引，可加速数组包含查询
CREATE INDEX idx_tags ON users ((CAST(tags AS CHAR(16) ARRAY)));
SELECT * FROM users WHERE 'vip' MEMBER OF (tags);PostgreSQL 写法
PostgreSQL 写法
SELECT * FROM users WHERE tags @> '["vip"]'::jsonb; -- 数组包含
SELECT * FROM users WHERE doc ? 'status'; -- 顶层键存在
SELECT * FROM users WHERE doc ?| ARRAY['a','b']; -- 任一键存在
SELECT * FROM users WHERE doc ?& ARRAY['a','b']; -- 全部键存在
-- 包含查询配 GIN 索引；jsonb_path_ops 更省空间，但只支持 @>
CREATE INDEX idx_doc ON users USING gin (doc jsonb_path_ops);两库对照
| 需求 | MySQL 8 | PostgreSQL |
|---|---|---|
| 数组包含某值 | JSON_CONTAINS(tags,'"vip"') | tags @> '[\"vip\"]'::jsonb |
| 判断键存在 | JSON_CONTAINS_PATH | doc ? 'key' |
| 多个键任一存在 | JSON_CONTAINS_PATH 配 one | doc ?| ARRAY[…] |
| 多个键全部存在 | JSON_CONTAINS_PATH 配 all | doc ?& ARRAY[…] |
| 索引支持 | 多值索引（8.0.17+） | GIN 索引 |
典型使用场景
- 用户标签筛选：找出含某个标签的用户群
- 属性过滤：按 JSON 里的渠道、来源、设备类型筛选
- 数据完整性检查：确认必备的键都存在
- 风控规则：把命中某个特征的记录单独拉出来
- 配置查询：找出开启了某个开关的对象
- 排查脏数据：找出缺少关键字段的记录
- 权限校验：确认用户的权限数组里包含所需角色
- 埋点分析：按事件参数里的取值筛出特定行为的人群
MySQL 的 candidate 参数必须是合法 JSON 文本：查字符串要写成 '"vip"'（带双引号），少写引号会成为非法 JSON 而报错，静默匹配不到的情况更难查。
包含判断是“结构包含”不是字符串包含：PG 的 @> 要求类型对齐，判断字符串要写成 '"vip"'::jsonb；MySQL 的 candidate 同理，两边都把值包成合法 JSON 才不会误判。
把 json（文本）列当 jsonb 用会付出代价：PostgreSQL 的 json 类型不支持 @>，也无法使用 GIN 索引，每次查询都要重新解析。建表时就选 jsonb，后期改类型需要重写整表。