JSON_DELETE_KEY

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