[FIXED] How to use JSON_ARRAY_APPEND when path does not exist in JSON doc?

Issue

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

Solution

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]}') 

Demo

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.

Answered By - Barbaros Özhan

Answer Checked By - Senaida (PHPFixing Volunteer)