(SQL Server) JSON Paths

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 ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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 @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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..
    DECLARE @success int
    EXEC sp_OAMethod @json, 'LoadFile', @success OUT, 'qa_data/json/pathSample.json'
    IF @success <> 1
      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 sp_OAMethod @json, 'ObjectOf', @obj2 OUT, 'mixture.arrayA[1]'
    -- 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

    EXEC @hr = sp_OADestroy @obj2

    -- Getting an array at a path:
    -- This gets the array containing the colors red, green, blue:
    DECLARE @arr1 int
    EXEC sp_OAMethod @json, 'ArrayOf', @arr1 OUT, 'mixture.arrayA[0].colors'
    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
    EXEC @hr = sp_OADestroy @arr1

    -- 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


END
GO