Currently you can find many JSON documents stored in files. Sensors generate information that are stored in files, applications log information in JSON files, etc. One important thing that you would need to do is to read JSON stored in files, load them in SQL Server, and analyze them. In this post we will see how you can import JSON files in SQL Server.
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 DocumentDb site . Sql Server provides built-in functions for accessing JSON fields (JSON_VALUE), fragments (JSON_QUERY) and opening JSON documents (OPENJSON). In this post, I will show you some queries that can be executed in DocumentDb and equivalent Sql Server queries.
OPENJSON function in Sql Server enables you to open complex JSON structures. In this post we will see how you can open GeoJSON format.
In Sql Server can be used standard indexes to speed-up queries that use values in JSON documents. In this post we will see how to use standard non-clustered index to improve performance of you queries on JSON data.
OPENJSON function that will be added in SQL Server 2016 is the easiest way to import JSON text into regular table. In this post we will see how you can transform JSON to table and then insert/update existing table in SQL Server.
JSON can be used to improve performance and reduce complexity in data load process if you serialize some entities as JSON collections. In this post we will see how you can use JSON columns in data load process.
SQL Server do not supports complex types such as arrays or lists. If you need to send some parametrized query or execute stored procedure, you will need to use primitive scalar types. Currently, it is hard to send some structured information to SQL server modules. New OPENJSON function can be used to parse array elements. If you need to send an array of element to SQL server it is better that dynamic SQL, and also simpler than table value functions.In this blog post you can see how to use this function. In this post you will see how you can use JSON arrays to send complex data to SQL Server.
SQL Server 2016 will include JSON support. First functionality (FOR JSON clause) is available in CTP2. Here you can see an overview of FOR JSON clause that is available in SQL Server 2016 CTP2.