第一標題
1、JSON_ARRAY_APPEND數組類的追加
這個用法主要是處理JSON字段中值數組型的數據,主要是追加值,不是標準數組類型的,這個函數會自動轉換為數組形式,把先前的值作為數組的第一個元素然後進行追加,有批次追加方法:
mysql> SET @j = '["a", ["b", "c"], "d"]';
在字段中第二個數值的數組中追加一個元素1:
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1); +----------------------------------+ | JSON_ARRAY_APPEND(@j, '$[1]', 1) | +----------------------------------+ | ["a", ["b", "c", 1], "d"] | +----------------------------------+
在字段中第一個數值的數組中追加一個元素2,如果不是數組形式的單一值會改變其為數組並追加:
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[0]', 2); +----------------------------------+ | JSON_ARRAY_APPEND(@j, '$[0]', 2) | +----------------------------------+ | [["a", 2], ["b", "c"], "d"] | +----------------------------------+
在字段中第二個數值的第一個數組中追加一個元素3,如果不是數組形式的單個數值會改變其為數組並追加:
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1][0]', 3); +-------------------------------------+ | JSON_ARRAY_APPEND(@j, '$[1][0]', 3) | +-------------------------------------+ | ["a", [["b", 3], "c"], "d"] | +-------------------------------------+
在字段中key為”b”的value數組中追加一個元素”x”:
mysql> SET @j = '{"a": 1, "b": [2, 3], "c": 4}'; mysql> SELECT JSON_ARRAY_APPEND(@j, '$.b', 'x'); +------------------------------------+ | JSON_ARRAY_APPEND(@j, '$.b', 'x') | +------------------------------------+ | {"a": 1, "b": [2, 3, "x"], "c": 4} | +------------------------------------+
”在字段中的中為為(數組中追加一個元素」y」:
mysql> SELECT JSON_ARRAY_APPEND(@j, '$.c', 'y'); +--------------------------------------+ | JSON_ARRAY_APPEND(@j, '$.c', 'y') | +--------------------------------------+ | {"a": 1, "b": [2, 3], "c": [4, "y"]} | +--------------------------------------+如果JSON字段為標準的JSON類型,使用這個函數則會把其變化數組形式,並追加值:
mysql> SET @j = '{"a": 1}'; mysql> SELECT JSON_ARRAY_APPEND(@j, '$', 'z'); +---------------------------------+ | JSON_ARRAY_APPEND(@j, '$', 'z') | +---------------------------------+ | [{"a": 1}, "z"] | +---------------------------------+如果JSON字段為標準的JSON類型,批次追加:
mysql> SELECT JSON_ARRAY_APPEND(@j, '$', 'z','$', 'w'); +---------------------------------+ | JSON_ARRAY_APPEND(@j, '$', 'z') | +---------------------------------+ | [{"a": 1}, "z", "w"] | +---------------------------------+2、JSON_ARRAY_INSERT數組類的插入這個用法主要是處理JSON字段中值數組型的數據,主要是插入,不是標準數組類型的,這個函數會自動轉換為數組形式,把先前的值作為數組的第一個元素然後進行追加:
mysql> SET @j = '["a", {"b": [1, 2]}, [3, 4]]';在數組第1個值後面插入字符'x' 元素:
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x'); +------------------------------------+ | JSON_ARRAY_INSERT(@j, '$[1]', 'x') | +------------------------------------+ | ["a", "x", {"b": [1, 2]}, [3, 4]] |在數組第1個值前面插入字符'x' 元素:
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[0]', 'x'); +------------------------------------+ | JSON_ARRAY_INSERT(@j, '$[1]', 'x') | +------------------------------------+ | ["x","a", {"b": [1, 2]}, [3, 4]] | +------------------------------------+
在數組第100個值後面插入字元'x' 元素,如果超長,則在最後一個值後面插入:
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[100]', 'x'); +--------------------------------------+ | JSON_ARRAY_INSERT(@j, '$[100]', 'x') | +--------------------------------------+ | ["a", {"b": [1, 2]}, [3, 4], "x"] | +--------------------------------------+在數組第1個值中插入:
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x'); +-----------------------------------------+ | JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x') | +-----------------------------------------+ | ["a", {"b": ["x", 1, 2]}, [3, 4]] | +-----------------------------------------+在數組第1個值中為'b'的value數組第1個值前面入字符元素'x':
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[2][1]', 'y'); +---------------------------------------+ | JSON_ARRAY_INSERT(@j, '$[2][1]', 'y') | +---------------------------------------+ | ["a", {"b": [1, 2]}, [3, "y", 4]] | +---------------------------------------+在數組第2個值後面數組第1個值後面入字符元素'y':
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y'); +----------------------------------------------------+ | JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y') | +----------------------------------------------------+ | ["x", "a", {"b": [1, 2]}, [3, 4]] | +----------------------------------------------------+數組元素的批量插入:
3、JSON_INSERT標準JSON值的插入如果這個key已經存在,則不會去覆蓋舊值,不存在的插入:
mysql> SET @j = '{ "a": 1, "b": [2, 3]}'; mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]'); +----------------------------------------------------+ | JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]') | +----------------------------------------------------+ | {"a": 1, "b": [2, 3], "c": "[true, false]"} | +----------------------------------------------------+4、JSON_MERGE合併操作JSON_MERGE這個主要用於合併:如果兩個是數組的,則合併為一個數組;如果兩個是JSON的,則合併為一個JSON,如果兩個JSON有相同的key,則value合併為數組;如果是兩個單值的,則合併為一個數組;如果是數組,一個是JSON,則合併為一個數組:
mysql> SELECT JSON_MERGE('[1, 2]', '[true, false]'); +---------------------------------------+ | JSON_MERGE('[1, 2]', '[true, false]') | +---------------------------------------+ | [1, 2, true, false] | +---------------------------------------+ mysql> SELECT JSON_MERGE('{"name": "x"}', '{"id": 47}'); +-------------------------------------------+ | JSON_MERGE('{"name": "x"}', '{"id": 47}') | +-------------------------------------------+ | {"id": 47, "name": "x"} | +-------------------------------------------+ mysql> SELECT JSON_MERGE('{"id": "x"}', '{"id": 47}'); +-------------------------------------------+ | JSON_MERGE('{"id": "x"}', '{"id": 47}') | +-------------------------------------------+ | {"id": ["x",47]} | +-------------------------------------------+ mysql> SELECT JSON_MERGE('1', 'true'); +-------------------------+ | JSON_MERGE('1', 'true') | +-------------------------+ | [1, true] | +-------------------------+ mysql> SELECT JSON_MERGE('[1, 2]', '{"id": 47}'); +------------------------------------+ | JSON_MERGE('[1, 2]', '{"id": 47}') | +------------------------------------+ | [1, 2, {"id": 47}] | +------------------------------------+5、JSON_REMOVE刪除操作JSON_REMOVE刪除操作,這個比較簡單,刪除字段中某個值,數組的或是JSON格式的都可以:
mysql> SET @j = '["a", ["b", "c"], "d"]'; mysql> SELECT JSON_REMOVE(@j, '$[1]'); +-------------------------+ | JSON_REMOVE(@j, '$[1]') | +-------------------------+ | ["a", "d"] | +-------------------------+ mysql> SET @j = '{"name": "x","id": 47}'; mysql> SELECT JSON_REMOVE(@j, '$.id'); +-------------------------+ | JSON_REMOVE(@j, '$.id') | +-------------------------+ | {"name": "x"} | +-------------------------+ mysql> SELECT JSON_REMOVE(@j, '$.id', '$.name'); +-----------------------------------+ | JSON_REMOVE(@j, '$.id', '$.name') | +-----------------------------------+ | {} | +-----------------------------------+ mysql> SET @j = '{"name": "x","id": [47,48]}'; mysql> SELECT JSON_REMOVE(@j, '$.id[1]'); +-------------------------+ | JSON_REMOVE(@j, '$.id') | +-------------------------+ | {"id": [47],"name": "x"} | +-------------------------+6、JSON_REPLACE操作JSON_REPLACE替換操作,有則替換,無則忽略。
mysql> SET @j = '{ "a": 1, "b": [2, 3]}'; mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]'); +-----------------------------------------------------+ | JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]') | +-----------------------------------------------------+ | {"a": 10, "b": [2, 3]} +-----------------------------------------------------+ mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.b[1]',99); +-----------------------------------------------------+ | JSON_REPLACE(@j, '$.a', 10, '$.b[1]',99') | +-----------------------------------------------------+ | {"a": 10, "b": [2, 99]} | +-----------------------------------------------------+7、JSON_SET操作JSON_SET操作為更新和插入操作,已經存在的去更新;不存在則插入,以下是JSON_SET、JSON_INSERT、JSON_REPLACE執行結果的區別:就是以上特性| Json Column和Generated Column(中)的內容,更多相關內容請關注PHP中文網(www.php.cn)!