The JSON Functions for using MySQL 5.7 are overwhelming at first glance. A peek at documentation such as the
JSON Modification Functions may make you shy at proceeding further. In previous posts the functions to create JSON documents for use in a MySQL column and the functions to get the meta information about JSON documents have been covered. And how to use the built-in JSON functions from PHP have been covered. But now it is time to cover modifying JSON data.
Appending
Lets start at the beginning and ask the server for a JSON array with the values of 1, 2, 3, 4, and 5.
mysql> select JSON_ARRAY(1,2,3,4,5);
+-----------------------+
| JSON_ARRAY(1,2,3,4,5) |
+-----------------------+
| [1, 2, 3, 4, 5] |
+-----------------------+
1 row in set (0.00 sec)
mysql>
Using the
$ operator to represent our array, we can append an array with the numbers 6, 7, and 8 in it to our array.
mysql> SELECT JSON_ARRAY_APPEND(JSON_ARRAY(1,2,3,4,5),'$',JSON_ARRAY(6,7,8));
+----------------------------------------------------------------+
| JSON_ARRAY_APPEND(JSON_ARRAY(1,2,3,4,5),'$',JSON_ARRAY(6,7,8)) |
+----------------------------------------------------------------+
| [1, 2, 3, 4, 5, [6, 7, 8]] |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
Or change values.
mysql> select JSON_ARRAY_APPEND(JSON_ARRAY(1,2,3,4,5),'$[2]',99);
+----------------------------------------------------+
| JSON_ARRAY_APPEND(JSON_ARRAY(1,2,3,4,5),'$[2]',99) |
+----------------------------------------------------+
| [1, 2, [3, 99], 4, 5] |
+----------------------------------------------------+
1 row in set (0.00 sec)
mysql>
Inserting an Array
But what if we need an array inside an array? We can modify arrays by inserting new arrays in them where we want. In the first example we add 9 & 10 at the beginning of an array,
$[0], and the second the new array in the third position,
$[2]. Remember array counting starts at zero!
mysql> SELECT JSON_ARRAY_INSERT(JSON_ARRAY(1,2,3,4,5),'$[0]',JSON_ARRAY(9,10));
+------------------------------------------------------------------+
| JSON_ARRAY_INSERT(JSON_ARRAY(1,2,3,4,5),'$[0]',JSON_ARRAY(9,10)) |
+------------------------------------------------------------------+
| [[9, 10], 1, 2, 3, 4, 5] |
+------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_ARRAY_INSERT(JSON_ARRAY(1,2,3,4,5),'$[2]',JSON_ARRAY(9,10));
+------------------------------------------------------------------+
| JSON_ARRAY_INSERT(JSON_ARRAY(1,2,3,4,5),'$[2]',JSON_ARRAY(9,10)) |
+------------------------------------------------------------------+
| [1, 2, [9, 10], 3, 4, 5] |
+------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
Merging Arrays
We can also put two arrays together with
JSON_MERGE.
mysql> SELECT JSON_MERGE(JSON_ARRAY(1,2,3,4,5),JSON_ARRAY(6,7,8,9));
+-------------------------------------------------------+
| JSON_MERGE(JSON_ARRAY(1,2,3,4,5),JSON_ARRAY(6,7,8,9)) |
+-------------------------------------------------------+
| [1, 2, 3, 4, 5, 6, 7, 8, 9] |
+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
Or Combine arrays with objects.
mysql> SELECT JSON_MERGE(JSON_ARRAY(1,2,3,4,5),JSON_OBJECT('name','Dave'));
+--------------------------------------------------------------+
| JSON_MERGE(JSON_ARRAY(1,2,3,4,5),JSON_OBJECT('name','Dave')) |
+--------------------------------------------------------------+
| [1, 2, 3, 4, 5, {"name": "Dave"}] |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
Removing/Replacing JSON
Sometimes you need to remove or replace items as well as add them. Here we remove the first item,
$[0].
mysql> SELECT JSON_REMOVE(JSON_ARRAY(1,2,3,4,5),'$[0]');
+-------------------------------------------+
| JSON_REMOVE(JSON_ARRAY(1,2,3,4,5),'$[0]') |
+-------------------------------------------+
| [2, 3, 4, 5] |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql>
The
JSON_REPLACE function has an argument for the new value. Here we change the first item in the array from 1 to 999.
mysql> SELECT JSON_REPLACE(JSON_ARRAY(1,2,3,4,5),'$[0]',999);
+------------------------------------------------+
| JSON_REPLACE(JSON_ARRAY(1,2,3,4,5),'$[0]',999) |
+------------------------------------------------+
| [999, 2, 3, 4, 5] |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql>
Game, SET , and Match
The
JSON_SET function can insert OR update values. Previously exiting items are updated, so we could use
JSON_SET like
JSON_REPLACE.
mysql> SELECT JSON_SET(JSON_ARRAY(1,2,3,4,5),'$[0]',999)\g
+--------------------------------------------+
| JSON_SET(JSON_ARRAY(1,2,3,4,5),'$[0]',999) |
+--------------------------------------------+
| [999, 2, 3, 4, 5] |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_SET(JSON_ARRAY(1,2,3,4,5),'$[2]',999)\g
+--------------------------------------------+
| JSON_SET(JSON_ARRAY(1,2,3,4,5),'$[2]',999) |
+--------------------------------------------+
| [1, 2, 999, 4, 5] |
+--------------------------------------------+
1 row in set (0.00 sec)
Or use it like
JSON_ARRAY_APPEND. Here using the not existing index of $[7] we append the value 999 to the array.
mysql> SELECT JSON_SET(JSON_ARRAY(1,2,3,4,5),'$[7]',999)\g
+--------------------------------------------+
| JSON_SET(JSON_ARRAY(1,2,3,4,5),'$[7]',999) |
+--------------------------------------------+
| [1, 2, 3, 4, 5, 999] |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql>
JSON_SET,
JSON_INSERT, and
JSON_REPLACE are very closely related. And all allow you to do lots of work with one call. So lets change the values for the first and third values in the array and add a new value at the end.
mysql> SELECT JSON_SET(JSON_ARRAY(1,2,3,4,5),'$[7]',999,'$[0]',888,'$[2]',777)\g
+------------------------------------------------------------------+
| JSON_SET(JSON_ARRAY(1,2,3,4,5),'$[7]',999,'$[0]',888,'$[2]',777) |
+------------------------------------------------------------------+
| [888, 2, 777, 4, 5, 999] |
+------------------------------------------------------------------+
1 row in set (0.00 sec)
So read this entry and then go to the
JSON Mofication Documentation and be less shy.
Next time -- Back to JSON coding with PHP
