Question 21738 in Mysql Question posted in
The official MySQL documentation can be found here.

How to create nested objects from MySQL query?

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. 🙂

Tags:

2

Answers


  1. 0

    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; }
    Login or Signup to reply.
  2. 0

    Those look like dates. Think about date arithmetic:

    '2020-01-01' + INTERVAL FLOOR(RAND() * 1000) DAY

    will generate one random date within 1000 days after the start of 2020. (Note there may be dups, but they could be dealt with afterward.)

    This technique will generate Jan 31, but not Feb 31. It looks like your approach leads to losing Jan 31 or creating Feb 31.

    Also, your data looks like you want one day per month. Is that a requirement. If so, please state. (My technique does not lend itself to such.)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search