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

Question

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

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
– amaster, Commented Dec 12, 2019 at 17:52

Barbaros Özhan · Accepted Answer · 2019-12-12 20:10:20Z

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.

edited Dec 12, 2019 at 20:10

answered Dec 12, 2019 at 19:58

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

amaster Over a year ago

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.

Barbaros Özhan Over a year ago

@amaster well, you're right. we'd better considering JSON_MERGE_PATCH() instead then.

amaster Over a year ago

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: database.guide/…

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related