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]} | +-----------------+