Reputation: 2672
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
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