JSON_DELETE_KEY
Removes a key/value pair from a JSON map or array.
Maps are in the form:
{“alpha”: 1, “beta”: 2, “gamma”:3}
JSON_DELETE_KEY(json, keypath)
Arguments
json: a JSON value.keypath: the path to the key to delete (comma-separated list of dictionary keys or zero-indexed array positions).
Return Value
The record without the key.
SQL NULL if JSON is not a valid JSON.
Examples
This example will remove the key/value indicated in the function (e.g., alpha).
SELECT JSON_DELETE_KEY('{"alpha": 1, "beta": 2, "gamma": 3}', 'alpha') AS del;
****
+----------------------+
| del |
+----------------------+
| {"beta":2,"gamma":3} |
+----------------------+
This example will have no change to the function unless the parameter indicated is a member of the function. Using alpha, beta, or gamma will remove it from the array. Using another parameter (e.g., zeta) will have no effect.
SELECT JSON_DELETE_KEY('{"alpha": 1, "beta": 2, "gamma": 3}', 'zeta') AS nochange;
****
+--------------------------------+
| nochange |
+--------------------------------+
| {"alpha":1,"beta":2,"gamma":3} |
+--------------------------------+
In this example a member of the array called, “alpha” is being removed. It is important to remember that arrays use a zero based numbering, so to delete the first item in an array by a numeric value you would you zero.
SELECT JSON_DELETE_KEY('{"alpha":[1,2,3]}','alpha', 1) AS array_delete;
****
+-----------------+
| array_delete |
+-----------------+
| {"alpha”:[1,3]} |
+-----------------+