SQL Server
SQL Server
JSON Paths
See more JSON Examples
Demonstrates using "Chilkat JSON Paths" to access parts of a JSON document, or to iterate over parts.This example uses the following JSON document:
{
"nestedArray" : [
[
[1,2,3],
[4,5,6],
[7,8,9,10]
],
[
[11,12,13],
[14,15,16],
[17,18,19,20]
],
[
[21,22,23],
[24,25,26],
[27,28,29,30],
[31,32,33,34,35,36]
]
],
"nestedObject" : {
"aaa" : {
"bb1" : {
"cc1" : "c1Value",
"cc2" : "c2Value",
"cc3" : "c3Value"
},
"bb2" : {
"dd1" : "d1Value",
"dd2" : "d2Value",
"dd3" : "d3Value"
}
}
},
"mixture" : {
"arrayA" : [
{ "fruit": "apple", "animal": "horse", "job": "fireman", "colors": ["red","blue","green"] },
{ "fruit": "pear", "animal": "plankton", "job": "waiter", "colors": ["yellow","orange","purple"] },
{ "fruit": "kiwi", "animal": "echidna", "job": "astronaut", "colors": ["magenta","tan","pink"] }
]
},
"name.with.dots" : { "grain" : "oats" }
}
Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OASetProperty @json, 'EmitCompact', 0
-- Assume the file contains the data as shown above..
EXEC sp_OAMethod @json, 'LoadFile', @success OUT, 'qa_data/json/pathSample.json'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @json, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @json
RETURN
END
-- First, let's get the value of "cc1"
-- The path to this value is: nestedObject.aaa.bb1.cc1
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'nestedObject.aaa.bb1.cc1'
PRINT @sTmp0
-- Now let's get number 18 from the nestedArray.
-- It is located at nestedArray[1][2][1]
-- (remember: Indexing is 0-based)
EXEC sp_OAMethod @json, 'IntOf', @iTmp0 OUT, 'nestedArray[1][2][1]'
PRINT 'This should be 18: ' + @iTmp0
-- We can do the same thing in a more roundabout way using the
-- I, J, and K properties. (The I,J,K properties will be convenient
-- for iterating over arrays, as we'll see later.)
EXEC sp_OASetProperty @json, 'I', 1
EXEC sp_OASetProperty @json, 'J', 2
EXEC sp_OASetProperty @json, 'K', 1
EXEC sp_OAMethod @json, 'IntOf', @iTmp0 OUT, 'nestedArray[i][j][k]'
PRINT 'This should be 18: ' + @iTmp0
-- Let's iterate over the array containing the numbers 17, 18, 19, 20.
-- First, use the SizeOfArray method to get the array size:
DECLARE @sz int
EXEC sp_OAMethod @json, 'SizeOfArray', @sz OUT, 'nestedArray[1][2]'
-- The size should be 4.
PRINT 'size of array = ' + @sz + ' (should equal 4)'
-- Now iterate...
DECLARE @i int
SELECT @i = 0
WHILE @i <= @sz - 1
BEGIN
EXEC sp_OASetProperty @json, 'I', @i
EXEC sp_OAMethod @json, 'IntOf', @iTmp0 OUT, 'nestedArray[1][2][i]'
PRINT @iTmp0
SELECT @i = @i + 1
END
-- Let's use a triple-nested loop to iterate over the nestedArray:
DECLARE @j int
DECLARE @k int
-- szI should equal 1.
DECLARE @szI int
EXEC sp_OAMethod @json, 'SizeOfArray', @szI OUT, 'nestedArray'
SELECT @i = 0
WHILE @i <= @szI - 1
BEGIN
EXEC sp_OASetProperty @json, 'I', @i
DECLARE @szJ int
EXEC sp_OAMethod @json, 'SizeOfArray', @szJ OUT, 'nestedArray[i]'
SELECT @j = 0
WHILE @j <= @szJ - 1
BEGIN
EXEC sp_OASetProperty @json, 'J', @j
DECLARE @szK int
EXEC sp_OAMethod @json, 'SizeOfArray', @szK OUT, 'nestedArray[i][j]'
SELECT @k = 0
WHILE @k <= @szK - 1
BEGIN
EXEC sp_OASetProperty @json, 'K', @k
EXEC sp_OAMethod @json, 'IntOf', @iTmp0 OUT, 'nestedArray[i][j][k]'
PRINT @iTmp0
SELECT @k = @k + 1
END
SELECT @j = @j + 1
END
SELECT @i = @i + 1
END
-- Now let's examine how to navigate to JSON objects contained within JSON arrays.
-- This line of code gets the value "kiwi" contained within "mixture"
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'mixture.arrayA[2].fruit'
PRINT @sTmp0
-- This line of code gets the color "yellow"
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'mixture.arrayA[1].colors[0]'
PRINT @sTmp0
-- Getting an object at a path:
-- This gets the 2nd object in "arrayA"
DECLARE @obj2 int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @obj2 OUT
EXEC sp_OAMethod @json, 'ObjectOf2', @success OUT, 'mixture.arrayA[1]', @obj2
-- This object's "animal" should be "plankton"
EXEC sp_OAMethod @obj2, 'StringOf', @sTmp0 OUT, 'animal'
PRINT @sTmp0
-- Note that paths are relative to the object, not the absolute root of the JSON document.
-- Starting from obj2, "purple" is at "colors[2]"
EXEC sp_OAMethod @obj2, 'StringOf', @sTmp0 OUT, 'colors[2]'
PRINT @sTmp0
-- Getting an array at a path:
-- This gets the array containing the colors red, green, blue:
DECLARE @arr1 int
EXEC @hr = sp_OACreate 'Chilkat.JsonArray', @arr1 OUT
EXEC sp_OAMethod @json, 'ArrayOf2', @success OUT, 'mixture.arrayA[0].colors', @arr1
DECLARE @szArr1 int
EXEC sp_OAGetProperty @arr1, 'Size', @szArr1 OUT
SELECT @i = 0
WHILE @i <= @szArr1 - 1
BEGIN
EXEC sp_OAMethod @arr1, 'StringAt', @sTmp0 OUT, @i
PRINT @i + ': ' + @sTmp0
SELECT @i = @i + 1
END
-- The Chilkat JSON path uses ".", "[", and "]" chars for separators. When a name
-- contains one of these chars, use double-quotes in the path:
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, '"name.with.dots".grain'
PRINT @sTmp0
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @obj2
EXEC @hr = sp_OADestroy @arr1
END
GO