Return a MySQL result, with results formated as JSON, with row id as the JSON key for each row result

Question

A query like this:

SELECT JSON_ARRAYAGG(JSON_OBJECT('id', id, 'tag', tag)) from all_tags;

produces a result like this:

[
    {
        "id": 979,
        "tag": "alternative"
    },
    {
        "id": 947,
        "tag": "ambient"
    }
]

The above is ok, but what I really want is to get a result like this:

[
    979:{
        "tag": "alternative"
    },
    947: {
        "tag": "ambient"
    }
]

Is this possible to do only using mysql?

I've tried SELECT JSON_ARRAYAGG(JSON_OBJECT(id, JSON_OBJECT( 'tag', tag))) from all_tags;

This does allow me to nest columns as a value for a row id. But it doesn't really help as the key is still nested in an object.

Ultimately I'm hoping to use the result as a map in Javacript to look up properties by key. I could shape the data in JS, I'm just curious if it can be done with MySQL

Answer 1

What you show is not valid JSON. If you use the square brackets [ ] then it's just an array, not an object. But if you use the key: value format, that's an object, not an array.

I think the following comes closer to what you want:

SELECT JSON_OBJECTAGG(id, JSON_OBJECT( 'tag', tag)) from all_tags;