I have a test table in which there is a json field with a JSON data format.
In this field is an array [2, 3, 4, 5].
I want to remove C grade from an array. And successfully do it with the following query:

UPDATE test SET json = JSON_REMOVE(json, '$[1]') WHERE `id` = 2;

But what if I know that there is an element in the array with a value of 3, but I do not know its index?
Trying to execute the following query:

 UPDATE test SET json = JSON_REMOVE( json, '$[ JSON_SEARCH(json, 'one', 3) ]' ) WHERE `id` = 2;

I get a syntax error.

What am I doing wrong?

    1 answer 1

    What am I doing wrong?

    There are so many brackets and quotes that the query has lost its meaning; need to be easier:

     UPDATE test SET json = JSON_REMOVE(json, JSON_UNQUOTE(JSON_SEARCH(json, 'one', 3))) WHERE `id` = 2;

    But more formally, JSON_SEARCH () returns the path JSON_SEARCH () , so no additional processing of the value is necessary before passing it to JSON_REMOVE () .

    Remarks:

    • JSON_SEARCH () returns the response as a json document - a string in quotes, so you can use JSON_UNQUOTE to remove it.
    • In MySQL, JSON_SEARCH () cannot search for non-string values ​​— this is an obvious bug , although some believe that it simply needs to be documented; MariaDB does not have this problem.
    • Thanks for participating! Well, at least this request does not give an error. However, as a result, it sets the json field to null. Now I twist-turn requests, try variations. Let's try it together. (One kiloChert, in the documentation, the syntax for working with the json data type is described so vigorously that nothing can be understood ...) - muturgan
    • It seems to figure it out. Looks like it's a matter of casting. For the select query, JSON_SEARCH ('[\ "1 \", \ "2 \", 3]', 'one', 3) as 'index' I get an answer "index": null. For the select query JSON_SEARCH ('[\ "1 \", \ "2 \", \ "3 \"]', 'one', '3') as 'index' they answer "index": "\" $ [ 2] \ "". But for the query select JSON_SEARCH ('[\ "1 \", \ "2 \", \ "3 \"]', 'one', 3) as 'index' I also get an answer "index": "\" $ [ 2] \ "" !!! It looks like my next question is how to make the JSON_SEARCH function understand that the number is a number ... - muturgan
    • @muturgan, corrected / added the answer ... I think for mysql the only solution would be to implement the logic of working with JSON on the sql-client side ... or change the scheme - Fat-Zer
    • Thank you very much for clarifying with JSON_UNQUOTE, I somehow missed this point - muturgan
    • Regarding MariaDB and bugs - I certainly didn’t study this question on purpose, but I thought that since she had split off from a muscle, she was doomed to just always catch up with him. And it turns out like! - muturgan