Using JSON fields with Doctrine ORM on PostgreSQL & MySQL
Working with JSON data types in the latest versions of PostgreSQL and MySQL is very similar to any other field. There's quite a bit of hidden work in making sure performance is good with JSON. MySQL for example validates the JSON document on insert and then converts it to a binary format for good performance.
For users working with the database level this complexity is hidden. Inserts to JSON fields obviously need to be validation, but on reading from the database JSON data blogs are naturally integrated to queries. Items in the JSON field is presented in virtual columns that are indistinguishable to the user.
The functionalities of PostgreSQL and JSON datatypes are well presented elsewhere and for in introductions I recommend watching these presentations on JSON in PostgreSQL 9.5 and JSON in MySQL 5.7. Access to these new features are available in the latest stable versions. PostgreSQL has JSON capabilities for a number of years and MySQL 5.7 since it's introduction in October 2015.
MySQL 5.7 is already the default version when you deploy the latest Ubuntu 16.04 LTS version. With PHP 7 support on board this version of Ubuntu is actually very capable for LAMP / LEMP application development. For MySQL compatible servers like Percona and they are on the way. For example the JSON features are a scheduled to be in MariaDB version 10.2.
Real life usage with Doctrine ORM and Symfony?
So getting a version of a compatible database is easy nowadays. And as for the driver layer there is nothing that needs to be changed for PHP 7. So it will be quite straightforward to get started with using these features on a project with handwritten SQL. But for many this is not enough.
The Doctrine ORM is not a part of the Symfony project, but because it is included in the Symfony Standard Edition distribution - it's used by a number of projects built on the framework. This also means that in addition to server level support, you'll need additional support from your toolkit.
For Doctrine ORM there are ongoing efforts to provide a JSON compatible field type for the popular project. The project, known as the Doctrine JSON ODM (Object-Document Mapper) allows leveraging the new JSON capabilities in modern relational database servers in a way that is identical to working with Doctrine Entities:
When the object will be hydrated, the JSON content of this column is transformed back to its original values, thanks again to the Symfony Serializer. All PHP objects and structures will be preserved (if you use Symfony >= 3.1, see the FAQ).
- https://github.com/dunglas/doctrine-json-odm
Currently the project only supports PostgresSQL, but there is already an active Pull Request that aims to add MySQL support. There is still some discussion on backwards compatibility breaks and so forth, but eventually Doctrine ORM users will be able to use JSON features natively.
With support seeping into high profile projects like Doctrine, it's likely that the use of hybrid storages will grow in popularity. Hopefully this field type will be eventually included in the main Doctrine project.
Update: Benjamin Beberlei did a talk on Doctrine and NoSQL at Symfony Catalonia 2016. I suggest you see the slides for more information: https://qafoo.com/resources/presentations/symfony_catalunya_2016/doctrine_orm_and_nosql.html