如何使用ActiveRecord查询存储为文本的JSON字段指定键值

嘿，这个需求太常见了！因为不同数据库对文本存储的JSON字段处理语法有差异，我给你分几种主流数据库的情况来写WHERE查询语句：

MySQL（5.7及以上版本） #

如果你的json_data是TEXT类型，MySQL的JSON函数可以直接解析它。你有两种写法可选：

• 标准函数写法：

  WHERE JSON_UNQUOTE(JSON_EXTRACT(json_data, '$.infoOnProgram.universityName')) = 'Harvard'
  

• 简化运算符写法（更简洁直观）：

  WHERE json_data->>'$.infoOnProgram.universityName' = 'Harvard'
  

  这里->>等价于JSON_UNQUOTE(JSON_EXTRACT(...))，会直接返回不带引号的字符串值。

PostgreSQL #

PostgreSQL需要先把TEXT类型的字段转换为JSON类型再提取值，写法如下：

WHERE (json_data::json)->'infoOnProgram'->>'universityName' = 'Harvard'

或者用函数式写法：

WHERE json_extract_path_text(json_data::json, 'infoOnProgram', 'universityName') = 'Harvard'

如果数据量较大，建议把字段改成jsonb类型，它支持索引，查询性能会提升很多。

SQL Server #

SQL Server用JSON_VALUE函数直接从文本类型的JSON字符串中提取指定路径的值：

WHERE JSON_VALUE(json_data, '$.infoOnProgram.universityName') = 'Harvard'

额外注意事项 #

• 务必确保json_data字段里的内容是合法的JSON格式，否则这些查询可能会抛出语法错误。
• 如果需要频繁查询这个JSON路径的内容，建议将字段改为数据库原生的JSON类型（比如MySQL的JSON、PostgreSQL的jsonb），还可以针对这个路径创建索引，大幅提升查询效率。

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