How do I implement an array from a database?

Question

I want to implement an array from the received data that will contain objects with the same identifiers.

 pool.query('SELECT * FROM columnslist INNER JOIN tableslist ON columnKey = keyTable', (error, result) => {
        response.send(result);
    });

After executing this code, I get the expected result:

[
    {
        "id": 1,
        "columnOne": "qeqeqq qq wq qw wwqqwdqd",
        "columnTwo": "qdqdqdq wdqdqwqwd",
        "columnThree": "dqwdq qw qqsvds",
        "columnFour": "svsdvsxcvscsv svd ds",
        "columnFive": "sdvsdvsdvs ds sdd",
        "columnKey": 1,
        "keyTable": 1,
        "name": "Test"
    },
    {
        "id": 3,
        "columnOne": "qdqwdwq",
        "columnTwo": "dqdqd",
        "columnThree": "qdqdwq",
        "columnFour": "wdqwdqwq",
        "columnFive": "wdqdqw",
        "columnKey": 2,
        "keyTable": 2,
        "name": "Qqeqeqeq"
    },
    {
        "id": 4,
        "columnOne": "qdqwdwq",
        "columnTwo": "dqdqd",
        "columnThree": "qdqdwq",
        "columnFour": "wdqwdqwq",
        "columnFive": "wdqdqw",
        "columnKey": 2,
        "keyTable": 2,
        "name": "Qqeqeqeq"
    }
]

Tell me how you can implement or process the response to get this result? I need an array to be created in the array, as I wrote earlier, with the same identifiers:

[ 
[{"keyTable": 1,"name": "Test"...}],
[{"columnKey": 2,"keyTable": 2...}, [{"columnKey": 2,"keyTable": 2...}]
]

Thanks to!

score 0 · Answer 1 · answered Sep 07 '21 at 11:03

0

You can typecast objects into an associative array like this

$array = (array) $yourObject;

In your case

 $array = (array) $result;

This will help out you

answered Sep 07 '21 at 11:03

Raees

29
10

score 0 · Answer 2 · answered Sep 07 '21 at 11:47

You can use JSON_ARRAYAGG() AND GROUP BY, for your example the query is :

SELECT ALL 
    columnKey, keyTable, 
    JSON_ARRAYAGG(JSON_OBJECT(
        'id', id,
        'columnOne', columnOne,
        'columnTwo', columnTwo,
        'columnThree', columnThree,
        'columnFour', columnFour,
        'columnFive', columnFive,
        'columnKey', columnKey,
        'keyTable', keyTable,
        'name', name
    )) AS jsonData
FROM columnslist
INNER JOIN tableslist ON columnKey = keyTable
GROUP BY columnKey, keyTable

https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_json-arrayagg

How do I implement an array from a database?

2 Answers2