Imaging the existing JSON doc:
{
"first": "data",
"second": [1,2,3]
}
When I try to execute:
JSON_ARRAY_APPEND(doc,'$.third',4)
I expect mysql to create the parameter as an empty array and add my element into that array resulting in:
{
"first": "data",
"second": [1,2,3],
"third": [4]
}
This however is not the case. I am trying to do this in an UPDATE query to add data into the db using something similar to:
UPDATE mytable
SET myjson=JSON_ARRAY_APPEND(myjson,'$.third',4)
WHERE ...
I am using mysql 8.0.16 if that makes any difference. I am not getting any errors, just 0 row(s) affected
Your JSON
is not an array, so rather than JSON_ARRAY_APPEND()
, you can consider using JSON_MERGE_PATCH()
function if the order of the keys do not matter :
UPDATE mytable
SET myjson = JSON_MERGE_PATCH(myjson, '{"third": [4]}')
According to Normalization principle ; To make lookups more efficient, MySQL also sorts the keys of a JSON object. You should be aware that the result of this ordering is subject to change and not guaranteed to be consistent across releases.