JSON is the most commonly used data format now days. It is lighter than XML, more readable and it's syntax complies with most OOP C-Like languages such as Java, JavaScript and C#.
Microsoft SQL server does not use JSON for any data structure and by nature does not understand it. Starting from SQL Server 2016t, Microsoft introduced JSON support by adding OPENJSON function.
OPENJSON function basically transforms JSON document into collection of row-sets, in simple words output of the function id table representation of JSON data.
If you are getting OPENJSON issue in SQL server then follow this article where we discussed how to resolve this issue https://www.coreprogramm.com/2021/11/how-to-fix-invalid-object-name-openjson-in-sql-server.html.
Let's Create a simple Json file using SQL like below,
Simple OPENJSON call without parameters will produce the output with key/value pair table with additional column of data type of the data read from JSON document.
If you want to format table to get properties as columns which is most useful way to use table for joining with other data tables, you can explicitly convert tot he type and column name.
Summary
In this tutorial we discussed Reading JSON data in T-SQL on SQL Server. If have any question related to this topic then give your feedback.
Post a Comment