MySQL: json data type

From here, learned: 

From 5.7.6 of mysql, supports json types.

Create table with json data type: 

CREATE TABLE t1 (jdoc JSON);

Create table with json data, and some value extracted as outer fields for indexing:

mysql> CREATE TABLE jemp (
    ->     c JSON,
    ->     g INT GENERATED ALWAYS AS (c->"$.id"),
    ->     INDEX i (g)
    -> );

Explain running check of commands: add explain.

explain select * from json where doc->"$.status" > 0 ;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | json | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

 

print json pretty by:

select json_pretty(doc) from json;

from here modify json values:

json_insert : add, but not modify existing value

json_set: add and modify existing value

json_replace : only modify existing value