MySQL JSON Keys

Continuing from the last entry on using MySQL's new JSON data type , let us take a look at the keys in our sample database. Use JSON_KEYS to get a lit of the Keys withing the document.

mysql> SELECT JSON_KEYS(info) FROM stooge;
+-----------------+
| json_keys(info) |
+-----------------+
| ["job", "name"] |
| ["job", "name"] |
| ["job", "name"] |
| ["job", "name"] |
+-----------------+
4 rows in set (0.00 sec)

So how many records have a 'job' key?

mysql> select  JSON_CONTAINS_PATH(info,'all','$.job') from stooge;
+----------------------------------------+
| JSON_CONTAINS_PATH(info,'all','$.job') |
+----------------------------------------+
|                                      1 |
|                                      1 |
|                                      1 |
|                                      1 |
+----------------------------------------+
4 rows in set (0.00 sec)

The 1 means the record has a 'job' key and a 0 would indicate a lack. Not too interesting in this case as all the JSON docs have the same keys. But how do you gran one particular key/value, also know as a object? If we know the object(s)/ record(s) sought contain something to search on we pass that to JSON_CONTAINS.

SELECT * FROM stooge WHERE JSON_CONTAINS(info,'{"job": "Head Stooge"}');

My history of fat fingering brackets, braces, and single/double quotes is pretty long and is it not cheating to use JSON_OBJECT to help here.

mysql> SELECT * FROM stooge WHERE JSON_CONTAINS(info,JSON_OBJECT('job','Head Stooge'));
+------+----------------------------------------------+
| id   | info                                         |
+------+----------------------------------------------+
|    1 | {"job": "Head Stooge", "name": "Moe Howard"} |
+------+----------------------------------------------+
1 row in set (0.00 sec)

Now up to this point we have not extracted the JSON data into a generated column for SQL compatible indexes. If we look at the EXPLAIN for the last query it tells use we had a full table scan. And full table scans are not efficient.

mysql> EXPLAIN select JSON_SEARCH(info,'Comic','job') from stooge\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: stooge
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql>