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}]

Please edit your question to include example data. This way you can show exactly what result you expect for a given input. — MatBailie
– MatBailie, Commented Sep 13, 2021 at 9:57
There are too many unnecessary words. Leave the script with the table and data, show the table-formatted desired result and give detailed explanations. JSON (week = 1, visit1=1, visit_total=3 ; week = 2, visit1=1, visit_total=2, ... ) This is NOT json... — Akina
– Akina, Commented Sep 13, 2021 at 10:25
db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/16. My first fidle). I need ad GROUP BY week and put it in JSON_ARRAYAGG — Александр Грешников
– Александр Грешников, Commented Sep 13, 2021 at 10:54
Please don't use images for data or results, use formatted text. Especially if you're going to make corrections. Also, make your expected results actually match the sample data you've supplied. — MatBailie
– MatBailie, Commented Sep 13, 2021 at 11:26

MatBailie · Accepted Answer · 2021-09-13 11:19:50Z

5

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

answered Sep 13, 2021 at 11:19

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

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

2 Comments

Александр Грешников Over a year ago

Dearr @MatBailie. I have been struggling to find a solution for my query for several days. Please tell me, can I contact you personally?

MatBailie Over a year ago

@АлександрГрешников Just open another question. SO doesn't support conversations and direct messages.

Collectives™ on Stack Overflow

MYSQL GROUP BY with JSON_ARRAYAGG

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related