Using generated columns to query and index JSON data

If you find yourself constantly extracting the same key from a JSON document in SQL mode, then it might be time to create a so-called generated column. The generated column looks like a normal column, but it has the data from whatever function you provide, usually an extract and unquote on a JSON document. The data for the generated column can either be virtual (generated on the go) or stored. The benefit of a fully virtual column is that adding or removing it is instantaneous, and it doesn't take up any storage space. With a generated column, the benefit is that it can be faster because it doesn't have to be generated every time it is used.

Take the worldcol collection as an example:

ALTER TABLE worldcol
ADD COLUMN district VARCHAR(255) AS (doc->>'$.district') NOT NULL;

This extracts the district from the JSON document and places it in a generated column:

Figure 13.45 –...