SQL Server

Get the Index of a JSON Member

See more JSON Examples

This example demonstrates how to get the index of a given member by name.

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

SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr 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

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

    EXEC sp_OAMethod @json, 'Load', @success OUT, @jsonStr
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @json, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    -- The top-level JSON object has three members: name, image, and thumbnail.
    DECLARE @nameIndex int
    EXEC sp_OAMethod @json, 'IndexOf', @nameIndex OUT, 'name'
    -- The index of the "name" member is 0.

    PRINT 'nameIndex = ' + @nameIndex

    DECLARE @thumbIndex int
    EXEC sp_OAMethod @json, 'IndexOf', @thumbIndex OUT, 'thumbnail'
    -- The index of the "thumbnail" member is 2.

    PRINT 'thumbIndex = ' + @thumbIndex

    -- The "fname" member is NOT a direct member of the top-level JSON object.
    -- It is a member of a nested object.  If we try to get the index of this
    -- member using the top-level JSON object, it is not found (and returns -1).
    DECLARE @fnameIndex int
    EXEC sp_OAMethod @json, 'IndexOf', @fnameIndex OUT, 'fname'
    -- The fnameIndex is -1 (not found).  This is correct.

    PRINT 'fnameIndex = ' + @fnameIndex

    -- Get the "image" object.
    DECLARE @imageObj int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @imageObj OUT

    EXEC sp_OAMethod @json, 'ObjectOf2', @success OUT, 'image', @imageObj

    -- Now we can get the index of the "fname" object, because it is a direct
    -- member of the "image" object:
    EXEC sp_OAMethod @imageObj, 'IndexOf', @fnameIndex OUT, 'fname'

    PRINT 'fnameIndex = ' + @fnameIndex

    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @imageObj


END
GO