JSON is one of the data formats that you may have to work with in SQL Server. Data elements contained in this format are delimited by square brackets and curly braces. Unlike structured data that is in tables (rows and columns), certain applications prefer to send data to the server in unstructured format, such as XML or JSON. We use OPENJSON function (reminiscent of OPENXML) to parse JSON data and turn it into rows and columns. When extracting data using OPENJSON, we specify the full path to the element. We often have to call OPENJSON recursively using CROSS APPLY because each data element may in turn contain data in JSON format.
Have a look at the following example:
DECLARE @plants NVARCHAR(MAX) =
'[{
"Plants":
[{"Fruit":
[{
"Apples":"Golden Delicious",
"Figs":"Brown Turkey",
"Peaches":"Sweet GA",
"PrimaryHealthBenefit":"Lower Blood Pressure",
"Callories":
[{
"Large": "100-150",
"Medium": "50-100",
"Small": "20-50"
}]
}]
}]
}]
}]'
SELECT
apples,
figs,
peaches,
[primary benefit],
[calories in large],
[calories in medium] ,
[calories in small]
FROM OPENJSON (@plants)
WITH (
plants NVARCHAR(MAX) '$.Plants' AS JSON
) AS plants
CROSS APPLY OPENJSON (plants, '$')
WITH (
fruit NVARCHAR(MAX) '$.Fruit' AS JSON
) AS fruit
CROSS APPLY OPENJSON(fruit, '$')
WITH (
Apples VARCHAR(200) '$.Apples',
Figs VARCHAR(200) '$.Figs',
Peaches VARCHAR(200) '$.Peaches',
[Primary Benefit] VARCHAR(200) '$.PrimaryHealthBenefit',
Calories NVARCHAR(MAX) '$.Callories' AS JSON
) AS Calories
CROSS APPLY OPENJSON (Calories, '$')
WITH (
[calories in large] VARCHAR(200) '$.Large',
[calories in medium] VARCHAR(200) '$.Medium',
[calories in small] VARCHAR(200) '$.Small')
In this example we parse three JSON constructs: plants, fruit and calories. The word “callories” is misspelled on purpose. The main point is that each JSON construct is called recursively with CROSS APPLY, whereas the elements are identified by passing the path, such as ‘$.Large’ for the calorie count in a large fruit. The query results in a single record that shows each element of the JSON string in separate columns.
Note that JSON constructs are always declared as NVARCHAR(MAX). Much like XML, JSON is case-sensitive, so if you don’t see the result you expect go back and make sure you used the correct case for each element.
BDBA Executives 