MySQL JSON datatype with Storing values in generated columns

This blog is the continuation of previous blog “MySQL JSON datatype with generated virtual columns and indexing” and put up information how to use STORED keyword when creating virtual column from JSON datatype elements.

Using the STORED keyword when set up a created column is normally not favored since you're mostly storing values two times in a table: once in the JSON document and yet again in the STORED column. However, there are three situations when MySQL suggests you use a STORED created column:

1. Indexing primary keys,

2. Needed a full text/R-tree index

3. A column that is scanned a lot.

The syntax for adding a STORED created column is the same as making VIRTUAL created columns, except we add the keyword STORED after the look like:

To demonstration you how to use STORED, let's create additional table that will take the IDs from our JSON documents and store them in a STORED created column. Then we will set the PRIMARY KEY to the id column. At the similar time, we will generate all of our VIRTUAL columns and set up indexes for those columns. This is done completely within the original table creation:

Following, we will insert the same dataset into players_two but we will eliminate the ID that we before added to the INSERT action:

After our data has been inserted into the table, we can run SHOW COLUMNS on the new table to see how MySQL has created our columns. Notice that the id field now has STORED GENERATED alongside it with a PRIMARY KEY index.

One item to note about making a PRIMARY KEY with created columns is that MySQL will not allow you to generate primary keys on VIRTUAL created columns. In fact, if we left out STORED on the id field, MySQL will throw the subsequent error:

At the same time, if you authorization off the primary key index and effort to insert a data, MySQL will throw an error asserting:

This means that you don't have a PK (primary key) on your table. Therefore, you must go back and moreover drop and reconstruct your table, or drop the id column and add the column as a STORED created column with a PRIMARY KEY like:

Conclusion

By means of created columns will allow you to put indexes on detailed elements of your JSON documents without indexing them in their whole, and offer you with the flexibility of also storing or not storing values within a table.