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:

JSON_SET(col, '$.field', "first value")
-- {"field": "first value"}

and then:

JSON_ARRAY_APPEND(col, '$.field', "second value")
-- {"field": ["first value", "second value"]}

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.

Solution

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

SELECT JSON_INSERT('{"foo": "bar"}', '$.new', JSON_ARRAY('one', 'two', 'three'))
/*
{
    "foo": "bar",
    "new": ["one", "two", "three"]
}
*/

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:

SELECT JSON_ARRAY_APPEND(JSON_INSERT('{"foo": "bar"}', '$.new', JSON_ARRAY()), '$.new', 'four')
/*
{
    "foo": "bar",
    "new": ["four"]
}
*/
SELECT JSON_ARRAY_APPEND(JSON_INSERT('{"foo": "bar", "new": ["one", "two", "three"]}', '$.new', JSON_ARRAY()), '$.new', 'four')
/*
{
    "foo": "bar",
    "new": ["one", "two", "three", "four"]
}
*/