MySQL JSON_ARRAY_APPEND() Function

Summary: in this tutorial, you will learn how to use the MySQL JSON_ARRAY_APPEND() function to add an element to a JSON array within a JSON document.

Introduction to the MySQL JSON_ARRAY_APPEND() function

The JSON_ARRAY_APPEND() function adds an element to a JSON array in a JSON document.

Here’s the syntax of the JSON_ARRAY_APPEND() function:

JSON_ARRAY_APPEND(json_doc, path, value1, value2, ...)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• json_doc: This is the JSON document that contains the array to which you want to append elements.
• path: The path to the array within the JSON document.
• value1, value2, ...: The values you want to append to the JSON array.

This JSON_ARRAY_APPEND() function is useful when you want to update JSON data stored in tables.

MySQL JSON_ARRAY_APPEND() function examples

Let’s take some examples of using the JSON_ARRAY_APPEND() function.

1) Adding elements to a JSON array

The following example uses the JSON_ARRAY_APPEND() function to add elements to a JSON array:

SELECT JSON_ARRAY_APPEND('["red","green"]', '$', 'blue');Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------------------------------------+
| JSON_ARRAY_APPEND('["red","green"]', '$', 'blue') |
+---------------------------------------------------+
| ["red", "green", "blue"]                          |
+---------------------------------------------------+
1 row in set (0.00 sec)Code language: plaintext (plaintext)

In this example, we append the value ‘blue’ to the JSON array ["red", "green"] that results in the ["red", "green", "blue"]

2) Adding elements to nested a JSON array

The following example uses the JSON_ARRAY_APPEND() function to add elements to nested JSON arrays:

SELECT 
  JSON_ARRAY_APPEND(
    '{ "colors": ["red", "green"], "fonts": ["serif"] }', 
    '$.colors', 'blue'
  );Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------------------------------------------+
| json_doc                                                |
+---------------------------------------------------------+
| {"fonts": ["serif"], "colors": ["red", "green", "blue"]}|
+---------------------------------------------------------+
1 row in set (0.00 sec)Code language: plaintext (plaintext)

In this example, the JSON document has two arrays: colors and fonts.

The JSON_ARRAY_APPEND() function appends the 'blue' string to the colors array by using the path '$.colors'.

3) Adding an element to a JSON array in a table

First, create a new table called decorations:

CREATE TABLE decorations(
   id INT AUTO_INCREMENT PRIMARY KEY,
   data JSON
);Code language: SQL (Structured Query Language) (sql)

Next, insert a new JSON document into the decorations table:

INSERT INTO decorations(data)
VALUES('{ "colors": ["red", "green"], "fonts": ["serif"] }');Code language: SQL (Structured Query Language) (sql)

Then, query data from the decorations table:

SELECT * FROM decorations
WHERE id = 1;Code language: SQL (Structured Query Language) (sql)

Output:

+----+--------------------------------------------------+
| id | data                                             |
+----+--------------------------------------------------+
|  1 | {"fonts": ["serif"], "colors": ["red", "green"]} |
+----+--------------------------------------------------+
1 row in set (0.01 sec)Code language: plaintext (plaintext)

After that, add an element ('georgia') to the fonts array using the JSON_ARRAY_APPEND() function:

UPDATE decorations
SET data = JSON_ARRAY_APPEND(data, '$.fonts', 'georgia')
WHERE id = 1Code language: SQL (Structured Query Language) (sql)

Finally, query data from the decorations table:

SELECT * FROM decorations
WHERE id = 1;Code language: SQL (Structured Query Language) (sql)

Output:

+----+-------------------------------------------------------------+
| id | data                                                        |
+----+-------------------------------------------------------------+
|  1 | {"fonts": ["serif", "georgia"], "colors": ["red", "green"]} |
+----+-------------------------------------------------------------+
1 row in set (0.00 sec)Code language: plaintext (plaintext)

Summary

• Use the MySQL JSON_ARRAY_APPEND() function to append an element to a JSON array within a JSON document.