Comments - JSON_NORMALIZE
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
The additional example above demonstrates a way to do this.
Thanks for adding the example - but unless I'm wrong, the data is still stored unnormalized, correct? And the normalize function is run each time there's a SELECT jnorm? That seems like quite a hit when dealing with large JSON documents (the column is LONGTEXT after all).
My hope was to be able to normalize the data between INSERT and actually writing to disk, to remove whitespace and help optimize any functions that run on it.
Of course, this is easily done on a front end, but if there's already a good JSON_NORMALIZE in MDB, why not let it be used for that purpose.
The use of `VIRTUAL` in the example means that you are correct. If you use `PERSISTENT` (or equivalently, `STORED`) instead, then the `jnorm` column will actually be stored in the db, but so too will the `val` column, doubling the storage used. Currently, having `BEFORE INSERT` and `BEFORE UPDATE` triggers with `SET NEW.val = JSON_NORMALIZE(NEW.val)` is the only way to do what we want.
Thanks for confirming, I added an issue to Jira for being able to do this without a trigger. Chime in if you'd use it https://jira.mariadb.org/browse/MDEV-27651