在mysql未支持json数据类型时，我们通常使用varchar、blob或text的数据类型存储json字符串，对mysql来说，用户插入的数据只是序列化后的一个普通的字符串，不会对JSON文档本身的语法合法性做检查，文档的合法性需要用户自己保证。在使用时需要先将整个json对象从数据库读取出来，在内存中完成解析及相应的计算处理，这种方式增加了数据库的网络开销并降低处理效率。
从 MySQL 5.7.8 开始，MySQL 支持RFC 7159定义的全部json 数据类型，具体的包含四种基本类型（strings, numbers, booleans,and null）和两种结构化类型（objects and arrays）。可以有效地访问 JSON文档中的数据。与将 JSON 格式的字符串存储在字符串列中相比，该数据类型具有以下优势：
mysql为了提供对json对象的支持，提供了一套将json字符串转为结构化二进制对象的存储方式。json会被转为二进制的doc对象存储于磁盘中（在处理JSON时MySQL使用的utf8mb4字符集，utf8mb4是utf8和ascii的超集）。
doc对象包含两个部分，type和value部分。其中type占1字节，可以表示16种类型：大的和小的json object类型、大的和小的 json array类型、literal类型（true、false、null三个值）、number类型（int6、uint16、int32、uint32、int64、uint64、double类型、utf8mb4 string类型和custom data（mysql自定义类型），具体可以参考源码json_binary.cc和json_binary.h进行学习。
下面进行简单介绍：
type ::=
0x00 | // small JSON object
0x01 | // large JSON object
0x02 | // small JSON array
0x03 | // large JSON array
0x04 | // literal (true/false/null)
0x05 | // int16
0x06 | // uint16
0x07 | // int32
0x08 | // uint32
0x09 | // int64
0x0a | // uint64
0x0b | // double
0x0c | // utf8mb4 string
0x0f // custom data (any MySQL data type)
array表示json数组，array类型主要包含4部分：
array ::= element-count size value-entry value
我们来使用示意图更清晰的展示它的结构：
举例说明：
需要注意的是：
json类型数据插入时有两种方式，一种是基于字符串格式插入，另一种是基于json_object()函数，在使用json_object()函数只需按k-v顺序，以,符号隔开顺序插入即可，MYSQL会自动验证 JSON 文档，无效的文档会产生错误。
mysql> CREATE TABLE t1 (jdoc JSON);
Query OK, 0 rows affected (0.20 sec)
mysql> INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO t1 VALUES('[1, 2,');
ERROR 3140 (22032) at line 2: Invalid JSON text:
"Invalid value." at position 6 in value (or column) '[1, 2,'.
当一个字符串被解析并发现是一个有效的 JSON 文档时，它也会被规范化：具有与文档中先前找到的键重复的键的成员被丢弃（即使值不同）。以下第一个sql中通过 JSON_OBJECT()调用生成的对象值不包括第二个key1元素，因为该键名出现在值的前面；第二个sql中只保留了x第一次出现的值：
mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');
+------------------------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') |
+------------------------------------------------------+
| {"key1": 1, "key2": "abc"} |
+------------------------------------------------------+
mysql> INSERT INTO t1 VALUES
> ('{"x": 17, "x": "red"}'),
> ('{"x": 17, "x": "red", "x": [3, 5, 7]}');
mysql> SELECT c1 FROM t1;
+-----------+
| c1 |
+-----------+
| {"x": 17} |
| {"x": 17} |
+-----------+
MySQL 5.7支持JSON_MERGE（）的合并算法，多个对象合并时产生一个对象。
可将多个数组合并为一个数组：
mysql> SELECT JSON_MERGE('[1, 2]', '["a", "b"]', '[true, false]');
+-----------------------------------------------------+
| JSON_MERGE('[1, 2]', '["a", "b"]', '[true, false]') |
+-----------------------------------------------------+
| [1, 2, "a", "b", true, false] |
+-----------------------------------------------------+
当合并数组与对象时，会将对象转换为新数组进行合并：
mysql> SELECT JSON_MERGE('[10, 20]', '{"a": "x", "b": "y"}');
+------------------------------------------------+
| JSON_MERGE('[10, 20]', '{"a": "x", "b": "y"}') |
+------------------------------------------------+
| [10, 20, {"a": "x", "b": "y"}] |
+------------------------------------------------+
如果多个对象具有相同的键，则生成的合并对象中该键的值是包含键值的数组
mysql> SELECT JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}');
+----------------------------------------------------+
| JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}') |
+----------------------------------------------------+
| {"a": [1, 4], "b": 2, "c": 3} |
+----------------------------------------------------+
MySQL 8.0.3（及更高版本）支持两种合并算法，由函数 JSON_MERGE_PRESERVE()和 JSON_MERGE_PATCH(). 它们在处理重复键的方式上有所不同：JSON_MERGE_PRESERVE()保留重复键的值（与5.7版本的JSON_MERGE（）相同），而 JSON_MERGE_PATCH()丢弃除最后一个值之外的所有值。具体的
mysql> SELECT JSON_MERGE_PRESERVE('{"a":1,"b":2}', '{"a":3,"c":3}');
+-------------------------------------------------------+
| JSON_MERGE_PRESERVE('{"a":1,"b":2}', '{"a":3,"c":3}') |
+-------------------------------------------------------+
| {"a": [1, 3], "b": 2, "c": 3} |
+-------------------------------------------------------+
1 row in set (0.01 sec)
mysql> SELECT JSON_MERGE_PATCH('{"a":1,"b":2}', '{"a":3,"c":3}');
+----------------------------------------------------+
| JSON_MERGE_PATCH('{"a":1,"b":2}', '{"a":3,"c":3}') |
+----------------------------------------------------+
| {"a": 3, "b": 2, "c": 3} |
+----------------------------------------------------+
1 row in set (0.02 sec)
mysql> SELECT JSON_MERGE_PRESERVE('["a", 1]', '"a"','{"key": "value"}');
+-----------------------------------------------------------+
| JSON_MERGE_PRESERVE('["a", 1]', '"a"','{"key": "value"}') |
+-----------------------------------------------------------+
| ["a", 1, "a", {"key": "value"}] |
+-----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_MERGE_PATCH('["a", 1]', '"a"','{"key": "value"}') ;
+--------------------------------------------------------+
| JSON_MERGE_PATCH('["a", 1]', '"a"','{"key": "value"}') |
+--------------------------------------------------------+
| {"key": "value"} |
+--------------------------------------------------------+
1 row in set (0.01 sec)
MySQL 5.7.7+本身提供了很多原生的函数以及路径表达式来方便用户访问JSON数据。
JSON_EXTRACT()函数用于解析json对象，->符号是就一种JSON_EXTRACT()函数的等价模式。例如查询上面t1表中 jdoc字段中key值为x的值
SELECT jdoc->'$.x' FROM t1;
SELECT JSON_EXTRACT(jdoc,'$.x') FROM t1;
JSON_EXTRACT返回值会带有” “,如果想获取原本的值可以使用JSON_UNQUOTE
mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan" |
+---------------------------------------------------------+
mysql> SELECT JSON_UNQUOTE(json_extract('{"id": 14, "name": "Aztalan"}', '$.name'));;
+-----------------------------------------------------------------------+
| JSON_UNQUOTE(json_extract('{"id": 14, "name": "Aztalan"}', '$.name')) |
+-----------------------------------------------------------------------+
| Aztalan |
+-----------------------------------------------------------------------+
json路径的语法：
pathExpression:
scope[(pathLeg)*]
pathLeg:
member | arrayLocation | doubleAsterisk
member:
period ( keyName | asterisk )
arrayLocation:
leftBracket ( nonNegativeInteger | asterisk ) rightBracket
keyName:
ESIdentifier | doubleQuotedString
doubleAsterisk:
'**'
period:
'.'
asterisk:
'*'
leftBracket:
'['
rightBracket:
']'
以json { “a”: [ [ 3, 2 ], [ { “c” : “d” }, 1 ] ], “b”: { “c” : 6 }, “one potato”: 7, “b.c” : 8 } 为例：
\(.a\[1\] 获取的值为 \[ { “c” : “d” }, 1 \]
\).b.c 获取的值为 6
$.”b.c” 获取的值为 8（因为键名包含不合法的表达式所以需要使用引号）
mysql> select json_extract('{ "a": [ [ 3, 2 ], [ { "c" : "d" }, 1 ] ], "b": { "c" : 6 }, "one potato": 7, "b.c" : 8 }','$**.c');
+-------------------------------------------------------------------------------------------------------------------+
| JSON_EXTRACT('{ "a": [ [ 3, 2 ], [ { "c" : "d" }, 1 ] ], "b": { "c" : 6 }, "one potato": 7, "b.c" : 8 }','$**.c') |
+-------------------------------------------------------------------------------------------------------------------+
| ["d", 6] |
+-------------------------------------------------------------------------------------------------------------------+
\(**.c 匹配到了两个路径 :
\).a[1].c 获取的值是”d”
$.b.c 获取的值为 6
一些函数采用现有的 JSON 文档，以某种方式对其进行修改，然后返回结果修改后的文档。路径表达式指示在文档中进行更改的位置。例如，JSON_SET()、 JSON_INSERT()和 JSON_REPLACE()函数各自采用现有的 JSON 文档，加上一个或多个路径和值对，来描述修改文档和要更新的值。这些函数在处理文档中现有值和不存在值的方式上有所不同。
具体如下
mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]';
JSON_SET()替换存在的路径的值并添加不存在的路径的值：
mysql> SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+--------------------------------------------+
| JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+--------------------------------------------+
| ["a", {"b": [1, false]}, [10, 20, 2]] |
+--------------------------------------------+
在这种情况下，路径\(\[1\].b\[0\]选择一个现有值 ( true)，该值将替换为路径参数 ( 1) 后面的值。该路径\)[2][2]不存在，因此将相应的值 ( 2) 添加到 选择的值中$[2]。
JSON_INSERT()添加新值但不替换现有值：
mysql> SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+-----------------------------------------------+
| JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+-----------------------------------------------+
| ["a", {"b": [true, false]}, [10, 20, 2]] |
+-----------------------------------------------+
JSON_REPLACE()替换现有值并忽略新值：
mysql> SELECT JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+------------------------------------------------+
| JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+------------------------------------------------+
| ["a", {"b": [1, false]}, [10, 20]] |
+------------------------------------------------+
JSON_REMOVE()接受一个 JSON 文档和一个或多个路径，这些路径指定要从文档中删除的值。返回值是原始文档减去文档中存在的路径选择的值：
mysql> SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');
+---------------------------------------------------+
| JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]') |
+---------------------------------------------------+
| ["a", {"b": [true]}] |
+---------------------------------------------------+
\(\[2\]匹配\[10, 20\] 并删除它。
\)[1].b[1]匹配 元素false中 的第一个实例b并将其删除。
不匹配的第二个实例$[1].b[1]：该元素已被删除，路径不再存在，并且没有效果。
JSON值可以使用=, <, <=, >, >=, <>, !=, <=>等操作符，BETWEEN, IN,GREATEST, LEAST等操作符现在还不支持。JSON值使用的两级排序规则，第一级基于JSON的类型，类型不同的使用每个类型特有的排序规则。
JSON类型按照优先级从高到低为
BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER, DOUBLE
NULL
优先级高的类型大，不用再进行其他的比较操作；如果类型相同，每个类型按自己的规则排序。具体的规则如下：
现在MySQL不支持对JSON列进行索引，官网文档的说明是：
JSON columns cannot be indexed. You can work around this restriction by creating an index on a generated column that extracts a scalar value from the JSON column.
虽然不支持直接在JSON列上建索引，但MySQL规定，可以首先使用路径表达式对JSON文档中的标量值建立虚拟列，然后在虚拟列上建立索引。这样用户可以使用表达式对自己感兴趣的键值建立索引。举个具体的例子来说明：
ALTER TABLE features ADD feature_street VARCHAR(30) AS (JSON_UNQUOTE(feature->"$.properties.STREET"));
ALTER TABLE features ADD INDEX (feature_street);
两个步骤，可以对feature列中properties键值下的STREET键(feature->”$.properties.STREET”)创建索引。
其中，feature_street列就是新添加的虚拟列。之所以取名虚拟列，是因为与它对应的还有一个存储列(stored column)。它们最大的区别为虚拟列只修改数据库的metadata，并不会存储真实的数据在硬盘上，读取过程也是实时计算的方式；而存储列会把表达式的列存储在硬盘上。两者使用的场景不一样，默认情况下通过表达式生成的列为虚拟列。
这样虚拟列的添加和删除都会非常快，而在虚拟列上建立索引跟传统的建立索引的方式并没有区别，会提高虚拟列读取的性能，减慢整体插入的性能。虚拟列的特性结合JSON的路径表达式，可以方便的为用户提供高效的键值索引功能。
作者：京东物流 王凤辉
来源：京东云开发者社区 自猿其说Tech