MYSQL GROUP BY with JSON_ARRAYAGG

Question

We have a QUERY

 SELECT
  t.user,
  COUNT(CASE WHEN t.visit = 1 THEN 1 END)   AS visit_1,
  COUNT(*)   AS visit_total
FROM
  t
GROUP BY
  t.user

IT return.

user | count(visit_1) | COUNT(visit_total)

I Need one more field in GROUP BY

GROUP BY week

WE HAVE a TABLE

CREATE TABLE table1(
user   int(11) ,
visit  int(3),  
week   int(1),
);

INSERT INTO  table1 VALUES (1,1,1),(1,2,1),(1,3,1),(2,1,1),(2,2,1),(2,3,1),(2,1,2),(2,1,3);

WE NEED RESULT

user	visit_1	visit_total	weekly_history
1	1	3	[{"week": 1, "visit_1": 1, "visit_total": 3}]
2	3	5	[{"week": 1, "visit_1": 1, "visit_total": 3}, {"week": 2, "visit_1": 1, "visit_total": 1}, {"week": 3, "visit_1": 1, "visit_total": 1}]

Answer 1

Aggregate twice. Once to get the data by user and week, then again to aggregate that to one row per user; both the summary columns and the json array.

WITH
  user_week AS
(
  SELECT
    t.user,
    t.week,
    COUNT(CASE WHEN t.visit = 1 THEN 1 END)   AS visit_1,
    COUNT(*)   AS visit_total
  FROM
    table1   AS t
  GROUP BY
    t.user,
    t.week
)
SELECT
  uw.user,
  SUM(uw.visit_1)        AS visit_1,
  SUM(uw.visit_total)    AS visit_total,
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'week',        uw.week,
      'visit_1',     uw.visit_1,
      'visit_total', uw.visit_total
    )
  )
    AS weekly_history
FROM
  user_week  AS uw
GROUP BY
  uw.user

Demo : https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3bd0a448ac36aa2d5dac8a6630a1b593