查询

1. 使用箭头(->)获取变量

语法： column->path

示例:

表名：temp

序号	json_column	normal_column
1	{"a":"this is value"}	this is text

select * from where json_column -> "$.a" = 'this is value'

2. json_extract

语法： json_extract(column, "$.path")

示例：

select * from where json_extract(json_column, "$.a")= 'this is value'

3. json_contains

语法：json_contains(column, value, "$.path")

select * from temp where json_contains(json_column, "this ", "$.a")

创建

Name	Description
`->`	Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().
`->>` (introduced 5.7.13)	Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
`JSON_APPEND()` (deprecated)	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
`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
`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()` (introduced 5.7.22)	Merge JSON documents, replacing values of duplicate keys
`JSON_MERGE_PRESERVE()` (introduced 5.7.22)	Merge JSON documents, preserving duplicate keys
`JSON_OBJECT()`	Create JSON object
`JSON_PRETTY()` (introduced 5.7.22)	Print a JSON document in human-readable format
`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()` (introduced 5.7.22)	Space used for storage of binary representation of a JSON document
`JSON_TYPE()`	Type of JSON value
`JSON_UNQUOTE()`	Unquote JSON value
`JSON_VALID()`	Whether JSON value is valid

1. json_set

语法： json_set(column, "$.path", value[, "$.path", value ...])

如果json中存在变量则更新，不存在则新增变量并设置值

示例：

update temp set json_column = json_set(ext, "$.a", "666", "$.b", "aasdas")

2. json_array

语法：json_array(val0[, val1, ...])

示例：

select json_array(1, 2, 3) as result0,       json_array(1, "a", 3) as result1,       json_array(1, "a", null) as result2

结果：

序号	result0	result1	result2
1	[1, 2, 3]	[1, "a", 3]	[1, "a", null]

3. json_quote / json_unquote

语法：

json_quote(String) 对json字符串进行转义；

json_unquote(String) 不对字符串进行转义

示例：

SELECT JSON_QUOTE('"Hot", "Warm", "Cold"') AS 'Result0', JSON_UNQUOTE(JSON_QUOTE('"Hot", "Warm", "Cold"')) AS 'Result'

结果：

序号	Result0	Result
1	"\"Hot\", \"Warm\", \"Cold\""	"Hot", "Warm", "Cold"

MySQL json类型数据的使用

查询