PostgreSQL表中基于键对字符串化KV对去重的实现方法

问题背景 #

现有如下PostgreSQL表：

CREATE TABLE data (
    id serial PRIMARY KEY,
    values varchar
);

INSERT INTO data (values) VALUES
    ('k1=v1&k2=v2&k3=v3&k1=v4'),
    ('k2=v1&k2=v2&k3=v1&k2=v4&k3=v4')
;

values列存储由&分隔的键值对字符串，该字段可为NULL或包含任意数量的KV对。需要生成一个新列values_d，存放values的去重版本：按键去重，且保留最左侧（首个）出现的键值对。预期输出如下：

最初尝试将列转换为jsonb对象利用其去重特性，但jsonb会保留重复键的最右侧值，后续尝试反转数组时遇到索引问题无法解决。

我的尝试代码 #

drop view if exists alan_1 cascade;
create view alan_1 as (
 SELECT
        values,
         regexp_split_to_array(values, '[=&]') AS kv_parts,
        generate_series(1, array_length(regexp_split_to_array(values, '[=&]'), 1), 2) AS idx
    FROM data
                      );

select * from alan_1;

drop view if exists alan_2 cascade;
create view alan_2 as
(
    SELECT
    values
    ,idx
        ,ARRAY(
        SELECT
            CASE
                WHEN i % 2 = 0 THEN kv_parts[i - 1]
                ELSE kv_parts[i + 1]
            END
        FROM generate_series(1, array_length(kv_parts, 1)) AS i
    ) AS kv_parts_r
    , kv_parts
    from alan_1
);

select * from alan_2;

drop view if exists alan_3 cascade;
create view alan_3 as
(
SELECT *,
       ARRAY(
               SELECT kv_parts_r[i]
               FROM generate_series(array_length(kv_parts_r, 1), 1, -1) AS i
           ) AS reversed_array
FROM alan_2
    );

select * from alan_3;

drop view if exists alan_4 cascade;
create view alan_4 as (
SELECT
    values,
    json_object_agg(
        reversed_array[idx], reversed_array[idx + 1]
    ) AS distinct_key_values
FROM alan_3
GROUP BY values
);

select * from alan_4;

可行解决方案 #

核心思路 #

1. 将键值对字符串按&分割为KV对数组
2. 反转数组，让原字符串最左侧的KV对处于数组末尾
3. 将反转后的数组转换为jsonb对象：利用jsonb保留重复键最右侧值的特性，此时保留的就是原字符串最左侧的KV对
4. 将jsonb对象转换回键值对字符串

实现代码 #

-- 步骤1：分割字符串为KV对数组
drop view if exists v_1 cascade;
create view v_1 as (
SELECT
    id,
    values,
     string_to_array(values, '&') as a
from data);

-- 步骤2：反转KV对数组
drop view if exists v_2 cascade;
create view v_2 as (
SELECT
    id,
    values,
    a,
    ARRAY(SELECT a[i] FROM generate_subscripts(a, 1) AS i ORDER BY i DESC) AS reverse
FROM v_1);

-- 步骤3：将反转后的数组转回字符串
drop view if exists v_3 cascade;
create view v_3 as
(
select id, values, reverse, array_to_string(reverse, '&') AS reversed_values
from v_2
    );

-- 步骤4：提取反转字符串的键值对并转为jsonb（完成去重）
drop view if exists v_4 cascade;
create view v_4 as(
    SELECT
    id,
    values,
    jsonb_object_agg(kv_key, kv_value) AS json_data
FROM (
    SELECT
        id,
        values,
        reversed_values,
        kv_pair,
        split_part(kv_pair, '=', 1) AS kv_key,
        split_part(kv_pair, '=', 2) AS kv_value
    FROM v_3
    CROSS JOIN LATERAL regexp_split_to_table(reversed_values, '&') AS kv_pair
) AS extracted_kv_pairs
GROUP BY id, values);

-- 步骤5：将jsonb转回键值对字符串，得到最终结果
SELECT
    id,
    values,
    string_agg(concat_ws('=', key, value), '&') AS values_d
FROM v_4,
LATERAL jsonb_each_text(json_data)
GROUP BY id, values
ORDER BY id;

注意事项 #

• 若values字段为NULL，需额外处理避免报错，可在分割前添加COALESCE(values, '')进行判断
• 上述代码保留了原表的id字段，最终输出与示例格式完全匹配

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