Skip to content
Advertisement

How to insert an array value to an existing JSON document?

Given a column containing a JSON document, I can use JSON_SET() to set a key in the JSON document to a value. Supported values are null, true, false, numbers and strings; but I can’t figure out how to set an array.

The example in the documentation (for JSON_INSERT(), but it works the same) weirdly shows how a naive user might try and fail to set an array value, but kind of lampshades it; if you look closely you will find that the array was converted to a string.

I figured out a workaround where you first use:

and then:

But there are all kinds of problems with that, not the least of it is that it can’t be used to set an array with less than 2 values.

Advertisement

Answer

JSON_INSERT/JSON_REPLACE/JSON_SET will happily accept a JSON document as the value:

Note that:

  • JSON_SET() replaces existing values and adds nonexisting values.
  • JSON_INSERT() inserts values without replacing existing values.
  • JSON_REPLACE() replaces only existing values.

If you want to append values to an array that may/may not already exist then chain the functions in this order:

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement