2

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

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
amaster
  • 1,915
  • 5
  • 25
  • 51
  • I was able to work around this by manually creating the `"third":[]` parameter on every item. I would like to know if this can be done without that step though for future reference. – amaster Dec 12 '19 at 17:52

1 Answers1

3

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.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • I will look more into this function, but I assume by saying preserve that is preserves any data that was already in the array if it existed. – amaster Dec 12 '19 at 20:06
  • @amaster well, you're right. we'd better considering `JSON_MERGE_PATCH()` instead then. – Barbaros Özhan Dec 12 '19 at 20:12
  • Thanks for the input. I actually want to use the `JSON_MERGE_PRESERVE` method though for my case, I wasn't very clear on that, sorry. I did find an article going into depth about the difference: https://database.guide/json_merge_patch-vs-json_merge_preserve-in-mysql-whats-the-difference/ – amaster Dec 12 '19 at 20:30