How to create nested objects from MySQL query?

Question

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. :)

Yes, you can do that, you can .reduce(..) the array and .split(..) the date value to extract the year, month and day. — Titus
– Titus, Commented Dec 16, 2022 at 11:57

Nina Scholz · Accepted Answer · 2022-12-16 12:27:05Z

1

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

edited Dec 16, 2022 at 12:27

answered Dec 16, 2022 at 12:10

Nina Scholz

388k26 gold badges367 silver badges417 bronze badges

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

3 Comments

node_modules Over a year ago

Good solution, only code readability is very low. What do the variables q, k, i, a mean? Usually, for readability you would avoid using ambiguous variable names. This code is hard to debug and maintain if error(s) occur.

Nina Scholz Over a year ago

@node_modules, i replace q with level and k with key (parts of date). i is index of the loop and a is the original array which the loop is working with. btw, _ is the childrens array of the lower level.

Bassel Turky Over a year ago

It worked out perfectly, it is a little hard to read tho :D, I need to check the .reduce doc to understand how it works. but overall it gets the job done thank you so much.

Rick James · Accepted Answer · 2022-12-16 19:47:01Z

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

answered Dec 16, 2022 at 19:47

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

How to create nested objects from MySQL query?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related