Like XML, JSON is an open standard storage format for data, metadata, parameters, or other unstructured or semi-structured data. Because of its heavy usage in applications today, it inevitably will make its way into databases where it will need to be stored, compressed, modified, searched, and retrieved.
Even though a relational database is not the ideal place to store and manage less structured data, application requirements can oftentimes override an “optimal” database design. There is a convenience in having JSON data close to related relational data and architecting its storage effectively from the start can save significant time and resources in the future.
Read on for plenty of examples and tips. Ideologically, I have no problem parsing JSON to load data into SQL Server. I have no real problem storing data in JSON if the calling application takes that JSON as-is and does not expect the database to modify or shred that JSON. I have no problem taking relational data and creating JSON structures to send out to calling applications. My problem comes when you store the data as JSON but then expect the database to manage data. Treat the JSON blob as atomic and we’re fine; otherwise, I want to make that data relational, as befits a relational database.
MongoDB bruh
but yes SQL is the Swiss-Army knife of data
Pretty soon there will be a native JSON data type and boom no more Cosmos
Because Cosmos has columnstore, right? no wait
Of course. MangoDB is web-scale.
You’re not wrong about a native JSON type in SQL Server–it’s in Azure SQL DB (https://learn.microsoft.com/en-us/sql/t-sql/data-types/json-data-type?view=azuresqldb-current), though my understanding is limited regarding its utility, given there are no JSON indexes (like XML indexes) and I don’t think the JSON data type will result in faster operations.
But Cosmos DB will never go away–it’s core part of helping cloud solution architects meet their ACR quotas, after all!