Recently I have been working on a migration of data from an old application to a brand spanking new Symfony 7 application. This migration included getting rid of the old Couchbase document database and moving to MySQL. MySQL has for quite some time already had a special JSON field type that is actually pretty cool. And using Doctrine, you don’t even notice it’s JSON, because there are automatic transformations to a PHP array structure.

I was looking for the right way to set indexes on specific fields inside the JSON structure for easy querying, and as I was doing so I learned about MySQL generated fields. This is pretty cool stuff, as it will automatically create virtual fields in your table with the data as it exists inside the JSON. And those fields can be part of an index!

Step 1 is obviously creating the column. So let’s add a column to a table called articles:

ALTER TABLE articles 
ADD sales_price INT AS (price_info->"$.salesprice"),
ADD cost_price INT AS (price_info->"$.costprice");

In this example, the price_info is the JSON field. Inside the JSON structure, it has a field with key salesprice and a field with key costprice, and I here add those as seperate columns sales_price and cost_price into the table articles.

Above query I can now add to a Doctrine migration.

Now I also need to specify to Doctrine in the entity that the fields exist. But they are special fields, because they are read-only (you update the value by updating the JSON). So you need to configure that for your entity. For instance:

/** * @Column(type="json") */
protected $priceinfo;

/**
 * @Column(type="int", nullable=true, insertable=false, updatable=false)
 */
protected $sales_price;

/** * @Column(type="int", nullable=true, insertable=false, updatable=false) */
protected $cost_price;

Notice the fact that I set insertable and updatable to false. Also important: I set nullable to true. Why? Because in a JSON field, there is no guarantee that a value is there. If a value is missing, MySQL will set it to NULL. If your field is not nullable, it will fail on writing the record to the database.

Now, if you create a new Article and set JSON that contain the costprice and salesprice fields, as soon as you persist it in the database, the values of $cost_price and $sales_price are automatically populated based on the values in the JSON.

Since they are now regular properties of your entity, you can also use them in the where clauses of your queries. They are, for all intents and purposes, simply regular fields in your table. The only thing you can not do is set the value on your entity and persist it, and expect it to stick. If you need to change the value, you need to update the JSON.

I just wanted to share this because I think this is extremely cool and useful stuff. As for a practical example: One use case I know of at least is to store data structures from external systems in exactly the structure you receive it, and let MySQL sort everything out for you. Especially when you may also need that structure to communicate back to the external system, it is good to keep the structure the way you received it.