Trying to tell my MariaDB database to update a json (longtext) column. It works fine if the path exists. It works fine if the path doesn't exist and the path is one-dimensional. It breaks if the path is multi-dimensional and doesn't exist or partially exists.
jsondata = {"firstname":"Bob","stats":{"height":"72","weight":"200"}}
UPDATE table SET jsondata = JSON_INSERT(jsondata, '$.lastname', 'Smith') - Works
UPDATE table SET jsondata = JSON_INSERT(jsondata, '$.stats.hair', 'Brown') - Works
UPDATE table SET jsondata = JSON_INSERT(jsondata, '$.stats.hair.color', 'Brown') - Doesn't work
UPDATE table SET jsondata = JSON_INSERT(jsondata, '$.car.make', 'Ford') - Doesn't work
Is there a command I'm unaware of that would create paths where there are none?
I tried creating a recursive function that exploded the path on the period and inserted the path into the json document level-by-level, but it fell apart.
EDIT: I'll send $20 Zelle/Venmo to whoever helps me figure this out.
CodePudding user response：
Use JSON_MERGE_PATCH() function.