如何合并JSON列并过滤重复推荐用户的账户退款记录？

我们需要从account_refunds表中，针对同一accountID，过滤掉那些包含已经在之前记录中出现过的推荐用户ID的行，只保留所有推荐用户ID都是首次出现的记录（比如排除id=127的行，因为推荐ID4已经在id=125的记录里出现过）。

你当前的SQL只是简单关联了ns_accounts和重复join了ns_accounts_refunds，完全没有处理referralsJSON数组的去重和过滤逻辑，所以无法达到需求。

实现步骤与SQL代码 #

注：如果你的实际表名是ns_accounts_refunds，请将代码中的account_refunds替换为该表名；若只需限定accountID='001'，可在第一个CTE中添加WHERE ar.accountID = '001'缩小范围。

WITH split_referrals AS (
    -- 把每条记录的referrals数组拆分成单个推荐ID，关联原记录信息
    SELECT
        ar.id AS record_id,
        ar.referrals,
        ar.accountID,
        json_array_elements_text(ar.referrals)::int AS referral_id
    FROM account_refunds ar
),
first_occurrence AS (
    -- 标记每个推荐ID在对应accountID下首次出现的记录ID
    SELECT
        referral_id,
        accountID,
        MIN(record_id) AS first_record_id
    FROM split_referrals
    GROUP BY referral_id, accountID
),
record_check AS (
    -- 检查每条原记录的所有推荐ID是否都首次出现在当前记录
    SELECT
        sr.record_id,
        BOOL_AND(sr.record_id = fo.first_record_id) AS is_valid
    FROM split_referrals sr
    JOIN first_occurrence fo 
        ON sr.referral_id = fo.referral_id 
        AND sr.accountID = fo.accountID
    GROUP BY sr.record_id
)
-- 筛选出所有推荐ID均为首次出现的记录
SELECT
    ar.id,
    ar.referrals,
    ar.accountID
FROM account_refunds ar
JOIN record_check rc ON ar.id = rc.record_id
WHERE rc.is_valid = true
ORDER BY ar.id;

代码解释 #

1. split_referrals CTE：用json_array_elements_text将JSON数组拆分为单个推荐ID，把多值数组转化为单条记录，方便后续追踪每个ID的出现情况。
2. first_occurrence CTE：对每个accountID下的每个推荐ID，通过MIN(record_id)找到它首次出现的记录ID（取最早的记录）。
3. record_check CTE：用BOOL_AND验证当前记录的所有推荐ID，是否它们的首次出现记录都是当前这条——只要有一个ID不是首次出现在当前记录，这条记录就会被标记为无效。
4. 最后关联原表和验证结果，只保留有效记录并按ID排序。

预期输出 #

执行后会得到你需要的结果：

id  | referrals | accountID
-----|-----------|-----------
123  | [1,2]     | 001
124  | [3]       | 001
125  | [4]       | 001
126  | [5,6,7]   | 001

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