如何在PostgreSQL的jsonb路径中匹配任意键？

方法1：使用jsonb_each_text展开键值对（兼容低版本） #

这种方法通过将foo下的所有键值对展开，逐个检查对应bar字段的值，适合PostgreSQL 9.4及以上版本（支持jsonb的最早版本）：

SELECT DISTINCT t.*
FROM "myTable" t
JOIN jsonb_each_text(t."myColumn"->'foo') AS foo_keys(key_name, value_json)
ON (value_json::jsonb)->>'bar' = 'myValueX';

• 说明：jsonb_each_text会把foo对象拆分成多行，每行对应一个键（key_name）和其对应的JSON字符串值（value_json）；
• 用(value_json::jsonb)->>'bar'提取每个子对象的bar字段，判断是否等于目标值；
• 添加DISTINCT是为了避免同一条记录因多个子键匹配而重复返回。

方法2：使用jsonb_path_exists（简洁高效，PostgreSQL 12+） #

PostgreSQL 12及以上版本支持JSON路径表达式，你可以直接用通配符*匹配任意键，和你设想的伪代码逻辑几乎一致：

SELECT *
FROM "myTable"
WHERE jsonb_path_exists("myColumn", '$.foo.*.bar ? (@ == "myValueX")');

• 说明：$.foo.*.bar表示foo下任意子键的bar字段；
• ? (@ == "myValueX")是过滤条件，仅当该字段值等于myValueX时返回对应记录；
• 若需要模糊匹配（包含指定文本），可以替换为正则匹配：

  SELECT *
  FROM "myTable"
  WHERE jsonb_path_exists("myColumn", '$.foo.*.bar ? (@ like_regex "myValueX")');
  

补充：精确匹配的简化写法（当子结构固定时） #

如果foo下的每个子对象都只有bar字段，还可以用jsonb的包含操作符@>，但需要构造对应的JSON结构：

SELECT *
FROM "myTable"
WHERE "myColumn"->'foo' @> '{"": {"bar": "myValueX"}}'::jsonb;

• 说明：空键""在这里作为通配符，匹配任意键名的子对象，只要其中存在bar等于目标值的结构即可。

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