1

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

shawn caza
  • 342
  • 2
  • 13

1 Answers1

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;
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Yes, thank you! I was always running into errors when I tried stuff like that. Your answer, as is, gives an error too, but it's perfect after removing the extra closing bracket. – shawn caza Jan 22 '23 at 03:16
  • 1
    Sorry about that, I didn't test it. I've edited to fix the extra paren. – Bill Karwin Jan 22 '23 at 03:20
  • No need to apologize. Was banging my head on the wall and this let me see the light. Thanks again. – shawn caza Jan 22 '23 at 21:07