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.
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.
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 this post I will talk about one annoying thing – how to return a result set containing one to many relationships between tables? In that case you will have multiple primary rows because one row is generated per for each child row. in this post we will see how you can resolve this problem using JSON.
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.
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 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.
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.