How to query a JSON map in mysql?

Question

I want to query a JSON mysql field as follows:

json:

{
    "key1": [
        {
            "firstname": "jane"
        },
        {
            "firstname": "john"
        }
    ]
}

I want to search for "key1", and extract the [0].firstname field, which in this case would be jane.

But how can I achieve this with JSON_EXTRACT() or similar?

Answer 1

mysql> set @j = '...your json example...';

mysql> select json_unquote(json_extract(@j, '$.key1[0].firstname')) as fn;
+------+
| fn   |
+------+
| jane |
+------+