MySQL5.7之json函数

作者: 5473 分类: MySQL 发布时间: 2017-09-04 21:07 阅读数:270

官方文档:JSON Functions

Name Description
JSON_APPEND() 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() Merge JSON documents
JSON_OBJECT() Create JSON object
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_TYPE() Type of JSON value
JSON_UNQUOTE() Unquote JSON value
JSON_VALID() Whether JSON value is valid

 

1. 概述

MySQL里的json分为json array和json object。 $表示整个json对象,在索引数据时用下标(对于json array,从0开始)或键值(对于json object,含有特殊字符的key要用”括起来,比如$.”my name”,$.”100.200″,$.”姓名”等)

例如:[3, {“a”: [5, 6], “b”: 10}, [99, 100]],那么:

  • $[0]:3
  • $[1]: {“a”: [5, 6], “b”: 10}
  • $[2] :[99, 100]
  • $[3] : NULL
  • $[1].a:[5, 6]
  • $[1].a[1]:6
  • $[1].b:10
  • $[2][0]:99

 

2. 比较规则

json中的数据可以用 =, <, <=, >, >=, <>, !=, and <=> 进行比较。但json里的数据类型可以是多样的,那么在不同类型之间进行比较时,就有优先级了,高优先级的要大于低优先级的(可以用JSON_TYPE()函数查看类型)。优先级从高到低如下:

  1. BLOB
  2. BIT
  3. OPAQUE
  4. DATETIME
  5. TIME
  6. DATE
  7. BOOLEAN
  8. ARRAY
  9. OBJECT
  10. STRING
  11. INTEGER, DOUBLE
  12. NULL

3. 常用函数

3.1 创建函数

3.1.1 JSON_ARRAY

JSON_ARRAY(val1,val2,val3…)

生成一个包含指定元素的json数组。

 

3.1.2 JSON_OBJECT

JSON_OBJECT(key1,val1,key2,val2…)

生成一个包含指定K-V对的json object。如果有key为NULL或参数个数为奇数,则抛错。

 

3.1.3 JSON_QUOTE

JSON_QUOTE(json_val)

将json_val用”号括起来。

 

3.1.4 CONVERT

CONVERT(json_string,JSON)

 

3.2 查询函数

 3.2.1 JSON_CONTAINS

JSON_CONTAINS(json_doc, val[, path])

查询json文档是否在指定path包含指定的数据,包含则返回1,否则返回0。如果有参数为NULL或path不存在,则返回NULL。

 

 3.2.2 JSON_CONTAINS_PATH

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] …)

查询是否存在指定路径,存在则返回1,否则返回0。如果有参数为NULL,则返回NULL。

one_or_all只能取值”one”或”all”,one表示只要有一个存在即可;all表示所有的都存在才行。

 

3.2.3 JSON_EXTRACT

JSON_EXTRACT(json_doc, path[, path] …)

从json文档里抽取数据。如果有参数有NULL或path不存在,则返回NULL。如果抽取出多个path,则返回的数据封闭在一个json array里。

在MySQL 5.7.9+里可以用”->“替代。

 

在MySQL 5.7.13+,还可以用”->>“表示去掉抽取结果的”号,下面三种效果是一样的:

  • JSON_UNQUOTE( JSON_EXTRACT(column, path) )
  • JSON_UNQUOTE(column -> path)
  • column->>path

 

3.2.4 JSON_KEYS

JSON_KEYS(json_doc[, path])

获取json文档在指定路径下的所有键值,返回一个json array。如果有参数为NULL或path不存在,则返回NULL。

 

3.2.5 JSON_SEARCH

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] …])

查询包含指定字符串的paths,并作为一个json array返回。如果有参数为NUL或path不存在,则返回NULL。

  • one_or_all:”one”表示查询到一个即返回;”all”表示查询所有。
  • search_str:要查询的字符串。 可以用LIKE里的’%’或‘_’匹配。
  • path:在指定path下查。

 

 

3.3 修改函数

3.3.1 JSON_APPEND/JSON_ARRAY_APPEND

JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …)

在指定path的json array尾部追加val。如果指定path是一个json object,则将其封装成一个json array再追加。如果有参数为NULL,则返回NULL。

 

3.3.2 JSON_ARRAY_INSERT

JSON_ARRAY_INSERT(json_doc, path, val[, path, val] …)

在path指定的json array元素插入val,原位置及以右的元素顺次右移。如果path指定的数据非json array元素,则略过此val;如果指定的元素下标超过json array的长度,则插入尾部。

 

3.3.3 JSON_INSERT/JSON_REPLACE/JSON_SET

JSON_INSERT(json_doc, path, val[, path, val] …)

在指定path下插入数据,如果path已存在,则忽略此val(不存在才插入)。

 

JSON_REPLACE(json_doc, path, val[, path, val] …)

替换指定路径的数据,如果某个路径不存在则略过(存在才替换)。如果有参数为NULL,则返回NULL。

 

JSON_SET(json_doc, path, val[, path, val] …)

设置指定路径的数据(不管是否存在)。如果有参数为NULL,则返回NULL。

 

3.3.4 JSON_MERGE

JSON_MERGE(json_doc, json_doc[, json_doc] …)

merge多个json文档。规则如下:

  • 如果都是json array,则结果自动merge为一个json array;
  • 如果都是json object,则结果自动merge为一个json object;
  • 如果有多种类型,则将非json array的元素封装成json array再按照规则一进行mege。

 

3.3.5 JSON_REMOVE

JSON_REMOVE(json_doc, path[, path] …)
移除指定路径的数据,如果某个路径不存在则略过此路径。如果有参数为NULL,则返回NULL。

 

3.3.6 JSON_UNQUOTE

JSON_UNQUOTE(val)

去掉val的引号。如果val为NULL,则返回NULL。

 

3.4 JSON特性查询

3.4.1 JSON_DEEPTH

JSON_DEPTH(json_doc)

获取json文档的深度。如果参数为NULL,则返回NULL。

空的json array、json object或标量的深度为1。

 

3.4.2 JSON_LENGTH

JSON_LENGTH(json_doc[, path])

获取指定路径下的长度。如果参数为NULL,则返回NULL。

长度的计算规则:

  • 标量的长度为1;
  • json array的长度为元素的个数;
  • json object的长度为key的个数。

 

3.4.3 JSON_TYPE

JSON_TYPE(json_val)

获取json文档的具体类型。如果参数为NULL,则返回NULL。

 

3.4.4 JSON_VALID

JSON_VALID(val)

判断val是否为有效的json格式,是为1,不是为0。如果参数为NUL,则返回NULL。

出处:https://www.cnblogs.com/waterystone/p/5626098.html

 

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!

发表评论

电子邮件地址不会被公开。 必填项已用*标注