最近在用到MySQL5.7版本的json的查询，感觉到MySQL越来越强大了。

使用到的场景是查询错误日志。我们会把错误日志的cookie数组信息组装成json_encode的形式存储成一个字段，存储到mysql中。

直接看示例代码吧：

#查看MySQL的版本信息
SELECT VERSION();


#查询where字段--注意(虽然clientId为整形，但是json里面请用字符串包装起来)

SELECT * FROM tbl_error_log WHERE cookie->'$.clientId'='100' LIMIT 20;


#查询field字段--两种形式都ojbk

SELECT cookie, cookie->>'$.clientId' AS client_id FROM tbl_error_log WHERE md5='dododbook...' LIMIT 20;

SELECT cookie, JSON_UNQUOTE(cookie->'$.clientId') AS name FROM tbl_error_log WHERE md5='dododbook...' LIMIT 20;
#有时候查询出来的类型是blob请勿惊慌，这是你要的数据，只是你没直观看到而已

#其他相关函数

SELECT json_length(cookie) FROM tbl_error_log WHERE md5='dododbook...';
SELECT json_type(cookie) FROM tbl_error_log WHERE md5='dododbook...';
SELECT JSON_VALID(cookie) a,JSON_VALID(detail_id) b  FROM tbl_error_log WHERE md5='dododbook...' LIMIT 20;
SELECT JSON_KEYS(cookie) as a FROM tbl_error_log WHERE md5='dododbook...' LIMIT 20;

#官方手册
JSON_APPEND() (deprecated 5.7.9)	Append data to JSON document
JSON_ARRAY()	Create JSON array
JSON_ARRAY_APPEND()	Append data to JSON document
JSON_ARRAY_INSERT()	Insert into JSON array
->	Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().
JSON_CONTAINS()	Whether JSON document contains specific object at path
JSON_CONTAINS_PATH()	Whether JSON document contains any data at path
JSON_DEPTH()	Maximum depth of JSON document
JSON_EXTRACT()	Return data from JSON document
->>	Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
JSON_INSERT()	Insert data into JSON document
JSON_KEYS()	Array of keys from JSON document
JSON_LENGTH()	Number of elements in JSON document
JSON_MERGE() (deprecated 5.7.22)	Merge JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE()
JSON_MERGE_PATCH()	Merge JSON documents, replacing values of duplicate keys
JSON_MERGE_PRESERVE()	Merge JSON documents, preserving duplicate keys
JSON_OBJECT()	Create JSON object
JSON_PRETTY()	Prints a JSON document in human-readable format, with each array element or object member printed on a new line, indented two spaces with respect to its parent.
JSON_QUOTE()	Quote JSON document
JSON_REMOVE()	Remove data from JSON document
JSON_REPLACE()	Replace values in JSON document
JSON_SEARCH()	Path to value within JSON document
JSON_SET()	Insert data into JSON document
JSON_STORAGE_SIZE()	Space used for storage of binary representation of a JSON document; for a JSON column, the space used when the document was inserted, prior to any partial updates
JSON_TYPE()	Type of JSON value
JSON_UNQUOTE()	Unquote JSON value
JSON_VALID()	Whether JSON value is valid

天高云淡，望断南飞雁。不到长城非好汉，屈指行程二万。——《清平乐·六盘山》