Express + MySql how to get data and create nested array

Question

I want to get an nested array from MySql with a query.

MySQL

+------+---------+--------+---------+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| id   | title   | kindof | website | categories               | images                                                                                                                                    |
+------+---------+--------+---------+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
|  158 | prova 7 | design | wwww    | Brass,Jewerly            | image000.jpg,image001.jpg                                                                                          |
|  159 | Prova 8 | food   | www     | Italian Food,Korean Food | image000.jpg,image001.jpg, image002.jpg |
+------+---------+--------+---------+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+

From my API I get this:

[
        {
            "id": 158,
            "title": "prova 7",
            "kindof": "design",
            "website": "wwww",
            "categories": "Brass,Jewerly",
            "img_order": "0,1",
            "images": "image000.jpg,image001.jpg"
        },
        {
            "id": 159,
            "title": "Prova 8",
            "kindof": "food",
            "website": "www",
            "categories": "Italian Food,Korean Food",
            "img_order": "0,1,2",
            "images": "image000.jpg,image001.jpg,image002.jpg"
        }
    ]

I need to get something like this:

[
            {
                "id": 158,
                "title": "prova 7",
                "kindof": "design",
                "website": "wwww",
                "categories": "Brass,Jewerly",
                "images": [
                           { position: "0", image: "image000.jpg" },
                           { position: "1", image: "image001.jpg" }
                           ]
            },
            {
                "id": 159,
                "title": "Prova 8",
                "kindof": "food",
                "website": "www",
                "categories": "Italian Food,Korean Food",
                "images": [
                           { position: "0", image: "image000.jpg" },
                           { position: "1", image: "image001.jpg" },
                           { position: "2", image: "image002.jpg" }
                           ]
            }
        ]

the only idea I got is to create it maping what I do need once I got it.

Is there maybe a solution doing this with a query?

Thanks!

I think even if you could get this done in the query you may end up having to run a loop over the data anyway to unserialise it. At this point why not just do a loop over it and run a split on the images property? — Matthew Usurp
– Matthew Usurp, Commented Aug 20, 2020 at 9:49
It might be a bit out of place for comment. But as I understand MySQL doesn't have the ability to return a structured object, so either way you will need to do a loop over each result and execute some logic. — Matthew Usurp
– Matthew Usurp, Commented Aug 20, 2020 at 10:09
ok. thanks is what I'm trying to do now. I just (wrongly) thought that maybe there was a syntax that I didn't know. The only thing that can be usefull is to add a CONCAT to add parenthesis if you need it — Marco Disco
– Marco Disco, Commented Aug 20, 2020 at 10:26
If you want to do it in DB look here for solution: stackoverflow.com/questions/56958056/… — Slava Rozhnev
– Slava Rozhnev, Commented Aug 20, 2020 at 10:33

Slava Rozhnev · Accepted Answer · 2020-08-20 12:26:40Z

1

You can solve this on DB side or on Node.js. For solve it on DB use next query:

SELECT 
    id,
    title,
    kindof,
    website,
    categories,
    CAST(
        CONCAT('[{"image": "', REPLACE(images, ',', '"}, {"image": "' ), '"}]')  
        AS JSON
    ) AS images
FROM your_table  ;

Below you can find JavaScript solution:

let api_result = [
        {
            "id": 158,
            "title": "prova 7",
            "kindof": "design",
            "website": "wwww",
            "categories": "Brass,Jewerly",
            "img_order": "0,1",
            "images": "image000.jpg,image001.jpg"
        },
        {
            "id": 159,
            "title": "Prova 8",
            "kindof": "food",
            "website": "www",
            "categories": "Italian Food,Korean Food",
            "img_order": "0,1,2",
            "images": "image000.jpg,image001.jpg,image002.jpg"
        }
    ];

api_result.map(
    (res)=>res.images=res.images.split(',')
      .reduce((ac, el, idx)=>{
        ac.push({position: idx, image:el}); return ac;
      }, []));

console.log(api_result);

answered Aug 20, 2020 at 12:26

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

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

1 Comment

Marco Disco Over a year ago

GREAT! Thanks a lot! With this simple solution I could cancel a few dozen of lines!

Collectives™ on Stack Overflow

Express + MySql how to get data and create nested array

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related