How the JSON_OBJECT_FILTER_KEYS() function works in Mariadb?

The JSON_OBJECT_FILTER_KEYS() function is a built-in function in Mariadb that allows you to filter a JSON object by keeping certain keys. It can be useful when you want to extract or exclude some data from a JSON object.

Syntax

The syntax of the JSON_OBJECT_FILTER_KEYS() function is as follows:

JSON_OBJECT_FILTER_KEYS(json_doc, array_keys)

The function takes three or more arguments, as follows:

• json_doc: This is a mandatory argument that specifies the JSON object to filter. It must be a valid JSON object or a column that contains JSON objects.
• array_keys: This is an array that contains keys to keeping.

The function returns a new JSON object that is filtered according to the specified keys.

Example: Filtering a JSON object by keeping certain keys

In this example, we use the JSON_OBJECT_FILTER_KEYS() function to filter a JSON object by keeping only the keys name and gender.

SELECT JSON_OBJECT_FILTER_KEYS(
  '{"name": "Alice", "age": 25, "gender": "female", "occupation": "teacher"}',
  JSON_ARRAY("name", "gender")
) AS filtered_object;

The output is:

+---------------------------------------+
| filtered_object                       |
+---------------------------------------+
| {"name": "Alice", "gender": "female"} |
+---------------------------------------+

As you can see, the function returns a JSON object that only contains the keys name and gender, and removes the rest.

Conclusion

The JSON_OBJECT_FILTER_KEYS() function is a useful function in Mariadb that allows you to filter a JSON object by keeping certain keys.