从JSON类型列筛选含coordinates节点的SQL查询无结果问题

你的SQL语句存在两个关键问题，导致无法返回正确结果：

• 不必要使用json_each(payload)：该函数会将payload这个JSON对象拆分为键值对多行，但你需要筛选的是完整的表行，拆分后关联逻辑错误，会过滤掉符合条件的数据。
• JSON路径错误：$.data.geo.是无效路径，正确的路径应该直接定位到coordinates节点。

下面是修正后的SQL语句，根据你使用的数据库选择对应写法：

通用写法（支持json_extract的数据库：SQLite、MySQL等） #

SELECT * 
FROM raw_data
WHERE json_extract(payload, '$.data.geo.coordinates') IS NOT NULL
LIMIT 20;

MySQL 8.0+ 精准判断写法 #

使用JSON_CONTAINS_PATH可以更精准地判断节点是否存在（即使节点值为null也能区分）：

SELECT * 
FROM raw_data
WHERE JSON_CONTAINS_PATH(payload, 'one', '$.data.geo.coordinates') = 1
LIMIT 20;

PostgreSQL 写法 #

如果使用PostgreSQL，可直接用json_exists：

SELECT * 
FROM raw_data
WHERE json_exists(payload, '$.data.geo.coordinates')
LIMIT 20;

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