如何从PostgreSQL的TEXT类型JSON列中仅保留指定属性？

一、直接使用内置JSON函数查询 #

针对你的需求，可以通过PostgreSQL的JSONB函数组合实现，直接执行以下查询语句：

SELECT jsonb_object_agg(key, processed_value) AS jsondata
FROM details,
     jsonb_each(jsondata::jsonb) AS j(key, value),
     LATERAL (
         SELECT CASE
             WHEN jsonb_typeof(value) = 'array' THEN
                 jsonb_agg(jsonb_build_object('name', elem->>'name', 'age', elem->>'age'))
             WHEN jsonb_typeof(value) = 'object' THEN
                 jsonb_build_object('name', value->>'name', 'age', value->>'age')
             ELSE value
         END AS processed_value
         FROM jsonb_array_elements(value) elem
         WHERE jsonb_typeof(value) = 'array'
         UNION ALL
         SELECT jsonb_build_object('name', value->>'name', 'age', value->>'age') AS processed_value
         WHERE jsonb_typeof(value) = 'object'
     ) AS pv
WHERE details_id = 5;

逻辑说明：

• 将TEXT类型的jsondata转为JSONB格式，方便后续操作
• 用jsonb_each拆分顶层键值对（如employee、student、Admin）
• 通过LATERAL子句分别处理不同类型的值：
  • 数组类型：遍历每个元素，仅保留name和age字段后重新聚合为数组
  • 单个对象类型：直接提取指定字段
• 最后用jsonb_object_agg将处理后的键值对重新组装为完整JSONB对象

二、创建自定义函数简化调用 #

如果你希望用类似retain_only(jsondata, ['name','age'])的简洁方式调用，可以创建自定义函数：

CREATE OR REPLACE FUNCTION retain_only(json_str TEXT, fields TEXT[])
RETURNS JSONB AS $$
BEGIN
    RETURN (
        SELECT jsonb_object_agg(key, processed_value)
        FROM jsonb_each(json_str::jsonb) AS j(key, value),
             LATERAL (
                 SELECT CASE
                     WHEN jsonb_typeof(value) = 'array' THEN
                         jsonb_agg(
                             jsonb_object_agg(f, elem->>f)
                             FROM unnest(fields) f
                         )
                     WHEN jsonb_typeof(value) = 'object' THEN
                         jsonb_object_agg(f, value->>f)
                         FROM unnest(fields) f
                     ELSE value
                 END AS processed_value
                 FROM jsonb_array_elements(value) elem
                 WHERE jsonb_typeof(value) = 'array'
                 UNION ALL
                 SELECT jsonb_object_agg(f, value->>f) AS processed_value
                 FROM unnest(fields) f
                 WHERE jsonb_typeof(value) = 'object'
             ) AS pv
    );
END;
$$ LANGUAGE plpgsql IMMUTABLE;

创建完成后，即可用简洁语句查询：

SELECT retain_only(jsondata, ARRAY['name','age']) AS jsondata
FROM details
WHERE details_id = 5;

这个函数支持传入任意字段数组，扩展性更强，执行后会输出你期望的结果：

{"employee":[{"name":"Sunil","age":"34"},{"name":"Abhi","age":"36"},{"name":"Arnav","age":"36"}],"student":[{"name":"Anil","age":"34"}],"Admin":{"name":"Admin","age":"36"}}

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