In this article, we will see how to read Object, Properties and Array values of a JSON document.
Here is a simple JSON Document
{ "name": "John", "skills": "SQL" }
The above example has a simple object with two properties(name and skills). These two properties are separated by a comma.
Now read the above JSON from T-SQL using OPENJSON
GO DECLARE @jsonInfo NVARCHAR(MAX) SET @jsonInfo=N' { "name": "John", "skills": "SQL" } ' SELECT * FROM OPENJSON(@jsonInfo)
Adding an object as a property type
{ "CoName": "DBPROX.COM", "Release": "David Willington", "Post": { "Category": "CTO", "Tags": ["IT-INFRA", "Solution Engineering & Delivery"] } }
Now read the above JSON from T-SQL using OPENJSON
DECLARE @jsonInfo NVARCHAR(MAX) SET @jsonInfo=N' { "CoName": "DBPROX.COM", "Release": "David Willington", "Post": { "Category": "CTO", "Tags": ["IT-INFRA", "Solution Engineering & Delivery"] } } ' SELECT * FROM OPENJSON(@jsonInfo)
In the above JSON document, You see that I have added a property named Post, its an Object having two properties Category and Tags (Tags property is an array).
Let see how to read the values of Post object properties using [, path]
After the optional path mode declaration, specify the path itself.
• The dollar sign ($) represents the context item.
• The property path is a set of path steps. Path steps can contain the following elements and operators.
o Key names. For example, $.name and $.”first name”. If the key name starts with a dollar sign or contains special characters such as spaces, surround it with quotes.
o Array elements. For example, $. product[3]. Arrays are zero-based.
o The dot operator (.) indicates a member of an object. For example, in $. people[1].surname, surname is a child of people.
DECLARE @jsonInfo NVARCHAR(MAX) SET @jsonInfo=N' { "CoName": "DBPROX.COM", "Release": "David Willington", "Post": { "Category": "CTO", "Tags": ["IT-INFRA", "Solution Engineering & Delivery"] } } ' SELECT * FROM OPENJSON(@jsonInfo, '$') SELECT * FROM OPENJSON(@jsonInfo, '$.Post') SELECT * FROM OPENJSON(@jsonInfo, '$.Post.Tags')
Reading JSON Document with OPENJSON with with_clause
with_clause contains a list of columns with their types for OPENJSON to return. By default, OPENJSONmatches keys in jsonExpression with the column names in with_clause (in this case, matches keys implies that it is case sensitive). If a column name does not match a key name, you can provide an optional column_path, which is a JSON Path Expression that references a key within the jsonExpression.
Sample JSON Document
DECLARE @jsonInfo NVARCHAR(MAX) SET @jsonInfo=N' { "CoName": "DBPROX.COM", "Release": "David Willington", "Post": { "Category": "CTO", "Tags": ["IT-INFRA", "Solution Engineering & Delivery"] } } ' SELECT CoName,Release FROM OPENJSON(@jsonInfo) WITH ( CoName varchar(max) '$.CoName', Release varchar(max) '$.Release' )
Reading Array value with with_clause & JSON_VALUE
GO DECLARE @jsonInfo NVARCHAR(MAX) SET @jsonInfo=N' { "CoName": "DBPROX.COM", "Release": "David Willington", "Post": { "Category": "CTO", "Tags": ["IT-INFRA", "Solution Engineering & Delivery"] } } ' SELECT CoName,Release,Post_Category,Post_Tags_0,Post_Tags_1 FROM OPENJSON(@jsonInfo) WITH ( CoName varchar(max) '$.CoName', Release varchar(max) '$.Release', Post_Category varchar(max) '$.Post.Category', Post_Tags_0 varchar(max) '$.Post.Tags[0]', Post_Tags_1 varchar(max) '$.Post.Tags[1]' )
Reading Array value with JSON_VALUE
GO DECLARE @jsonInfo NVARCHAR(MAX) SET @jsonInfo=N' { "CoName": "DBPROX.COM", "Release": "David Willington", "Post": { "Category": "CTO", "Tags": ["IT-INFRA", "Solution Engineering & Delivery"] }, "Post1": { "Category1": "CTO", "Tags1": ["IT-INFRA", "Solution Engineering & Delivery"] } } ' SELECT CoName,Release,JSON_VALUE(Post,'$.Category') Category,JSON_VALUE(Post,'$.Tags[0]') Category FROM OPENJSON(@jsonInfo) WITH ( CoName varchar(max) '$.CoName', Release varchar(max) '$.Release', Post Nvarchar(max) AS JSON, Post1 Nvarchar(max) AS JSON )
Open JSON with Key
SELECT top 1000 json_data.* FROM OPENROWSET (BULK 'G:\Dummy_Data\world_bank\world_bank.json', SINGLE_CLOB) as j CROSS APPLY OPENJSON (j.BulkColumn,'$[0].majorsector_percent') json_data