GroupBY with alaSQL in an array of Objects

Question

I need help to achieve this transformation of an Array of Objects. I am working with alaSQL. I need to do a GroupBY by player. I tried alasql('SELECT score FROM ? WHERE score GROUP BY player',[arrayGamePlayerObjects]) But I got this result

{
  "player": {
    "id": 5,
    "firstName": "Player",
    "lastName": "05",
    "email": "[email protected]"
  }
}

Original Array of Objects:

[
    {
    "player": {"id": 5,"firstName": "Player","lastName": "05","email": "[email protected]"},
    "score": {"id": 5,"score": 0.5,"finishDate": "2022-05-05T16:12:22.038+00:00",
            "game":   {"id": 3,"gameName": "Game 03","gameDate": "2022-05-05T18:12:21.049+00:00"},
            "player": {"id": 5,"firstName": "Player","lastName": "05","email": "[email protected]"}
            }
    },
    {
    "player": {"id": 6,"firstName": "Player","lastName": "06","email": "[email protected]"},
    "score": {"id": 6,"score": 0.5,"finishDate": "2022-05-05T16:12:22.038+00:00",
            "game": {"id": 3,"gameName": "Game 03","gameDate": "2022-05-05T18:12:21.049+00:00"},
            "player": {"id": 6,"firstName": "Player","lastName": "06","email": "[email protected]"}
            }
    },
    {
    "player": {"id": 5,"firstName": "Player","lastName": "05","email": "[email protected]"},
    "score": {"id": 7,"score": 0.5,"finishDate": "2022-05-05T16:12:22.042+00:00",
            "game": {"id": 6,"gameName": "Game 06","gameDate": "2022-05-05T20:12:21.049+00:00"},
            "player": {"id": 5,"firstName": "Player","lastName": "05","email": "[email protected]"}
            }
    },
    {
    "player": {"id": 6,"firstName": "Player","lastName": "06","email": "[email protected]"},
    "score": {"id": 8,"score": 0.5,"finishDate": "2022-05-05T16:12:22.042+00:00",
            "game": {"id": 6,"gameName": "Game 06","gameDate": "2022-05-05T20:12:21.049+00:00"},
            "player": {"id": 6,"firstName": "Player","lastName": "06","email": "[email protected]"}
            }
    }

The end result I need is :

[
    {
    "player": {"id": 5,"firstName": "Player","lastName": "05","email": "[email protected]"},
            "score01": {"id": 5,"score": 0.5,"finishDate": "2022-05-05T16:12:22.038+00:00",
                    "game":   {"id": 3,"gameName": "Game 03","gameDate": "2022-05-05T18:12:21.049+00:00"},
                    "player": {"id": 5,"firstName": "Player","lastName": "05","email": "[email protected]"}
            },
            "score02": {"id": 7,"score": 0.5,"finishDate": "2022-05-05T16:12:22.042+00:00",
                    "game": {"id": 6,"gameName": "Game 06","gameDate": "2022-05-05T20:12:21.049+00:00"},
                    "player": {"id": 5,"firstName": "Player","lastName": "05","email": "[email protected]"}
            }
    },
    {
    "player": {"id": 6,"firstName": "Player","lastName": "06","email": "[email protected]"},
            "score01": {"id": 6,"score": 0.5,"finishDate": "2022-05-05T16:12:22.038+00:00",
                    "game": {"id": 3,"gameName": "Game 03","gameDate": "2022-05-05T18:12:21.049+00:00"},
                    "player": {"id": 6,"firstName": "Player","lastName": "06","email": "[email protected]"}
                    },
            "score02": {"id": 8,"score": 0.5,"finishDate": "2022-05-05T16:12:22.042+00:00",
                    "game": {"id": 6,"gameName": "Game 06","gameDate": "2022-05-05T20:12:21.049+00:00"},
                    "player": {"id": 6,"firstName": "Player","lastName": "06","email": "[email protected]"}
                    }
    }
]

Any guide or ideas on how to get this result ?

Answer 1

I think this can help!

Implementation

const groupBy = key => array =>
  array.reduce((objectsByKeyValue, obj) => {
    const value = obj[key];
    objectsByKeyValue[value] = (objectsByKeyValue[value] || []).concat(obj);
    return objectsByKeyValue;
  }, {});

Usage

const cars = [
  { brand: 'Audi', color: 'black' },
  { brand: 'Audi', color: 'white' },
  { brand: 'Ferarri', color: 'red' },
  { brand: 'Ford', color: 'white' },
  { brand: 'Peugot', color: 'white' }
];

const groupByBrand = groupBy('brand');
const groupByColor = groupBy('color');

console.log(
  JSON.stringify({
    carsByBrand: groupByBrand(cars),
    carsByColor: groupByColor(cars)
  }, null, 2)
);

Output

{
  "carsByBrand": {
    "Audi": [
      {
        "brand": "Audi",
        "color": "black"
      },
      {
        "brand": "Audi",
        "color": "white"
      }
    ],
    "Ferarri": [
      {
        "brand": "Ferarri",
        "color": "red"
      }
    ],
    "Ford": [
      {
        "brand": "Ford",
        "color": "white"
      }
    ],
    "Peugot": [
      {
        "brand": "Peugot",
        "color": "white"
      }
    ]
  },
  "carsByColor": {
    "black": [
      {
        "brand": "Audi",
        "color": "black"
      }
    ],
    "white": [
      {
        "brand": "Audi",
        "color": "white"
      },
      {
        "brand": "Ford",
        "color": "white"
      },
      {
        "brand": "Peugot",
        "color": "white"
      }
    ],
    "red": [
      {
        "brand": "Ferarri",
        "color": "red"
      }
    ]
  }
}

Ref: from

Answer 2

I used alaSQL with this query and works Ok .

alasql('SELECT player->id , ARRAY(_) AS gamePlayer_per_player FROM ? WHERE score GROUP BY player->id',[arrayGamePlayerObjects]);