How to create nested objects from MySQL query?-CodePudding

I have this dummy MySQL data:

id  date (y/m/d)  value
 1  2022-1-1      random value
 2  2022-2-5      random value
 3  2022-3-3      random value
 4  2022-4-6      random value
 5  2022-5-11     random value
 6  2022-6-7      random value
 7  2022-7-16     random value
 8  2022-8-4      random value
 9  2022-9-7      random value
10  2022-10-8     random value
11  2022-11-4     random value
12  2022-12-9     random value
13  2023-1-2      random value
14  2023-2-4      random value
15  2023-3-22     random value
16  2023-4-5      random value
17  2023-5-8      random value
18  2023-6-19     random value
19  2023-7-12     random value
20  2023-8-4      random value
21  2023-9-2      random value
22  2023-10-10    random value
23  2023-11-21    random value
24  2023-12-27    random value

I want to achieve something like this:

[{
    year:2022,
    value:[
        {
            month:1,
            value:[
                {
                    day:1,
                    value:'random value'
                }
            ]
        },
        {
            month:2,
            value:[
                {
                    day:5,
                    value:'random value'
                }
            ],
            ...
        }
    ]
},{
    year:2023,
    value:[
        ...
    ]
}]

Is there anything that can sort something like this? I am using JavaScript, Node.js and I get an array of data like the dummy data from MySQL query. I have to sort it like this for frontend React.

I need something that is fast and not require a lot of processing because this operation will be done many times on the server.

Do you have any ideas?

Thank you in advance. :)

CodePudding user response：

You could take an object for grouping and get a nested structure by taking parts of the date.

const
    data = [{ id: 1, date: '2022-01-01', value: 'random value' }, { id: 2, date: '2022-02-05', value: 'random value' }, { id: 3, date: '2022-03-03', value: 'random value' }, { id: 4, date: '2022-04-06', value: 'random value' }, { id: 5, date: '2022-05-11', value: 'random value' }, { id: 6, date: '2022-06-07', value: 'random value' }, { id: 7, date: '2022-07-16', value: 'random value' }, { id: 8, date: '2022-08-04', value: 'random value' }, { id: 9, date: '2022-09-07', value: 'random value' }, { id: 10, date: '2022-10-08', value: 'random value' }, { id: 11, date: '2022-11-04', value: 'random value' }, { id: 12, date: '2022-12-09', value: 'random value' }, { id: 13, date: '2023-01-02', value: 'random value' }, { id: 14, date: '2023-02-04', value: 'random value' }, { id: 15, date: '2023-03-22', value: 'random value' }, { id: 16, date: '2023-04-05', value: 'random value' }, { id: 17, date: '2023-05-08', value: 'random value' }, { id: 18, date: '2023-06-19', value: 'random value' }, { id: 19, date: '2023-07-12', value: 'random value' }, { id: 20, date: '2023-08-04', value: 'random value' }, { id: 21, date: '2023-09-02', value: 'random value' }, { id: 22, date: '2023-10-10', value: 'random value' }, { id: 23, date: '2023-11-21', value: 'random value' }, { id: 24, date: '2023-12-27', value: 'random value' }],
    keys = ['year', 'month', 'day'],
    result = data
        .reduce((r, { date, value }) => {
            date.split('-').reduce(function(level, key, i, a) {
                if (!level[key]) {
                  level[key] = { _: [] };
                  level._.push(i   1 === a.length
                      ? { [keys[i]]: key, value }
                      : { [keys[i]]: key, children: level[key]._ }
                  );
                }
                return level[key];
            }, r);
            return r;
        }, { _: [] })
        ._;

console.log(result);

.as-console-wrapper { max-height: 100% !important; top: 0; }