Important

Helios features are now enabled during weekly update windows and are no longer directly tied to SingleStore engine releases. Refer to the release notes to view the latest features available in your Helios cluster.

JSON_DELETE_KEY

The JSON_DELETE_KEY() function 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:

Was this article helpful?