Returning MySQL data as an OBJECT rather than an ARRAY (Knex)

Asked Oct 05 '18 at 10:54

Active Oct 05 '18 at 11:12

Viewed 862 times

Is there a way to get the output of a MySQL query to list rows in the following structure

{
    1:{voo:bar,doo:dar},
    2:{voo:mar,doo:har}
}

as opposed to

[
    {id:1,voo:bar,doo:dar},
    {id:2,voo:mar,doo:har}
]

which I then have to loop through to create the desired object?

I should add that within each row I am also concatenating results to form an object, and from what I've experimented with you can't group_concatenate inside a group_concatenation. As follows:

knex('table').select(
    'table.id',
    'table.name',
    knex.raw(
        `CONCAT("{", GROUP_CONCAT(DISTINCT
        '"',table.voo,'"',':','"',table.doo,'"'),
        "}") AS object`
    )
.groupBy('table.id')

Could GROUP BY be leveraged in any way to achieve this? Generally I'm inexperienced at SQL and don't know what's possible and what's not.

edited Oct 05 '18 at 11:12

asked Oct 05 '18 at 10:54

MikeyB

May be this could be helpful https://stackoverflow.com/questions/41758870/how-to-convert-result-table-to-json-array-in-mysql – Alexey Usharovski Oct 05 '18 at 10:57
please, show your query – rgen3 Oct 05 '18 at 11:05
edited and updated to show where I'm at, thanks – MikeyB Oct 05 '18 at 11:13
I assume you mean JSON output? Then i should more look like this to be valid `{ "1": { "a": "b" }, "2": { "a": "b" } }` – Raymond Nijland Oct 05 '18 at 11:19
Sorry, yes, by object I mean JSON, which my top example still is. – MikeyB Oct 05 '18 at 11:28

Returning MySQL data as an OBJECT rather than an ARRAY (Knex)

0 Answers0