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

In MariaDB, JSON_ARRAY_INSERT()<\/code> is a built-in function that inserts a value into 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_INSERT(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 insert the value\/s, and value<\/code> is the value to insert.<\/p>\n\n\n\n
Example<\/h2>\n\n\n\n
Here’s an example to demonstrate.<\/p>\n\n\n\n
SELECT JSON_ARRAY_INSERT('[0, 1, 2]', '$[1]', 3);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-------------------------------------------+\n| JSON_ARRAY_INSERT('[0, 1, 2]', '$[1]', 3) |\n+-------------------------------------------+\n| [0, 3, 1, 2]                              |\n+-------------------------------------------+<\/pre>\n\n\n\n
In this case, the value 3<\/code> was inserted into the the array at position 1<\/code> . Arrays are zero based, so it becomes the second element in the array.<\/p>\n\n\n\n
Insert Multiple Values<\/h2>\n\n\n\n
You can insert multiple values within a single call to JSON_ARRAY_INSERT()<\/code>. <\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
SELECT JSON_ARRAY_INSERT('[0, 1, 2]', '$[0]', 4, '$[2]', 5);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------------------------------------------------------+\n| JSON_ARRAY_INSERT('[0, 1, 2]', '$[0]', 4, '$[2]', 5) |\n+------------------------------------------------------+\n| [4, 0, 5, 1, 2]                                      |\n+------------------------------------------------------+<\/pre>\n\n\n\n
Evaluation is performed from left to right. <\/p>\n\n\n\n
Here’s what happens if we switch the path<\/code>\/value<\/code> arguments around:<\/p>\n\n\n\n
SELECT JSON_ARRAY_INSERT('[0, 1, 2]', '$[2]', 5, '$[0]', 4);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------------------------------------------------------+\n| JSON_ARRAY_INSERT('[0, 1, 2]', '$[2]', 5, '$[0]', 4) |\n+------------------------------------------------------+\n| [4, 0, 1, 5, 2]                                      |\n+------------------------------------------------------+<\/pre>\n\n\n\n
Multiple Arrays<\/h2>\n\n\n\n
You can insert values into more than one array within the same call to JSON_ARRAY_INSERT()<\/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_INSERT(@json_doc, '$.a[1]', 4, '$.b[2]', 5);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------------------------------------------------------+\n| JSON_ARRAY_INSERT(@json_doc, '$.a[1]', 4, '$.b[2]', 5) |\n+--------------------------------------------------------+\n| {\"a\": [0, 4, 1], \"b\": [2, 3, 5]}                       |\n+--------------------------------------------------------+<\/pre>\n\n\n\n
Nested Arrays<\/h2>\n\n\n\n
Here’s an example of inserting a value into a nested array:<\/p>\n\n\n\n
SET @json_doc = '[0, 1, [2, 3]]';\n\nSELECT JSON_ARRAY_INSERT(@json_doc, '$[2][1]', 4);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------------------------------------------+\n| JSON_ARRAY_INSERT(@json_doc, '$[2][1]', 4) |\n+--------------------------------------------+\n| [0, 1, [2, 4, 3]]                          |\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\", \"Water\"]  \n    }\n }';\nSELECT JSON_DETAILED(\n        JSON_ARRAY_INSERT(\n            @json_doc, \n            '$.pet.diet[1]', \n            'Chicken')\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
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_INSERT(null, '$[1]', 4);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------------------------------------+\n| JSON_ARRAY_INSERT(null, '$[1]', 4) |\n+------------------------------------+\n| NULL                               |\n+------------------------------------+<\/pre>\n\n\n\n
The same applies to the path<\/code> argument:<\/p>\n\n\n\n
SELECT JSON_ARRAY_INSERT('[0, 1, 2, 3]', null, 4);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------------------------------------------+\n| JSON_ARRAY_INSERT('[0, 1, 2, 3]', null, 4) |\n+--------------------------------------------+\n| NULL                                       |\n+--------------------------------------------+<\/pre>\n\n\n\n
However, if the value<\/code> argument is NULL<\/code>, then NULL<\/code> is inserted into to the array:<\/p>\n\n\n\n
SELECT JSON_ARRAY_INSERT('[0, 1, 2]', '$[1]', null);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+----------------------------------------------+\n| JSON_ARRAY_INSERT('[0, 1, 2]', '$[1]', null) |\n+----------------------------------------------+\n| [0, null, 1, 2]                              |\n+----------------------------------------------+<\/pre>\n\n\n\n
You can also use JSON_ARRAY_APPEND()<\/a><\/code> to append values to an array.<\/p>\n","protected":false},"excerpt":{"rendered":"
In MariaDB, JSON_ARRAY_INSERT() is a built-in function that inserts a value into 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],"class_list":["post-18258","post","type-post","status-publish","format-standard","hentry","category-mariadb","tag-arrays"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18258","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=18258"}],"version-history":[{"count":11,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18258\/revisions"}],"predecessor-version":[{"id":18275,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18258\/revisions\/18275"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=18258"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=18258"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=18258"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}