CODEWITHSUNDEEP
mysqlsqlarraysjsonaggregate-functions
Amit Badheka
Amit Badheka

Reputation: 2672

Merge Two JSON array columns in Mysql 8

I am using Mysql (version > 8).

I have a table where I am storing unique customer ids for the day in an JSON field as an array.

I need to query for all unique customers in last 30 days. I am unable to find an equivalent mysql query for the same.

I am using JSON_ARRAYAGG to first merge all the arrays and then I am using ruby to flatten the array and find uniq values.

Example Query:

SELECT 
    JSON_ARRAYAGG(customers) as customers, 
    name 
FROM `source_type_daily_metrics` 
WHERE 
    `source_type_daily_metrics`.`merchant_id` = 29 
    AND (date >= curdate() - interval 30 day) 
GROUP BY `source_type_daily_metrics`.`name`

Upvotes: 2

Views: 1528

Answers (1)

GMB
GMB

Reputation: 222722

You can use json_table() to unnest the arrays as rows, and then json_arrayagg() to aggregate back:

select s.name, json_arrayagg(t.cust) customers
from source_type_daily_metrics s
cross join json_table(s.customers, '$[*]' columns (cust int path '$')) t
where s.merchant_id = 29 and date >= current_date - interval 30 day
group by s.name

If there are duplicate customer ids across arrays, and you want distinct values in the resultset, then you need an additional level of aggregation (since, unfortunately, json_arrayagg() does not support distinct):

select name, json_arrayagg(cust) customers
from (
    select distinct s.name, t.cust
    from source_type_daily_metrics s
    cross join json_table(s.customers, '$[*]' columns (cust int path '$')) t
    where s.merchant_id = 29 and date >= current_date - interval 30 day
) t
group by s.name

Upvotes: 3

Related Questions