PostgreSQL如何解析JSON数组 拆分元素并过滤指定值

以下操作均适配存储在data字段下、结构为"languages": ["EN", "BN", "HI"]的JSON/JSONB数据。

注意：你当前使用的data->>'languages'语法会将整个数组转为纯文本格式，无法直接做元素级操作，做过滤、下标取值时建议先用->操作符取到JSON类型的数组节点再处理。

优先实现：返回排除"EN"后的语言数组 #

通过展开数组元素、过滤目标值、重新聚合的方式实现，自动适配任意长度的数组，无论"EN"在数组哪个位置都能被过滤，返回结果仍为标准JSON数组格式：

-- data字段为JSONB类型时使用
SELECT
  (
    SELECT jsonb_agg(lang_elem)
    FROM jsonb_array_elements(data->'languages') AS lang_elem
    WHERE lang_elem != '"EN"'::jsonb
  ) AS languages
FROM 你的表名;

-- data字段为JSON类型时使用，把jsonb相关关键字替换为json即可
SELECT
  (
    SELECT json_agg(lang_elem)
    FROM json_array_elements(data->'languages') AS lang_elem
    WHERE lang_elem != '"EN"'::json
  ) AS languages
FROM 你的表名;

针对你给出的示例数据，上述语句会返回["BN", "HI"]结构的结果；如果原数组中不存在"EN"，会返回完整的原数组内容。

数组元素拆分为独立字段 #

PostgreSQL中JSON数组的下标从0开始计数，直接按下标取值即可得到对应位置的独立字段，不存在的下标会返回null不会抛出异常：

SELECT
  data->'languages'->>0 AS language1, -- 取数组第1位，对应示例值"EN"
  data->'languages'->>1 AS language2, -- 取数组第2位，对应示例值"BN"
  data->'languages'->>2 AS language3  -- 取数组第3位，对应示例值"HI"
FROM 你的表名;

如果你的数组长度不固定、需要动态生成对应数量的独立字段，需要通过动态SQL拼接实现，固定取前N位的场景用上述写法即可满足需求。

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