Making use of the JSON_REMOVE() function in MySQL 8

Introduction

In the evolving world of web development, storing and manipulating JSON data efficiently has become increasingly crucial. MySQL 8 introduces a range of functions that work with JSON data types, and one of the handiest among these is the JSON_REMOVE() function. This function allows developers to modify JSON documents by removing elements from them without altering the original data structure.

In this tutorial, we’ll guide you through practical examples of how to use the JSON_REMOVE() function in MySQL 8, explaining its syntax, and showcasing scenarios where it can be invaluable. We’ll also discuss best practices for working with JSON data in MySQL.

Understanding JSON_REMOVE()

Before diving into examples, it’s important to understand what JSON_REMOVE() does. Essentially, the function takes a JSON document and an array of one or more paths to elements that should be removed. It then returns a new JSON document with those elements omitted.

The syntax for JSON_REMOVE() is as follows:

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

Where json_doc is the JSON document and path specifies the location of the element to be removed within the JSON document. Paths use the MySQL JSON path language, starting with a dollar sign (‘$’) and designating keys in objects or indexes in arrays.

Using JSON_REMOVE()

Let’s start with a simple JSON document:

{
  "gadgets": [
    {"name": "smartphone", "price": 299},
    {"name": "laptop", "price": 899},
    {"name": "tablet", "price": 199}
  ]
}

Example 1: Removing an Element

If we want to remove the tablet from our list of gadgets, we can write a query like this:

SET @json_doc = '{
  "gadgets": [
    {"name": "smartphone", "price": 299},
    {"name": "laptop", "price": 899},
    {"name": "tablet", "price": 199}
  ]
}';

SELECT JSON_REMOVE(@json_doc, '$.gadgets[2]');

This will output a new JSON document:

{
  "gadgets": [
    {"name": "smartphone", "price": 299},
    {"name": "laptop", "price": 899}
  ]
}

Note that the original document remains unchanged, while the tablet entry is excluded from the resulting document.

Example 2: Removing Multiple Elements

Imagine you want to remove both the smartphone and the laptop from the document. Your query would look like this:

SELECT JSON_REMOVE(@json_doc, '$.gadgets[0]', '$.gadgets[1]');

The resulting JSON document will be:

{
  "gadgets": [
    {"name": "tablet", "price": 199}
  ]
}

Example 3: Dealing with Nested Objects

Consider a more complex document:

{
  "store": {
    "book": [
      { "category": "reference",
        "author": "Nigel Rees",
        "title": "Sayings of the Century",
        "price": 8.95
      },
      { "category": "fiction",
        "author": "Evelyn Waugh",
        "title": "Sword of Honour",
        "price": 12.99
      },
      { "category": "fiction",
        "author": "Herman Melville",
        "title": "Moby Dick",
        "isbn": "0-553-21311-3",
        "price": 8.99
      },
      { "category": "fiction",
        "author": "J. R. R. Tolkien",
        "title": "The Lord of the Rings",
        "isbn": "0-395-19395-8",
        "price": 22.99
      }
    ],
    "bicycle": {
      "color": "red",
      "price": 19.95
    }
  }
}

To remove the entire bicycle entry from the store, we would write:

SET @complex_json = '{
  "store": {
    "book": [
      { "category": "reference",
        "author": "Nigel Rees",
        "title": "Sayings of the Century",
        "price": 8.95
      },
      { "category": "fiction",
        "author": "Evelyn Waugh",
        "title": "Sword of Honour",
        "price": 12.99
      },
      { "category": "fiction",
        "author": "Herman Melville",
        "title": "Moby Dick",
        "isbn": "0-553-21311-3",
        "price": 8.99
      },
      { "category": "fiction",
        "author": "J. R. R. Tolkien",
        "title": "The Lord of the Rings",
        "isbn": "0-395-19395-8",
        "price": 22.99
      }
    ],
    "bicycle": {
      "color": "red",
      "price": 19.95
    }
  }
}';

SELECT JSON_REMOVE(@complex_json, '$.store.bicycle');

This would return a document with only the book section under store.

Considerations When Using JSON_REMOVE()

• When removing elements from a JSON array, the elements are not renumbered — use JSON_UNQUOTE() and JSON_COMPACT() after removal if necessary.
• Elements from an JSON object that do not exist are silently ignored, no error is thrown.
• Operations in MySQL on JSON documents are, by their nature, more computationally intensive compared to standard SQL operations.
• The function is immutable, meaning it returns a new JSON document rather than altering the existing one.

Conclusion

JSON_REMOVE() is a powerful tool provided by MySQL 8 for manipulating JSON documents. By using it correctly, you can efficiently manage and mutate your JSON data stored in MySQL. It’s particularly useful in web development and applications that need to handle JSON natively.

Keep practicing with different JSON documents and structures to see how JSON_REMOVE() behaves. The more familiar you become with the JSON path expressions, the more adept you’ll be at querying and updating JSON in MySQL.