PostgreSQL多级JSON列中使用LIKE语句搜索名称的实现方法

你的问题出在json_object_keys是返回多行的集合函数（set-returning function），WHERE子句无法直接处理这类函数的输出——它需要的是针对单行的布尔条件，而不是多行结果。下面给你两种可行的解决方法：

方法1：用json_each展开JSON对象并过滤 #

这种方法适用于json类型的列，通过横向连接展开JSON对象的键值对，然后检查每个语言对应的name字段：

SELECT DISTINCT p.*
FROM places p
JOIN json_each(p.translations) AS lang(locale_key, lang_data)
ON lang_data->>'name' LIKE '%Namur%';

说明： #

• json_each(p.translations)会把translations对象拆成多行，每行包含一个语言代码（locale_key，比如en、de）和对应的JSON数据（lang_data，比如{"locale":"en","name":"Namur"}）
• lang_data->>'name'提取出每个语言下的name字符串，用LIKE匹配目标内容
• DISTINCT用来避免同一行因为多个语言匹配而被重复返回

方法2：用jsonb_path_exists（推荐，适合jsonb类型） #

如果你的translations列是jsonb类型（PostgreSQL中更推荐用于查询的JSON类型），可以用JSON路径查询直接匹配所有层级的name字段，无需展开：

SELECT *
FROM places p
WHERE jsonb_path_exists(p.translations, '$.**.name ? (@ like_regex "Namur")');

如果你的列是json类型，可以先转成jsonb再查询：

SELECT *
FROM places p
WHERE jsonb_path_exists(p.translations::jsonb, '$.**.name ? (@ like "%Namur%")');

说明： #

• $.**.name是JSON路径表达式，**表示遍历所有层级的子对象，找到所有name字段
• ? (@ like "%Namur%")是过滤条件，直接用类LIKE语法匹配包含"Namur"的name值

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