![]() In this post, I will show you some queries that can be executed in DocumentDB and equivalent Sql Server queries. Sql Server provides built-in functions for accessing JSON fields (JSON_VALUE), fragments (JSON_QUERY) and opening JSON documents (OPENJSON). Being a database designed for JSON and JavaScript, DocumentDB uses JavaScript as its underlying type system and supports both SQL as well as JavaScript queries.īoth SQL Server 2016 and DocumentDB enable you to query JSON documents.DocumentDB has nice syntax for querying JSON documents – you can find some good examples on JSON can be used with all components/features in SQL Server such as In-memory OLTP, Column store, temporal, etc.ĭocumentDB is a write optimized and schema agnostic document database purpose built for JSON and JavaScript it does not require any schema or secondary indices in order to serve queries. JSON support in SQL Server uses standard T-SQL syntax for querying JSON data with some additional built-in functions that enables you to read values of objects from JSON data. SQL Server 2016/Azure SQL Db are relational databases that will have support for handling JSON data. In this article I will show you similarities and differences between SQl Server JSON and DocumentDB query syntax. The code for this exercise is available on GitHub.SQL Server JSON query syntax compared to DocumentDB Equipped with these techniques, we can handle almost anything users throw at us and are still able to query data fast enough. Public DbSet WishlistEntries ", "Connected Home & Housewares").ToList() Įven though our application is far from complete we are in a good place. We’ve created a small EF Core project where DBContext looks something like this: class SHDbContext : DbContext ![]() From here on we’ll be exploring ways to query the data Test Bench Moving on to coding, let us focus on data retrieval and skip the API part for now – it’s a whole different can or worms and will distract us from our point. We can save heaps of time creating one simple data model and calling it a day. ![]() SQL Server has functions to work with JSON data – all we have to do is put it into a sufficiently big column. We could just lump it all together and chuck it into a schema-less data store like Cosmos DB or Mongo, but for the sake of argument we’ll imagine the client was adamant they needed a SQL Server because it’s a business decision they made a long time ago. And, probably, we should not care – perfect data model for frequently changing e-commerce websites would be hard to deduce and likely very painful to maintain. The catch is, we don’t know full list of attributes. That API will have to make sense of all different attributes each website provides and try store it in a way that makes querying as easy and efficient as possible. We may have a browser extension of some sort that would send information about an item we’re currently watching to an API. ![]() Our journey would likely start with a web browser. One realistic case study where this situation may pop up would be building a browser extension that helps capture and organise wish lists from various platforms such as Amazon or eBay. Now we need to bridge the gap and efficiently query it. We have seen a few projects where the client insists on using SQL Server, but their workload generates a bunch of semi-structured JSON data for us to store.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |