How to merge json columns with there are multiple rows-CodePudding

I have the following table called account_refunds:

id |  referrals(type json) |  accountID 
___________________________

123 |  [1,2]               |   001  
124 |  [3]                 |   001 
125 |  [4]                 |   001 
126 |  [5,6,7]             |   001
127 |  [4]                 |   001

In this table I got some accounts that can have multiple refunds based on their referrals,(referred users id's are stored in referrals column) if they refer a service to someone, they'll be refunded. It's a pretty simple logic, however, the main problem I have is that I need to filter out if that account has already been refunded for a referred user. So for example, If there's a record of that account been refunded for a user by an ID of 4, I don't want to create another refund. So my goal is to select all the records by the same accountID, merge the referrals columns together as an array and filter through it. So the end result will look like this : [1,2,3,4,5,6,7] and after I get this value I'll simply look for that user ID in that array.

select af.id, af.referrals, af.accountID
 from ns_accounts_refunds af
inner join ns_accounts a on a.id = 001
 join ns_accounts_refunds af 
where  af.accountID = a.id

Expected outcome:

id |  referrals(type json) |  accountID 
___________________________

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

The last row that would be sharing the same userID in referrals column array would not be selected

Thanks in advance!

CodePudding user response：

Here is how to do it with JSON_TABLE to split arrays into rows and then JSON_ARRAYAGG to get the arrays back after filtering out duplicates with the aggregate function MIN() :

WITH CTE AS (
  SELECT accountID, refID, MIN(id) AS id
  FROM account_refunds
  CROSS JOIN JSON_TABLE(
         referrals,
         "$[*]"
         COLUMNS(
           refID INT PATH "$"
         )
       ) t
  GROUP BY accountID, refID
)
SELECT id, JSON_ARRAYAGG(refID) AS referrals, accountID
FROM CTE
GROUP BY accountID, id;

Result :

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

Demo here

CodePudding user response：

SELECT DISTINCT a.id, jsonb_array_elements_text(merged_referrals) AS referral_id, a.accountID
FROM ns_accounts_refunds a
JOIN (
    SELECT accountID, jsonb_agg(jsonb_array_elements_text(referrals)) AS merged_referrals
    FROM ns_accounts_refunds
    GROUP BY accountID
) AS merged ON a.accountID = merged.accountID
WHERE a.accountID = 001;

CodePudding user response：

You can use JSON_TABLE for your required query.

WITH CTE AS (

SELECT accountID, refID, MIN(id) AS id FROM account_refunds

CROSS JOIN JSON_TABLE( referrals, "$[*]" COLUMNS( refID INT PATH "$" ) ) t GROUP BY accountID, refID)

SELECT id, JSON_ARRAYAGG(refID) AS referrals, accountID FROM CTE GROUP BY accountID, id;