PostgreSQL中jsonb列已知键与任意位置字符串匹配查询问题

你用到的OPENJSON函数和CROSS APPLY语法是SQL Server的专属特性，PostgreSQL没有对应实现，因此会触发语法和函数不存在报错。

你给出的json结构中，Material Type是嵌套在regions数组下每个元素的region_attributes对象中的键，针对这类已知路径的查询，有两种常用实现方式：

• 高性能索引适配写法（推荐，可走GIN索引）

-- 示例：匹配regions下存在region_attributes.Material Type = 'wood'的行
SELECT * 
FROM table_name
WHERE col_name->'regions' @> '[{"region_attributes":{"Material Type":"wood"}}]'::jsonb;

可以提前创建GIN索引提升查询效率：

CREATE INDEX idx_table_col_gin ON table_name USING GIN(col_name);

• 数组拆分流写法（适合需要对数组元素做复杂逻辑判断的场景）

SELECT DISTINCT t.*
FROM table_name t,
     jsonb_array_elements(t.col_name->'regions') region_item
WHERE region_item->'region_attributes'->>'Material Type' = 'wood';

说明：->操作符返回jsonb类型的节点值，->>操作符直接返回文本类型的节点值，加DISTINCT是避免单条数据多个数组元素匹配时返回重复行。

PostgreSQL 12及以上版本支持JSONPath语法，可以直接递归遍历所有json节点匹配：

• 精准匹配任意节点值等于目标字符串：

-- 匹配json任意位置值为'wood'的行
SELECT * 
FROM table_name
WHERE jsonb_path_exists(col_name, '$.** ? (@ == "wood")');

• 模糊匹配任意节点值包含目标字符串：

-- 匹配json任意位置值包含'wood'的行，flag "i"代表忽略大小写，不需要可删除
SELECT * 
FROM table_name
WHERE jsonb_path_exists(col_name, '$.** ? (@ like_regex "wood" flag "i")');

如果使用的是PostgreSQL 12以下版本，可以用递归CTE展开所有json节点后匹配：

WITH RECURSIVE parse_all_json_nodes(key, val, origin_data) AS (
    SELECT NULL, col_name, col_name FROM table_name
    UNION ALL
    -- 展开对象类型节点
    SELECT k, v, p.origin_data
    FROM parse_all_json_nodes p,
         jsonb_each(CASE WHEN jsonb_typeof(val) = 'object' THEN val ELSE '{}'::jsonb END) e(k,v)
    UNION ALL
    -- 展开数组类型节点
    SELECT NULL, v, p.origin_data
    FROM parse_all_json_nodes p,
         jsonb_array_elements(CASE WHEN jsonb_typeof(val) = 'array' THEN val ELSE '[]'::jsonb END) a(v)
)
SELECT DISTINCT t.*
FROM table_name t
JOIN parse_all_json_nodes p ON t.col_name = p.origin_data
WHERE jsonb_typeof(p.val) IN ('string', 'number', 'boolean')
  -- 精准匹配用下面的条件
  AND p.val::text = '"wood"'
  -- 模糊匹配替换为下面的条件
  -- AND p.val::text LIKE '%wood%'
;

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