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}

Syntax

JSON_DELETE_KEY(json, keypath)

Arguments

  • json: A JSON value.

  • keypath: A comma-separated list of dictionary keys or zero-indexed array positions that specify the path to the key to delete.

Return Value

  • The record without the key.

  • SQL NULL if input 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. Hence, to delete the first item in an array by a numeric value, specify zero as the index.

SELECT JSON_DELETE_KEY('{"alpha":[1,2,3]}','alpha', 1) AS array_delete;
+-----------------+
| array_delete    |
+-----------------+
| {"alpha”:[1,3]} |
+-----------------+

Last modified: August 1, 2024

Was this article helpful?