Merge Two JSON array columns in Mysql 8

Question

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`

GMB · Accepted Answer · 2020-04-29 20:55:21Z

3

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

answered Apr 29, 2020 at 20:55

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Merge Two JSON array columns in Mysql 8

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related