Select JSON objects in MySQL by key

Question

I have JSON stored in MySQL, which has multiple objects with no key. An example:

[
{
    "glosses": [
        "cascade, rapids, cataract"
    ],
    "raw_glosses": [
        "cascade, rapids, cataract"
    ]
},
{
    "glosses": [
        "waterfall"
    ],
    "raw_glosses": [
        "waterfall"
    ]
},
{
    "glosses": [
        "the sound of a strong water stream"
    ],
    "raw_glosses": [
        "the sound of a strong water stream"
    ]
}
]

How is it possible to select each object by numeric key? For example:

SELECT senses[0], senses[2] FROM table WHERE id = 1;

Answer 1

Using JSON_EXTRACT, you can select objects by key ($[0]).

SELECT JSON_EXTRACT( senses, '$[0]'), JSON_EXTRACT( senses, '$[1]'), JSON_EXTRACT( senses, '$[2]'), senses FROM table WHERE id = 1;

Result:

{"glosses": ["cascade, rapids, cataract"], "raw_glosses": ["cascade, rapids, cataract"]}

{"glosses": ["waterfall"], "raw_glosses": ["waterfall"]}

{"glosses": ["the sound of a strong water stream"], "raw_glosses": ["the sound of a strong water stream"]}