{"id":18244,"date":"2021-07-02T07:19:58","date_gmt":"2021-07-01T21:19:58","guid":{"rendered":"https:\/\/database.guide\/?p=18244"},"modified":"2024-04-28T11:48:42","modified_gmt":"2024-04-28T01:48:42","slug":"mariadb-json_array_append-explained","status":"publish","type":"post","link":"https:\/\/database.guide\/mariadb-json_array_append-explained\/","title":{"rendered":"MariaDB JSON_ARRAY_APPEND() Explained"},"content":{"rendered":"\n

In MariaDB, JSON_ARRAY_APPEND()<\/code> is a built-in function that appends values to the end of the specified array\/s within a JSON document, and returns the result. <\/p>\n\n\n\n\n\n\n\n

Syntax<\/h2>\n\n\n\n

The syntax goes like this:<\/p>\n\n\n\n

JSON_ARRAY_APPEND(json_doc, path, value[, path, value] ...)<\/code><\/pre>\n\n\n\n
Where json_doc<\/code> is the JSON document, path<\/code> is the path to where you want to append the value\/s, and value<\/code> is the value  to append.<\/p>\n\n\n\n
Example<\/h2>\n\n\n\n
Here’s an example to demonstrate the function.<\/p>\n\n\n\n
SET @json_doc = '[0, 1, 2, 3]';\n\nSELECT JSON_ARRAY_APPEND(@json_doc, '$', 4);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------------------------------------+\n| JSON_ARRAY_APPEND(@json_doc, '$', 4) |\n+--------------------------------------+\n| [0, 1, 2, 3, 4]                      |\n+--------------------------------------+<\/pre>\n\n\n\n
In this case, the value 4<\/code> was appended to the end of the array.<\/p>\n\n\n\n
Append Multiple Values<\/h2>\n\n\n\n
You can append multiple values within a single call to JSON_ARRAY_APPEND()<\/code>. <\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
SET @json_doc = '[0, 1, 2, 3]';\n\nSELECT JSON_ARRAY_APPEND(@json_doc, '$', 4, '$', 5);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+----------------------------------------------+\n| JSON_ARRAY_APPEND(@json_doc, '$', 4, '$', 5) |\n+----------------------------------------------+\n| [0, 1, 2, 3, 4, 5]                           |\n+----------------------------------------------+<\/pre>\n\n\n\n
Multiple Arrays<\/h2>\n\n\n\n
You can append values to more than one array within the same call to JSON_ARRAY_APPEND()<\/code>.<\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
SET @json_doc = '{\"a\": [0, 1], \"b\": [2, 3]}';\n\nSELECT JSON_ARRAY_APPEND(@json_doc, '$.a', 4, '$.b', 5);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------------------------------------------------+\n| JSON_ARRAY_APPEND(@json_doc, '$.a', 4, '$.b', 5) |\n+--------------------------------------------------+\n| {\"a\": [0, 1, 4], \"b\": [2, 3, 5]}                 |\n+--------------------------------------------------+<\/pre>\n\n\n\n
Nested Arrays<\/h2>\n\n\n\n
Here’s an example of appending a value to a nested array:<\/p>\n\n\n\n
SET @json_doc = '[0, 1, [2, 3]]';\n\nSELECT JSON_ARRAY_APPEND(@json_doc, '$[2]', 4);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------------------------------------+\n| JSON_ARRAY_APPEND(@json_doc, '$[2]', 4) |\n+-----------------------------------------+\n| [0, 1, [2, 3, 4]]                       |\n+-----------------------------------------+<\/pre>\n\n\n\n
And in the following example, the original JSON document doesn’t contain a nested array, but JSON_ARRAY_APPEND()<\/code> creates a nested array based on our path:<\/p>\n\n\n\n
SET @json_doc = '[0, 1, 2, 3]';\n\nSELECT JSON_ARRAY_APPEND(@json_doc, '$[3]', 4);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------------------------------------+\n| JSON_ARRAY_APPEND(@json_doc, '$[3]', 4) |\n+-----------------------------------------+\n| [0, 1, 2, [3, 4]]                       |\n+-----------------------------------------+<\/pre>\n\n\n\n
Larger JSON Document<\/h2>\n\n\n\n
Here’s an example with a slightly larger JSON document. <\/p>\n\n\n\n
I also use JSON_DETAILED()<\/code> to prettify the result:<\/p>\n\n\n\n
SET @json_doc = '{  \n    \"pet\": {    \n       \"name\": \"Fluffy\", \n       \"diet\": [\"Fish\", \"Chicken\"]  \n    }\n }';\nSELECT JSON_DETAILED(\n        JSON_ARRAY_APPEND(\n            @json_doc, \n            '$.pet.diet', \n            'Water')\n);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
{\n    \"pet\": \n    {\n        \"name\": \"Fluffy\",\n        \"diet\": \n        [\n            \"Fish\",\n            \"Chicken\",\n            \"Water\"\n        ]\n    }\n}<\/pre>\n\n\n\n
And here’s one that creates a nested array:<\/p>\n\n\n\n
SET @json_doc = '{  \n    \"pet\": {    \n       \"name\": \"Scratch\", \n       \"diet\": [\"Beef\", \"Water\"]\n    }\n }';\nSELECT JSON_DETAILED(\n        JSON_ARRAY_APPEND(\n            @json_doc, \n            '$.pet.diet[1]', \n            'Beer')\n);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
{\n    \"pet\": \n    {\n        \"name\": \"Scratch\",\n        \"diet\": \n        [\n            \"Beef\",\n            \n            [\n                \"Water\",\n                \"Beer\"\n            ]\n        ]\n    }\n}<\/pre>\n\n\n\n
Null Arguments<\/h2>\n\n\n\n
If the first argument is NULL<\/code>, the result is NULL<\/code>:<\/p>\n\n\n\n
SELECT JSON_ARRAY_APPEND(null, '$', 4);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+---------------------------------+\n| JSON_ARRAY_APPEND(null, '$', 4) |\n+---------------------------------+\n| NULL                            |\n+---------------------------------+<\/pre>\n\n\n\n
The same applies to the path<\/code> argument:<\/p>\n\n\n\n
SET @json_doc = '[0, 1, 2, 3]';\n\nSELECT JSON_ARRAY_APPEND(@json_doc, null, 4);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+---------------------------------------+\n| JSON_ARRAY_APPEND(@json_doc, null, 4) |\n+---------------------------------------+\n| NULL                                  |\n+---------------------------------------+<\/pre>\n\n\n\n
However, if the value<\/code> argument is NULL<\/code>, then NULL<\/code> is appended to the array:<\/p>\n\n\n\n
SET @json_doc = '[0, 1, 2, 3]';\n\nSELECT JSON_ARRAY_APPEND(@json_doc, '$', null);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------------------------------------+\n| JSON_ARRAY_APPEND(@json_doc, '$', null) |\n+-----------------------------------------+\n| [0, 1, 2, 3, null]                      |\n+-----------------------------------------+<\/pre>\n\n\n\n
You can also use JSON_ARRAY_INSERT()<\/a><\/code> to insert values into an array.<\/p>\n","protected":false},"excerpt":{"rendered":"
In MariaDB, JSON_ARRAY_APPEND() is a built-in function that appends values to the end of the specified array\/s within a JSON document, and returns the result.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[117],"tags":[169,93,80,20],"class_list":["post-18244","post","type-post","status-publish","format-standard","hentry","category-mariadb","tag-arrays","tag-functions","tag-json","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18244","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=18244"}],"version-history":[{"count":16,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18244\/revisions"}],"predecessor-version":[{"id":18274,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18244\/revisions\/18274"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=18244"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=18244"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=18244"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}