如何在数据库JSON字段中查询含/不含指定键值对的记录？

不同数据库对JSON字段的查询语法有所差异，以下是主流数据库的实现方案：

MySQL #

检查键存在 #

使用JSON_CONTAINS_PATH()函数，'one'表示只要目标键存在即可（若要检查多个键都存在，改用'all'）：

SELECT * FROM `foo_db`.`foo_table`
WHERE JSON_CONTAINS_PATH(`foo_field`, 'one', '$.bar_key') = 1;

检查键不存在 #

对存在判断取反，或直接判断返回值为0：

SELECT * FROM `foo_db`.`foo_table`
WHERE JSON_CONTAINS_PATH(`foo_field`, 'one', '$.bar_key') = 0;

-- 或使用NOT取反
SELECT * FROM `foo_db`.`foo_table`
WHERE NOT JSON_CONTAINS_PATH(`foo_field`, 'one', '$.bar_key');

PostgreSQL #

PostgreSQL的jsonb类型（推荐使用）支持直接用操作符判断：

检查键存在 #

使用?操作符：

SELECT * FROM foo_db.foo_table
WHERE foo_field ? 'bar_key';

检查键不存在 #

用NOT取反，或使用!操作符（部分版本支持）：

SELECT * FROM foo_db.foo_table
WHERE NOT (foo_field ? 'bar_key');

-- 或
SELECT * FROM foo_db.foo_table
WHERE foo_field ! 'bar_key';

如果是多层嵌套的键（如parent.bar_key），可使用jsonb_path_exists()：

SELECT * FROM foo_db.foo_table
WHERE jsonb_path_exists(foo_field, '$.parent.bar_key');

SQL Server #

通过判断JSON_QUERY()的返回值是否为NULL来识别键是否存在：

检查键存在 #

SELECT * FROM foo_db.foo_table
WHERE JSON_QUERY(foo_field, '$.bar_key') IS NOT NULL;

检查键不存在 #

SELECT * FROM foo_db.foo_table
WHERE JSON_QUERY(foo_field, '$.bar_key') IS NULL;

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