PostgreSQL中如何通过键数组从JSON字段批量提取匹配键值对

实现方案 #

PostgreSQL没有原生支持json -> text[]格式的批量提取运算符，但可以通过内置函数组合实现，也可以自定义运算符匹配你期望的调用格式。

方案1：直接用内置函数组合实现（无需额外定义） #

如果是JSONB类型字段，核心逻辑是先把JSON拆成键值对，过滤匹配指定数组的键后再聚合为新JSON：

-- 测试用例
WITH test_data AS (
    SELECT '{"firstname": "John", "secondname": "Smith", "age": 55}'::jsonb AS field
)
-- 核心查询，替换WHERE后的数组即可动态修改提取的键
SELECT jsonb_object_agg(key, value) AS filtered_json
FROM test_data,
     jsonb_each(field)
WHERE key = ANY('{"firstname", "secondname"}'::text[]);

如果是普通JSON类型，将所有jsonb前缀的函数换成json即可（json_each、json_object_agg）。

方案2：自定义运算符实现json -> text[]调用格式 #

如果需要频繁使用该能力，可以封装为自定义函数和运算符，调用方式完全符合需求：

第一步：定义提取函数

CREATE OR REPLACE FUNCTION jsonb_extract_keys(jsonb, text[])
RETURNS jsonb AS $$
-- COALESCE处理无匹配键时返回空对象而不是null
SELECT COALESCE(jsonb_object_agg(key, value), '{}'::jsonb)
FROM jsonb_each($1)
WHERE key = ANY($2);
$$ LANGUAGE sql STABLE;

第二步：绑定自定义运算符

CREATE OPERATOR -> (
    LEFTARG = jsonb,
    RIGHTARG = text[],
    PROCEDURE = jsonb_extract_keys
);

调用示例

-- 返回 {"firstname": "John", "secondname": "Smith"}
SELECT field -> '{"firstname", "secondname"}'::text[] FROM test_data;

-- 返回 {"firstname": "John"}
SELECT field -> '{"firstname", "job"}'::text[] FROM test_data;

-- 返回 {}
SELECT field -> '{"job"}'::text[] FROM test_data;

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