如何使用通配符语法从JSONB结构中删除多个元素？

PostgreSQL的#-运算符仅支持明确的路径数组，不支持通配符，因此无法直接用它批量删除所有嵌套的languages键。以下是两种更简便的实现方式：

方法1：使用jsonb_path_modify（PostgreSQL 12+推荐） #

PostgreSQL 12及以上版本提供的jsonb_path_modify支持JSON路径通配符，结合jsonb_strip_nulls可以一步完成批量删除：

-- 针对单个JSONB值测试
SELECT jsonb_strip_nulls(
  jsonb_path_modify(
    '{
      "collections": {
        "drafts": [{"availabilities": [], "languageCodes": [], "languages": []}],
        "items": [{"languageCodes": [], "languages": []}, {"languageCodes": [], "languages": []}]
      }
    }'::jsonb,
    '$.**.languages',
    'null'
  )
) AS modified_jsonb;

-- 针对表中JSONB列操作
SELECT jsonb_strip_nulls(
  jsonb_path_modify(your_jsonb_column, '$.**.languages', 'null')
) AS modified_jsonb
FROM your_table;

原理说明： #

1. jsonb_path_modify通过通配符路径$.**.languages匹配所有层级下的languages键，将其值设为null；
2. jsonb_strip_nulls自动移除所有值为null的键，最终得到预期的清理后结果。

方法2：递归CTE处理（兼容PostgreSQL 11及以下） #

如果使用的是更早版本的PostgreSQL，可以通过递归CTE遍历JSONB的所有层级，过滤掉languages键：

-- 针对表中JSONB列操作
WITH RECURSIVE clean_json(source, data) AS (
  SELECT 
    your_jsonb_column AS source,
    CASE
      WHEN jsonb_typeof(your_jsonb_column) = 'object' THEN jsonb_build_object()
      WHEN jsonb_typeof(your_jsonb_column) = 'array' THEN jsonb_build_array()
      ELSE your_jsonb_column
    END AS data
  FROM your_table
  UNION ALL
  SELECT
    c.source,
    CASE
      -- 处理对象：跳过languages键，递归处理其他值
      WHEN jsonb_typeof(c.source) = 'object' THEN
        c.data || jsonb_build_object(o.key, COALESCE(ci.data, o.value))
      -- 处理数组：递归处理每个元素并追加到数组
      WHEN jsonb_typeof(c.source) = 'array' THEN
        c.data || COALESCE(ci.data, a.value)
      ELSE
        c.data
    END AS data
  FROM clean_json c
  -- 展开对象
  LEFT JOIN jsonb_each(c.source) o ON jsonb_typeof(c.source) = 'object' AND o.key != 'languages'
  -- 展开数组
  LEFT JOIN jsonb_array_elements(c.source) a ON jsonb_typeof(c.source) = 'array'
  -- 递归处理嵌套的对象/数组
  LEFT JOIN clean_json ci ON 
    (jsonb_typeof(o.value) IN ('object', 'array') AND ci.source = o.value)
    OR (jsonb_typeof(a.value) IN ('object', 'array') AND ci.source = a.value)
)
-- 取最终处理完成的结果
SELECT DISTINCT data AS modified_jsonb
FROM clean_json
WHERE jsonb_typeof(data) = jsonb_typeof(source);

原理说明： #

递归CTE逐层遍历JSONB的对象和数组：

• 遇到对象时，跳过languages键，对其他键对应的值递归清理；
• 遇到数组时，对每个数组元素递归清理，最终重组为完整的清理后JSONB。

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