![]() Now compare how difficult that is to: UPDATE content SET status = 1 WHERE name = 'Jack' Using this in an UPDATE looks like this: mysql> update example set content = json_set(content, concat(json_unquote(json_search(json_extract(content, '$.name'), 'one', 'Jack')), '.status'), '1') Now use it in a JSON_SET() call: mysql> select json_pretty(Ĭoncat(json_unquote(json_search(json_extract(content, '$.name'), 'one', 'Jack')), '.status'), status and you can get the full path to the field you want to update: mysql> select concat(json_unquote(json_search(json_extract(content, '$.name'), 'one', 'Jack')), '.status') as path from example Then you can search that array to get the array position: mysql> select json_search(json_extract(content, '$.name'), 'one', 'Jack') as root from example Here's a trick: You can extract the name field, and that turns into an array of those values: mysql> select json_extract(content, '$.name') as a from example ![]() mysql> select j.* from example cross join json_table(content, '$' columns( And that doesn't tell you the path to the element, it only allows you to return a specific row out of the array. ![]() To search for a key/value pair, you need to use JSON_TABLE() and that requires you upgrade to MySQL 8.0. mysql> select json_unquote(json_search(content, 'one', 'Jack')) as path from example If "Jack" occurs in some other field, that would also be found. You can get the path to a JSON element with JSON_SEARCH(), but you can only search by value, not by a key/value pair. ![]() The solution in that case depends on you knowing which array element your pseudo-record exists in. The question like yours has come up before on Stack Overflow, for example JSON update single value in MySQL table. mysql> select json_pretty(json_replace(content, '$.status', '1')) as j So in this case, we can see that the array element is $ but if you didn't know that, you couldn't use this solution. You can use JSON_REPLACE() or JSON_SET(), but both require that you know the path to the field you want to change. This is very awkward, nearly impossible with MySQL's JSON functions. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |