如何在PostgreSQL中仅提取JSON字符串的指定属性

针对你需要仅保留JSON中指定属性的需求，这里提供两种实现方式：

直接查询实现 #

如果只是单次查询，不需要复用逻辑，可以直接用PostgreSQL的JSONB函数组合实现：

WITH test_data AS (
  -- 模拟输入的JSON数据
  SELECT '{"employee":[{"name":"Raunak","Age":30,"sec":"A"},{"name":"Manoj","Age":35,"sec":"N"},{"name":"Naveen","Age":38,"sec":"D"}]}'::jsonb AS input_json
)
SELECT jsonb_build_object(
  'employee', -- 保留顶层的键名
  jsonb_agg(
    jsonb_build_object('name', elem->>'name') -- 仅保留每个对象的name属性
  )
) AS result_json
FROM test_data,
     jsonb_array_elements(input_json->'employee') AS elem; -- 拆分employee数组为单个元素

执行后会得到预期结果：

{"employee":[{"name":"Raunak"},{"name":"Manoj"},{"name":"Naveen"}]}

可复用函数实现 #

如果需要多次使用类似逻辑，可以封装成自定义函数skip_rest_all_properties，方便调用：

CREATE OR REPLACE FUNCTION skip_rest_all_properties(json_data jsonb, keep_keys text[])
RETURNS jsonb AS $$
BEGIN
  -- 提取顶层唯一键（适配示例中只有一个顶层键的场景，若需处理多顶层键可调整逻辑）
  RETURN jsonb_build_object(
    (SELECT jsonb_object_keys(json_data) LIMIT 1),
    jsonb_agg(
      jsonb_object_agg(key, value)
      FROM jsonb_array_elements(json_data->(SELECT jsonb_object_keys(json_data) LIMIT 1)) AS elem,
           jsonb_each(elem) AS kv(key, value)
      WHERE kv.key = ANY(keep_keys) -- 只保留指定的属性
    )
  );
END;
$$ LANGUAGE plpgsql;

调用函数时只需传入JSON数据和要保留的属性列表：

SELECT skip_rest_all_properties(
  '{"employee":[{"name":"Raunak","Age":30,"sec":"A"},{"name":"Manoj","Age":35,"sec":"N"},{"name":"Naveen","Age":38,"sec":"D"}]}'::jsonb,
  ARRAY['name']
) AS result;

注意事项 #

• 如果你的JSON字段类型是json而非jsonb，只需在查询或函数中用::jsonb转换类型即可。
• 若需要处理多个顶层键的场景，可以修改函数逻辑，遍历所有顶层键进行处理。

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