Estou lutando para tentar agregar resultados em uma tabela em um json aninhado.
esta é a tabela:
+----+------+--------+------+------+-------+-----------+--------------+
| id | area | userId | game | step | score | completed | validAnswers |
+----+------+--------+------+------+-------+-----------+--------------+
| 1 | 2 | 21 | 7 | 53 | 10 | 0 | 0 |
| 2 | 2 | 37 | 7 | 53 | 0 | 0 | 0 |
| 3 | 2 | 21 | 7 | 53 | 10 | 0 | 0 |
| 4 | 2 | 37 | 7 | 53 | 10 | 0 | 0 |
...
| 37 | 3 | 21 | 7 | 57 | 80 | 1 | 8 |
| 38 | 2 | 21 | 8 | 56 | 80 | 1 | 8 |
| 39 | 2 | 21 | 7 | 58 | 100 | 1 | 10 |
| 40 | 2 | 21 | 7 | 59 | 50 | 1 | 5 |
+----+------+--------+------+------+-------+-----------+--------------+
Eu gostaria de criar uma view mostrando algo assim:
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| userId | completedSteps |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 21 | [{"area": 2, "games": [{"id": 7, "steps": [58, 59]},{"id":8,"steps":[15,16,17]}]},{"area": 3, "games": [{"id": 1, "steps": [34, 18]},{"id":4,"steps":[11,12,14]}]}] |
| 18 | [{"area": 2, "games": [{"id": 7, "steps": [58, 59]},{"id":8,"steps":[15,16,17]}]},{"area": 3, "games": [{"id": 1, "steps": [34, 18]},{"id":4,"steps":[11,12,14]}]}] |
| 23 | [{"area": 2, "games": [{"id": 7, "steps": [58, 59]},{"id":8,"steps":[15,16,17]}]},{"area": 3, "games": [{"id": 1, "steps": [34, 18]},{"id":4,"steps":[11,12,14]}]}] |
| 11 | [{"area": 2, "games": [{"id": 7, "steps": [58, 59]},{"id":8,"steps":[15,16,17]}]},{"area": 3, "games": [{"id": 1, "steps": [34, 18]},{"id":4,"steps":[11,12,14]}]}] |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
agrupando todos os jogos, cada um com seus passos na área a que pertencem. Este é um dbfiddle que criei.
Eu tentei abordagens diferentes, como começar com
select userId,
json_arrayagg(
select distinct area
from scoreTable st2
where st1.userId =st2.userId and
st1.area=st2.area
group by area
) as completedSteps
from scoreTable st1
where completed = 1
group by userId ;
que surpreendentemente não agrupa por área, ou
select userId,
json_objectagg('areas',
(select distinct area
from scoreTable st2
where st1.userId =st2.userId
group by area)) as completedSteps
from scoreTable st1
where completed = 1
group by userId ;
ou muitas outras tentativas. Eu posso obter resultados de área discreta como:
select area,
json_object('games',json_object('id',game,'steps',JSON_ARRAYAGG(step))) as completedSteps
from scoreTable
where completed = 1
group by userId,area,game;
mas qualquer tentativa de agregar áreas em uma matriz de objetos falha. Alguém poderia me ajudar a undestad o que estou perdendo?
ATUALIZAÇÃO :
isso é algo mais próximo do que eu quero obter:
select userId, json_arrayagg(json_object('completed',completed)) as completed
from (
select distinct userId, json_arrayagg(json_object('area',area,'games',completed)) as completed
from (
select distinct userId,area, json_object('id',game,'steps',(json_arrayagg(step))) as completed
from scoreTable
where completed = 1 and userId = 21
group by area,game
) st1
group by userId, area
) st3
group by userId
mas ainda não agrupa os jogos aninhando-os em superobjetos de área. continua lutando..