如何在SQL中查询JSON列匹配default_locale条件的记录

问题根因 #

你写的查询语句失效的核心原因是default_locale是嵌套在JSON结构的_translated二级节点下的属性，你直接从details根节点提取default_locale自然匹配不到对应值。

常用数据库解决方案 #

PostgreSQL 方案（你使用的->是PG JSON/JSONB类型的标准运算符，优先适配该场景） #

1. 逻辑验证查询（查询default_locale为es_ES的记录，可先执行验证逻辑正确性）

SELECT details FROM locations 
WHERE details->_translated->>'default_locale' = 'es_ES';

说明：

• -> 用于提取JSON节点，返回JSON/JSONB类型
• ->> 直接提取JSON节点值并转换为文本格式，可直接和字符串做等值判断

2. 符合需求的完整查询

同时匹配「default_locale取值为en_US」、「不存在default_locale字段」两类记录：

SELECT details FROM locations 
WHERE 
  -- 条件1：default_locale值为en_US
  details->_translated->>'default_locale' = 'en_US'
  OR
  -- 条件2：_translated节点下不存在default_locale键
  (details->_translated) ? 'default_locale' = FALSE;

如果你的details字段是JSON类型（而非JSONB类型），判断键不存在的条件可替换为：

json_extract_path(details, '_translated', 'default_locale') IS NULL

MySQL 方案 #

完整查询语句（适配MySQL 5.7及以上版本）

SELECT details FROM locations
WHERE
  -- 条件1：default_locale值为en_US
  details->>'$._translated.default_locale' = 'en_US'
  OR
  -- 条件2：不存在default_locale字段
  JSON_CONTAINS_PATH(details, 'one', '$._translated.default_locale') = 0;

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