Using JSON functions and operators to query JSON columns

For this, we use the MySQL client. You can also use MySQL Shell in SQL mode; just issue \sql after connecting with MySQL Shell.

There are many convenient functions to deal with JSON data when you are working with collections in SQL mode or tables that use JSON fields.

The first thing to do is extract and unquote fields. This is something we did in the previous chapter, so here is a quick reminder of it. The functions are JSON_EXTRACT() and JSON_UNQUOTE(). However, it is more convenient to use the operators that were created to do this – -> to extract and ->> to extract and unquote. You have to specify a JSON path expression to the extract function, which in its most basic form looks like $.name, to extract the name field.

Consider the following example:

SELECT doc->>'$.name' FROM worldcol LIMIT 5;

This produces the following output:

Figure 13.38 – The...