While a specialized JSON database like MongoDB may suit some applications, it takes some skill to implement and administer it correctly; and there are a lot of reported issues with regard to data loss, lack of ACID-compliance, poor response times, security holes and so on.
PostgreSQL have perhaps made the strongest bid to be considered a truly “polyglot” database. Version 9.3 introduced much stronger support for JSON. It offers a native JSON type, and a raft of new functions and operators that make querying the JSON data easier and faster. It, like MongoDB, stores the data in binary JSON format (JSONB), a format that distills down the text structure to the absolute minimum required to represent the data. Once parsed into this format, it can be indexed, and processing and querying of the data from that point is very fast.
Of course, Microsoft has also introduced native JSON support into SQL Server 2016, though initially made a rather half-hearted job of it. There was no native JSON datatype; it continued to use the
NVARCHAR type, and no indexing. It was nowhere near as comprehensive as the XML support, and if you’re a developer who always found working with XML data in SQL Server slow and laborious anyway, then the JSON offering was underwhelming
However, with the recent announcement of the public preview of new JSON functionality in Azure SQL Database, they seem to have acted quickly to improve the situation. This is perhaps not surprising, given the recent announcement of support for SQL Server on Linux, which makes SQL Server a much closer competitor of PostgresSQL.
So why is JSON so important, when we know from bitter experience that XML didn’t prove to be important for most people? Many will argue that we learned from XML that data documents have no place in the client-server relationship.
What’s changed? JSON is simpler, more compact and works fine for a rudimentary ‘object-persistence’ model. Now that relational databases are beginning to have the ability to parse JSON documents very efficiently, it makes more sense to delegate responsibility to the database interface for mapping the object model to the relational model. The database can then grow and change without demanding any change from the application in the way that it passes the data, and avoid a close dependency between two components of the application that should never have become close-coupled in the first place.