(SQL Server) JSON: Nested Objects

Here we have a JSON object that contains nested JSON objects. This example demonstrates how to access the contents of the nested objects.

{
  "name": "donut",
  "image":
    {
    "fname": "donut.jpg",
    "w": 200,
    "h": 200
    },
  "thumbnail":
    {
    "fname": "donutThumb.jpg",
    "w": 32,
    "h": 32
    }
}

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
    DECLARE @iTmp1 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

    -- This is the above JSON with whitespace chars removed (SPACE, TAB, CR, and LF chars).
    -- The presence of whitespace chars for pretty-printing makes no difference to the Load
    -- method. 
    DECLARE @jsonStr nvarchar(4000)
    SELECT @jsonStr = '{"name": "donut","image":{"fname": "donut.jpg","w": 200,"h": 200},"thumbnail":{"fname": "donutThumb.jpg","w": 32,"h": 32}}'

    DECLARE @success int
    EXEC sp_OAMethod @json, 'Load', @success OUT, @jsonStr
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @json, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    -- Get the "image" object.
    DECLARE @imageObj int
    EXEC sp_OAMethod @json, 'ObjectOf', @imageObj OUT, 'image'
    EXEC sp_OAGetProperty @json, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN

        PRINT 'image object not found.'
        EXEC @hr = sp_OADestroy @json
        RETURN
      END


    EXEC sp_OAMethod @imageObj, 'StringOf', @sTmp0 OUT, 'fname'

    EXEC sp_OAMethod @imageObj, 'IntOf', @iTmp0 OUT, 'w'

    EXEC sp_OAMethod @imageObj, 'IntOf', @iTmp1 OUT, 'h'
    PRINT 'image: fname=' + @sTmp0 + ', width=' + @iTmp0 + ', height=' + @iTmp1

    EXEC @hr = sp_OADestroy @imageObj

    -- Get the "thumbnail" object.
    DECLARE @thumbObj int
    EXEC sp_OAMethod @json, 'ObjectOf', @thumbObj OUT, 'thumbnail'
    EXEC sp_OAGetProperty @json, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN

        PRINT 'thumbnail object not found.'
        EXEC @hr = sp_OADestroy @json
        RETURN
      END


    EXEC sp_OAMethod @thumbObj, 'StringOf', @sTmp0 OUT, 'fname'

    EXEC sp_OAMethod @thumbObj, 'IntOf', @iTmp0 OUT, 'w'

    EXEC sp_OAMethod @thumbObj, 'IntOf', @iTmp1 OUT, 'h'
    PRINT 'thumbnail: fname=' + @sTmp0 + ', width=' + @iTmp0 + ', height=' + @iTmp1

    EXEC @hr = sp_OADestroy @thumbObj


    EXEC @hr = sp_OADestroy @json


END
GO