When using JSON documents with MySQL, we can use the JSON_ARRAY_APPEND() function to append new values to an array.

The way it works is, you provide the JSON document as the first argument, then follow that up with the path to append to, followed by the value to append.

In MySQL 5.7, this function was called JSON_APPEND() but that name is no longer supported.

Syntax

The syntax goes like this:

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

Where:

• json_doc is the JSON document.
• path is the path of the element for which to append the new value to.
• val is the new value that will be appended.

The path-value pairs are evaluated left to right. The document produced by evaluating one pair becomes the new value against which the next pair is evaluated.

Example 1 – Basic Usage

Here’s an example to demonstrate:

SELECT JSON_ARRAY_APPEND('[1, 2, 3]', '$', 4) AS 'Result';

Result:

+--------------+
| Result       |
+--------------+
| [1, 2, 3, 4] |
+--------------+

So the 3rd argument is appended to the array. In this case, I use $ for the path. This is a synonym for the JSON document, so the value is appended to the top level array (which in this case, happens to be the only array).

Example 2 – Nested Array

Here’s an example of appending a value to an array that’s nested inside another array:

SELECT JSON_ARRAY_APPEND('[1, 2, [3, 4]]', '$[2]', 5) AS 'Result';

Result:

+-------------------+
| Result            |
+-------------------+
| [1, 2, [3, 4, 5]] |
+-------------------+

So here, I use $[2] as the path. This specifies the array at the third element (arrays use zero-based numbering, so the count starts at zero).

If there wasn’t already an array at that element, a new array is created.

Like this:

SELECT JSON_ARRAY_APPEND('[1, 2, 3]', '$[2]', 4) AS 'Result';

Result:

+----------------+
| Result         |
+----------------+
| [1, 2, [3, 4]] |
+----------------+

Example 3 – A Larger JSON Document

This example uses a (slightly) larger JSON document. This demonstrates how the path changes depending on where the array is within the document.

SET @data = '{  
    "Person": {    
       "Name": "Homer", 
       "Hobbies": ["Eating", "Sleeping"]  
    }
 }';
SELECT JSON_ARRAY_APPEND(@data, '$.Person.Hobbies', "Base Jumping") AS 'Result';

Result:

+----------------------------------------------------------------------------------+
| Result                                                                           |
+----------------------------------------------------------------------------------+
| {"Person": {"Name": "Homer", "Hobbies": ["Eating", "Sleeping", "Base Jumping"]}} |
+----------------------------------------------------------------------------------+

And just like in the previous example, we can also create a new, nested array, if required:

SET @data = '{  
    "Person": {    
       "Name": "Homer", 
       "Hobbies": ["Eating", "Sleeping"]  
    }
 }';
SELECT JSON_ARRAY_APPEND(@data, '$.Person.Hobbies[0]', "Drinking") AS 'Result';

Result:

+--------------------------------------------------------------------------------+
| Result                                                                         |
+--------------------------------------------------------------------------------+
| {"Person": {"Name": "Homer", "Hobbies": [["Eating", "Drinking"], "Sleeping"]}} |
+--------------------------------------------------------------------------------