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;

If you're going to store data in JSON columns in MySQL, you should read the documentation on the builtin JSON functions: dev.mysql.com/doc/refman/8.0/en/json-functions.html and also do some practice. Try them out on some test data, and make sure you know how to use each one. — Bill Karwin
– Bill Karwin, Commented Mar 23, 2022 at 17:56

Kohjah Breese · Accepted Answer · 2022-03-23 17:34:56Z

2

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"]}

answered Mar 23, 2022 at 17:34

Kohjah Breese

4,1666 gold badges34 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Akina Over a year ago

senses->'$[0]' is... at least it is in short JSON_EXTRACT.

Collectives™ on Stack Overflow

Select JSON objects in MySQL by key

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related