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

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

CodePudding user response：

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;