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