In MariaDB, JSON_REMOVE() is a built-in function that removes data from a JSON document and returns the result. 

Syntax

The syntax goes like this:

JSON_REMOVE(json_doc, path[, path] ...)

Example

Here’s an example to demonstrate.

SET @json = '{ "name" : "Wag", "type" : "Dog" }';

SELECT JSON_REMOVE(@json, '$.type');

Result:

+------------------------------+
| JSON_REMOVE(@json, '$.type') |
+------------------------------+
| {"name": "Wag"}              |
+------------------------------+

In this case we removed the data member type from the document. In other words, the type key and its associated value were removed.

Arrays

JSON_REMOVE() can be used to remove the whole array, or specific elements within the array.

To remove the whole array, simply use the key name:

SET @json = '{ "name" : "Wag", "scores" : [8, 7, 9] }';

SELECT JSON_REMOVE(@json, '$.scores');

Result:

+--------------------------------+
| JSON_REMOVE(@json, '$.scores') |
+--------------------------------+
| {"name": "Wag"}                |
+--------------------------------+

That removed the whole array from the document.

To remove an array element, specify the element’s index. Here’s an example of removing an array element from an array:

SET @json = '{ "name" : "Wag", "scores" : [8, 7, 9] }';

SELECT JSON_REMOVE(@json, '$.scores[1]');

Result:

+-----------------------------------+
| JSON_REMOVE(@json, '$.scores[1]') |
+-----------------------------------+
| {"name": "Wag", "scores": [8, 9]} |
+-----------------------------------+

In this case, the second array element was removed. Arrays are zero based, and so $.scores[1] refers to the second element in the array.

See below for how JSON_REMOVE() deals with removing multiple paths within the array.

Multiple Paths

When providing multiple paths, they’re evaluated from left to right. This means that the result from the earlier evaluation is used as the value for the next.

It pays to be especially mindful of this when removing elements from an array based on their index.

First, here’s an example that removes multiple paths from a document, based on their key:

SET @json = '
    { 
        "name" : "Wag", 
        "type" : "Dog",
        "weight" : 10
    }
';

SELECT JSON_REMOVE(@json, '$.type', '$.weight');

Result:

+------------------------------------------+
| JSON_REMOVE(@json, '$.type', '$.weight') |
+------------------------------------------+
| {"name": "Wag"}                          |
+------------------------------------------+

Each key/value pair was removed as expected.

In the next example we don’t remove the key/value pair. Instead, we remove multiple elements from an array:

SET @json = '{ "scores" : [ 0, 1, 2, 3, 4, 5 ] }';

SELECT 
    JSON_REMOVE(@json, '$.scores[2]', '$.scores[4]') AS a,
    JSON_REMOVE(@json, '$.scores[4]', '$.scores[2]') AS b;

Result:

+--------------------------+--------------------------+
| a                        | b                        |
+--------------------------+--------------------------+
| {"scores": [0, 1, 3, 4]} | {"scores": [0, 1, 3, 5]} |
+--------------------------+--------------------------+

In this case we called JSON_REMOVE() twice. Both specify the same array index to remove (2 and 4), but we swap the arguments around in the second call. In the first example, it goes 2 then 4 (in that order). In the second example, it’s 4 then 2.

This produced a different result for each call. As mentioned, multiple paths are evaluated from left to right, and so the order can affect the result.

Here’s another example that illustrates how the result can be quite different, depending on how many paths are specified, which ones, and in which order:

SET @json = '{ "scores" : [ 0, 1, 2, 3, 4, 5 ] }';

SELECT 
    JSON_REMOVE(@json, '$.scores[0]', '$.scores[1]', '$.scores[5]') AS a,
    JSON_REMOVE(@json, '$.scores[1]', '$.scores[5]', '$.scores[0]') AS b,
    JSON_REMOVE(@json, '$.scores[5]', '$.scores[0]', '$.scores[1]') AS c,
    JSON_REMOVE(@json, '$.scores[5]', '$.scores[1]', '$.scores[0]') AS d,
    JSON_REMOVE(@json, '$.scores[1]', '$.scores[0]', '$.scores[5]') AS e,
    JSON_REMOVE(@json, '$.scores[0]', '$.scores[5]', '$.scores[1]') AS f;

Result (using vertical output):

a: {"scores": [1, 3, 4, 5]}
b: {"scores": [2, 3, 4, 5]}
c: {"scores": [1, 3, 4]}
d: {"scores": [2, 3, 4]}
e: {"scores": [2, 3, 4, 5]}
f: {"scores": [1, 3, 4, 5]}

Null Arguments

If any argument is NULL, the result is NULL:

SELECT 
    JSON_REMOVE(null, '$.a') AS a,
    JSON_REMOVE('{"a":1}', null) AS b,
    JSON_REMOVE(null, null) AS c;

Result:

+------+------+------+
| a    | b    | c    |
+------+------+------+
| NULL | NULL | NULL |
+------+------+------+

Incorrect Parameter Count

Calling JSON_REMOVE() without an argument results in an error:

SELECT JSON_REMOVE();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_REMOVE'

It’s the same when not enough arguments are passed:

SELECT JSON_REMOVE('{"a":1}');

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_REMOVE'