概覽
PolarDB-X目前支援mysql 5.7的所有
json函數,可分為以下五大類:
JSON_ARRAY([val [, val] ...])
- 描述:傳回包含所有參數值的JSON數組(數組可為空)。
- 示例:
mysql> SELECT JSON_ARRAY(123, "polardb-x", NULL, TRUE);
+------------------------------------------+
| JSON_ARRAY(123, 'polardb-x', NULL, true) |
+------------------------------------------+
| [123,"polardb-x",null,true] |
+------------------------------------------+
JSON_OBJECT([key, val [, key, val] ...])
- 描述:根據參數中鍵值對來建立一個JSON對象,如果key為NULL或參數個數不為2的倍數,将傳回相應錯誤提示。
mysql> SELECT JSON_OBJECT('id', 123, 'name', 'polardb-x');
+---------------------------------------------+
| JSON_OBJECT('id', 123, 'name', 'polardb-x') |
+---------------------------------------------+
| {"name":"polardb-x","id":123} |
+---------------------------------------------+
JSON_QUOTE(string)
- 描述:将JSON值用雙引号括起來,并對内部特殊處理字元進行轉義,傳回utf8mb4編碼的字元串結果。若參數為NULL,傳回值為NULL。該函數通常用于生成一個合法的JSON字元串字面量。
mysql> SELECT JSON_QUOTE(null), JSON_QUOTE('"abc"');
+------------------+---------------------+
| JSON_QUOTE(NULL) | JSON_QUOTE('"abc"') |
+------------------+---------------------+
| NULL | ""abc"" |
+------------------+---------------------+
JSON_CONTAINS(target, candidate [, path])
- 描述:判斷給定的candidate JSON文檔是否包含了target JSON文檔;如果給定了path參數,則是判斷candidate能否找到包含target的路徑path,結果傳回1或0。如果隻關心candidate是否存在路徑path,可以使用 函數。
mysql> SET @json_doc = '{"a": 123, "b": null, "c": {"d": 456}}';
mysql> SELECT JSON_CONTAINS(@json_doc, '123', '$.a');
+----------------------------------------+
| JSON_CONTAINS(@json_doc, '123', '$.a') |
+----------------------------------------+
| 1 |
+----------------------------------------+
mysql> SELECT JSON_CONTAINS(@json_doc, 'null', '$.b');
+-----------------------------------------+
| JSON_CONTAINS(@json_doc, 'null', '$.b') |
+-----------------------------------------+
| 1 |
+-----------------------------------------+
mysql> SELECT JSON_CONTAINS(@json_doc, '123', '$.b');
+----------------------------------------+
| JSON_CONTAINS(@json_doc, '123', '$.b') |
+----------------------------------------+
| 0 |
+----------------------------------------+
mysql> SELECT JSON_CONTAINS(@json_doc, '{"d": 456}', '$.c');
+-----------------------------------------------+
| JSON_CONTAINS(@json_doc, '{"d": 456}', '$.c') |
+-----------------------------------------------+
| 1 |
+-----------------------------------------------+
JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
- 判斷給定的json_doc JSON文檔是否存在一條或多條路徑path,結果傳回1或0。參數one_or_all的取值規定如下:
-
- 'one':隻要json_doc存在參數中的至少一條路徑,就傳回1,否則傳回0;
- 'all':隻有json_doc包含參數中的所有路徑時,才傳回1,否則傳回0。
mysql> SET @json_doc = '{"a": 123, "b": null, "c": {"d": 456}}';
mysql> SELECT JSON_CONTAINS_PATH(@json_doc, 'one', '$.a', '$.e');
+----------------------------------------------------+
| JSON_CONTAINS_PATH(@json_doc, 'one', '$.a', '$.e') |
+----------------------------------------------------+
| 1 |
+----------------------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@json_doc, 'all', '$.a', '$.e');
+----------------------------------------------------+
| JSON_CONTAINS_PATH(@json_doc, 'all', '$.a', '$.e') |
+----------------------------------------------------+
| 0 |
+----------------------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@json_doc, 'one', '$.c.d');
+-----------------------------------------------+
| JSON_CONTAINS_PATH(@json_doc, 'one', '$.c.d') |
+-----------------------------------------------+
| 1 |
+-----------------------------------------------+
JSON_EXTRACT(json_doc, path[, path] ...)
- 描述:從JSON文檔中解析出路徑path對應的部分。
mysql> SELECT JSON_EXTRACT('[123, 456, [789, 1000]]', '$[1]');
+-------------------------------------------------+
| JSON_EXTRACT('[123, 456, [789, 1000]]', '$[1]') |
+-------------------------------------------------+
| 456 |
+-------------------------------------------------+
mysql> SELECT JSON_EXTRACT('[123, 456, [789, 1000]]', '$[0]', '$[1]');
+---------------------------------------------------------+
| JSON_EXTRACT('[123, 456, [789, 1000]]', '$[0]', '$[1]') |
+---------------------------------------------------------+
| [123,456] |
+---------------------------------------------------------+
mysql> SELECT JSON_EXTRACT('[123, 456, [789, 1000]]', '$[0]', '$[2]');
+---------------------------------------------------------+
| JSON_EXTRACT('[123, 456, [789, 1000]]', '$[0]', '$[2]') |
+---------------------------------------------------------+
| [123,[789,1000]] |
+---------------------------------------------------------+
column->path
- 描述:->操作符提供了與JSON_EXTRACT函數相同的功能,傳回path對應的列值。
mysql> SELECT JSON_OBJECT('id', 123, 'name', 'polardb-x')->"$.name" as NAME;
+-------------+
| NAME |
+-------------+
| "polardb-x" |
+-------------+
column->>path
-
- JSON_UNQUOTE( JSON_EXTRACT(column, path) )
- JSON_UNQUOTE( column -> path )
mysql> SELECT JSON_OBJECT('id', 123, 'name', 'polardb-x')->"$.name" as NAME;
+-------------+
| NAME |
+-------------+
| "polardb-x" |
+-------------+
JSON_KEYS(json_doc[, path])
- 以JSON數組的形式傳回json_doc對象最外層的key值清單。
mysql> SELECT JSON_KEYS('{"a": 123, "b": {"c": 456}}');
+------------------------------------------+
| JSON_KEYS('{"a": 123, "b": {"c": 456}}') |
+------------------------------------------+
| ["a","b"] |
+------------------------------------------+
JSON_SEARCH(json_doc, one_or_all, search_str [, escape_char[, path] ...])
- 傳回給定字元串search_str在json_doc中的路徑,未找到則傳回NULL。search_str中支援包含如LIKE中使用的%和_通配符作為模糊比對。參數escape_char指定了轉義字元,而參數one_or_all的取值規定如下:
-
- 'one':傳回第一個比對的路徑值(哪一條路徑第一個比對到是不确定的);
-
'all':以數組形式傳回所有路徑值(數組元素順序是不确定的)。
說明 PolarDB-X暫不支援**路徑通配符
mysql> SET @json_doc = '["abc", [{"k1": 123}, "def"], {"k2": "abc"}, {"k3": null}]';
mysql> SELECT JSON_SEARCH(@json_doc, 'one', 'abc');
+--------------------------------------+
| JSON_SEARCH(@json_doc, 'one', 'abc') |
+--------------------------------------+
| "$[0]" |
+--------------------------------------+
mysql> SELECT JSON_SEARCH(@json_doc, 'all', 'abc');
+--------------------------------------+
| JSON_SEARCH(@json_doc, 'all', 'abc') |
+--------------------------------------+
| ["$[0]","$[2].k2"] |
+--------------------------------------+
mysql> SELECT JSON_SEARCH(@json_doc, 'all', 'xyz');
+--------------------------------------+
| JSON_SEARCH(@json_doc, 'all', 'xyz') |
+--------------------------------------+
| NULL |
+--------------------------------------+
mysql> SELECT JSON_SEARCH(@json_doc, 'all', 'def', NULL, '$[*]');
+----------------------------------------------------+
| JSON_SEARCH(@json_doc, 'all', 'def', NULL, '$[*]') |
+----------------------------------------------------+
| "$[1][1]" |
+----------------------------------------------------+
mysql> SELECT JSON_SEARCH(@json_doc, 'all', '%a%');
+--------------------------------------+
| JSON_SEARCH(@json_doc, 'all', '%a%') |
+--------------------------------------+
| ["$[0]","$[2].k2"] |
+--------------------------------------+
JSON_APPEND(json_doc, path, val [, path, val] ...)
即
說明 該函數在MySQL5.7中已過時,在MySQL8.0中被移除。
JSON_ARRAY_APPEND(json_doc, path, val [, path, val] ...)
- 描述:将val追加到json_doc指定JSON數組末尾,并傳回修改後的json_doc。當有多對(path, val)參數時,該函數會從左到右依次計算每一對參數,并将追加完前一對參數後的結果作為下一次計算的輸入。
mysql> SET @json_doc = '{"a": 1, "b": [2, 3], "c": 4}';
mysql> SELECT JSON_ARRAY_APPEND(@json_doc, '$.b', 'x');
+------------------------------------------+
| JSON_ARRAY_APPEND(@json_doc, '$.b', 'x') |
+------------------------------------------+
| {"a":1,"b":[2,3,"x"],"c":4} |
+------------------------------------------+
mysql> SELECT JSON_ARRAY_APPEND(@json_doc, '$.c', 'y');
+------------------------------------------+
| JSON_ARRAY_APPEND(@json_doc, '$.c', 'y') |
+------------------------------------------+
| {"a":1,"b":[2,3],"c":[4,"y"]} |
+------------------------------------------+
JSON_ARRAY_INSERT(json_doc, path, val [, path, val] ...)
- 描述:将val插入到json_doc中JSON數組的指定位置,并傳回修改後的json_doc。如果val插入位置超出了數組大小,則會追加為最後一個元素。當有多對(path, val)參數時,該函數會從左到右依次計算每一對參數,并将插入完前一對參數後的結果作為下一次計算的輸入。
mysql> SET @json_doc = '["a", {"b": [1, 2]}, [3, 4]]';
mysql> SELECT JSON_ARRAY_INSERT(@json_doc, '$[1]', 'x');
+-------------------------------------------+
| JSON_ARRAY_INSERT(@json_doc, '$[1]', 'x') |
+-------------------------------------------+
| ["a","x",{"b":[1,2]},[3,4]] |
+-------------------------------------------+
mysql> SELECT JSON_ARRAY_INSERT(@json_doc, '$[10]', 'x');
+--------------------------------------------+
| JSON_ARRAY_INSERT(@json_doc, '$[10]', 'x') |
+--------------------------------------------+
| ["a",{"b":[1,2]},[3,4],"x"] |
+--------------------------------------------+
mysql> SELECT JSON_ARRAY_INSERT(@json_doc, '$[1].b[1]', 'x');
+------------------------------------------------+
| JSON_ARRAY_INSERT(@json_doc, '$[1].b[1]', 'x') |
+------------------------------------------------+
| ["a",{"b":[1,"x",2]},[3,4]] |
+------------------------------------------------+
mysql> SELECT JSON_ARRAY_INSERT(@json_doc, '$[0]', 'x', '$[3][1]', 'y');
+-----------------------------------------------------------+
| JSON_ARRAY_INSERT(@json_doc, '$[0]', 'x', '$[3][1]', 'y') |
+-----------------------------------------------------------+
| ["x","a",{"b":[1,2]},[3,"y",4]] |
+-----------------------------------------------------------+
JSON_INSERT(json_doc, path, val [, path, val] ...)
- 描述:将val插入到json_doc的指定位置。當有多對(path, val)參數時,該函數會從左到右依次計算每一對參數,并将插入完前一對參數後的結果作為下一次計算的輸入。插入val時的行為,将區分以下兩種情況:
-
- 如果指定path處本身已存在值,則會忽略目前這輪處理的(path, val)對,不會覆寫原本已存在的值;
- 如果指定path處不存在值,則分為以下三種情況:
-
-
- 如果指定path處是對象内不存在的成員,則直接在對象内添加對應的鍵值對;
- 如果指定path處是超出數組下标的元素,則會将val添加到該數組末尾(标量值會自動包裝為隻含一個元素的數組);
- 其他情況,則會忽略目前這輪處理的(path, val)對,不采取任何操作。
-
mysql> SET @json_doc = '{ "a": 1, "b": [2, 3]}';
mysql> SELECT JSON_INSERT(@json_doc, '$.a', 10, '$.c', '[true, false]');
+-----------------------------------------------------------+
| JSON_INSERT(@json_doc, '$.a', 10, '$.c', '[true, false]') |
+-----------------------------------------------------------+
| {"a":1,"b":[2,3],"c":"[true, false]"} |
+-----------------------------------------------------------+
JSON_MERGE(json_doc, json_doc [, json_doc] ...)
即JSON_MERGE_PRESERVE函數(注:該函數在MySQL5.7中已過時,在MySQL8.0.3中被移除)。
JSON_MERGE_PATCH(json_doc, json_doc [, json_doc] ...)
- 合并多個JSON文檔,且會對相同的key進行去重。該函數将從左到右依次合并兩個json_doc(下稱為doc1與doc2),合并規則如下:
-
- 如果doc1不是對象類型,則合并結果即為doc2;
- 如果doc2不是對象類型,則合并結果也為doc2;
- 如果doc1與doc2都是對象類型,則合并結果是包含了以下成員的對象:
-
-
- key隻存在于doc1、而不存在于doc2的所有doc1成員;
- key隻存在于doc2、而不存在于doc1,且值不是null字面量的所有doc2成員;
- 對于key既存在于doc1、又存在于doc2且在doc2中值不為null字面量的成員,會将key對應的兩個value再按上述規則進行遞歸合并。
-
-
- 根據上述規則可知,如果有重複key則會保留doc2中對應的值。
mysql> SELECT JSON_MERGE_PATCH('{"name": "polardb-x"}', '{"id": 123}');
+----------------------------------------------------------+
| JSON_MERGE_PATCH('{"name": "polardb-x"}', '{"id": 123}') |
+----------------------------------------------------------+
| {"name":"polardb-x","id":123} |
+----------------------------------------------------------+
mysql> SELECT JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}');
+--------------------------------------------------+
| JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}') |
+--------------------------------------------------+
| {"a":1} |
+--------------------------------------------------+
mysql> SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }', '{ "a": 3, "c":4 }',
-> '{ "a": 5, "d":6 }');
+---------------------------------------------------------------------------------+
| JSON_MERGE_PATCH('{ "a": 1, "b":2 }', '{ "a": 3, "c":4 }', '{ "a": 5, "d":6 }') |
+---------------------------------------------------------------------------------+
| {"a":5,"b":2,"c":4,"d":6} |
+---------------------------------------------------------------------------------+
JSON_MERGE_PRESERVE(json_doc, json_doc [, json_doc] ...)
- 描述:合并多個JSON文檔,會保留所有的值而不會根據相同key去重。合并規則如下:
-
- 兩個數組的合并結果為一個數組;
- 兩個對象的合并結果為一個對象;
- 标量類型值會自動包裝為數組類型進行合并;
- 一個對象和一個數組合并,則會把對象包裝為含單個元素數組并進行合并。
mysql> SELECT JSON_MERGE_PRESERVE('{"name": "polardb-x"}', '{"id": 123}');
+-------------------------------------------------------------+
| JSON_MERGE_PRESERVE('{"name": "polardb-x"}', '{"id": 123}') |
+-------------------------------------------------------------+
| {"name":"polardb-x","id":123} |
+-------------------------------------------------------------+
mysql> SELECT JSON_MERGE_PRESERVE('{"a":1, "b":2}', '{"b":null}');
+-----------------------------------------------------+
| JSON_MERGE_PRESERVE('{"a":1, "b":2}', '{"b":null}') |
+-----------------------------------------------------+
| {"a":1,"b":[2,null]} |
+-----------------------------------------------------+
mysql> SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b":2 }', '{ "a": 3, "c":4 }',
-> '{ "a": 5, "d":6 }');
+------------------------------------------------------------------------------------+
| JSON_MERGE_PRESERVE('{ "a": 1, "b":2 }', '{ "a": 3, "c":4 }', '{ "a": 5, "d":6 }') |
+------------------------------------------------------------------------------------+
| {"a":[1,3,5],"b":2,"c":4,"d":6} |
+------------------------------------------------------------------------------------+
JSON_REMOVE(json_doc, path [, path] ...)
- 描述:删除json_doc下指定路徑的元素。當有多個path參數時,該函數會從左到右依次計算路徑并删除元素,并将前一次删除後的結果作為下一次計算的輸入。若指定路徑不存在,不會報錯。
mysql> SET @json_doc = '["a", ["b", "c"], "d"]';
mysql> SELECT JSON_REMOVE(@json_doc, '$[1]');
+--------------------------------+
| JSON_REMOVE(@json_doc, '$[1]') |
+--------------------------------+
| ["a","d"] |
+--------------------------------+
JSON_REPLACE(json_doc, path, val [, path, val] ...)
- 描述:用val替換json_doc的指定位置的值,隻會替換已存在的值。當有多對(path, val)參數時,該函數會從左到右依次計算每一對參數,并将計算完前一對參數後的結果作為下一次計算的輸入。若指定路徑不存在,不會報錯。
mysql> SET @json_doc = '{ "a": 1, "b": [2, 3]}';
mysql> SELECT JSON_REPLACE(@json_doc, '$.a', 10, '$.c', '[true, false]');
+------------------------------------------------------------+
| JSON_REPLACE(@json_doc, '$.a', 10, '$.c', '[true, false]') |
+------------------------------------------------------------+
| {"a":"10","b":[2,3]} |
+------------------------------------------------------------+
JSON_SET(json_doc, path, val [, path, val] ...)
- 描述:在json_doc的指定位置插入或更新值val。該函數會從左到右依次計算每一對參數,并将計算完前一對參數後的結果作為下一次計算的輸入。将區分以下兩種情況:
-
- 如果指定path處本身已存在值,會用參數val的值覆寫原本已存在的值;
mysql> SET @json_doc = '{ "a": 1, "b": [2, 3]}';
mysql> SELECT JSON_SET(@json_doc, '$.a', 10, '$.c', '[true, false]');
+-------------------------------------------------+
| JSON_SET(@j, '$.a', 10, '$.c', '[true, false]') |
+-------------------------------------------------+
| {"a":10,"b":[2,3],"c":[true,false]} |
+-------------------------------------------------+
JSON_UNQUOTE(json_val)
- 描述:去除json值外圍的雙引号,對轉義字元進行反轉義,傳回utf8mb4編碼的字元串結果。
mysql> SELECT JSON_UNQUOTE('"abc"');
+-----------------------+
| JSON_UNQUOTE('"abc"') |
+-----------------------+
| abc |
+-----------------------+
mysql> SELECT JSON_UNQUOTE('"a\\tbc"');
+--------------------------+
| JSON_UNQUOTE('"a\\tbc"') |
+--------------------------+
| a bc |
+--------------------------+
mysql> SELECT JSON_UNQUOTE('"\\t\\u0032"');
+------------------------------+
| JSON_UNQUOTE('"\\t\\u0032"') |
+------------------------------+
| 2 |
+------------------------------+
JSON_DEPTH(json_doc)
- 描述:傳回json_doc的最大深度,規則如下:
-
- 空數組、空對象以及标量類型,深度均為1;
- 隻有單個元素的數組深度為1;
- 對象中如果所有成員的value深度均為1,則自身的深度為2。
mysql> SELECT JSON_DEPTH('[10, {"a": 20}]');
+-------------------------------+
| JSON_DEPTH('[10, {"a": 20}]') |
+-------------------------------+
| 3 |
+-------------------------------+
JSON_LENGTH(json_doc [, path])
- 描述:傳回json_doc的長度,規則如下:
-
- 标量類型的長度為1;
- 數組類型的長度為數組元素個數;
- 對象類型的長度為對象成員個數;
- 結果不包含内部嵌套的數組或對象長度。
mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');
+-----------------------------------------+
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}') |
+-----------------------------------------+
| 2 |
+-----------------------------------------+
JSON_TYPE(json_val)
- 描述:傳回參數json_val的json類型。
mysql> SET @json_obj = '{"a": [10, true]}';
mysql> SELECT JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a'));
+-------------------------------------------+
| JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a')) |
+-------------------------------------------+
| ARRAY |
+-------------------------------------------+
mysql> SELECT JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a[0]'));
+----------------------------------------------+
| JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a[0]')) |
+----------------------------------------------+
| INTEGER |
+----------------------------------------------+
mysql> SELECT JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a[1]'));
+----------------------------------------------+
| JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a[1]')) |
+----------------------------------------------+
| BOOLEAN |
+----------------------------------------------+
JSON_VALID(val)
- 描述:判斷參數值val是否為合法的json格式。
mysql> SELECT JSON_VALID('hello'), JSON_VALID('"hello"');
+---------------------+-----------------------+
| JSON_VALID('hello') | JSON_VALID('"hello"') |
+---------------------+-----------------------+
| 0 | 1 |
+---------------------+-----------------------+
JSON_PRETTY(json_doc)
- 描述:通過添加換行與縮進,以更直覺的方式列印出json_doc。
mysql> SET @json_doc = '["abc", [{"k1": 123}, "def"], {"k2": "abc"}, {"k3": null}]';
mysql> SELECT JSON_PRETTY(@json_doc);
+---------------------------------------------------------------------------+
| JSON_PRETTY(@json_doc) |
+---------------------------------------------------------------------------+
| [
"abc",
[
{
"k1":123
},
"def"
],
{
"k2":"abc"
},
{
}
] |
+---------------------------------------------------------------------------+
JSON_STORAGE_SIZE(json_doc)
- 描述:傳回json_doc以二進制形式存儲的位元組大小。
mysql> SET @json_doc = '[999, "polardb-x", [1, 2, 3], 888.88]';
mysql> SELECT JSON_STORAGE_SIZE(@json_doc) AS Size;
+------+
| Size |
+------+
| 48 |
+------+