(SQL Server) Understanding JSON Array vs JSON Object

This example explains the difference between a JSON Array and a JSON Object. A JSON Array begins with "[" and ends with "]", whereas a JSON Object begins with "{" and ends with "}".

Elements contained in a JSON array are accessed by index, whereas elements in a JSON object are typically accessed by name (but can also be accessed by index).

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
    -- A JSON array should be loaded into a Chilkat JSON array,
    -- whereas a JSON object should be loaded into a Chilkat JSON object.

    -- A JSON array may contain objects, and a JSON object may contains arrays, but
    -- it is the top-level (outermost) element that defines whether the JSON
    -- document is an array or an object.
    -- An array begins and ends with "[" ... "]"
    -- An object begins and ends with "{" ... "}"

    -- For example, an array containing 2 objects:
    DECLARE @strJsonArray nvarchar(4000)
    SELECT @strJsonArray = '[ { "name": "Bill" }, { "name": "Ted" } ]'

    -- Load it into a JSON array.
    DECLARE @jsonA int
    -- Use "Chilkat_9_5_0.JsonArray" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonArray', @jsonA OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @jsonA, 'Load', @success OUT, @strJsonArray

    EXEC sp_OAGetProperty @jsonA, 'Size', @iTmp0 OUT
    PRINT 'number of array elements: ' + @iTmp0

    -- This is an object containing an array:
    DECLARE @strJsonObj nvarchar(4000)
    SELECT @strJsonObj = '{ "characters": [ "Bill", "Ted" ] }'

    -- Load it into a JSON object.
    DECLARE @jsonO int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonO OUT

    EXEC sp_OAMethod @jsonO, 'Load', @success OUT, @strJsonObj

    EXEC sp_OAGetProperty @jsonO, 'Size', @iTmp0 OUT
    PRINT 'number of object members: ' + @iTmp0

    EXEC @hr = sp_OADestroy @jsonA
    EXEC @hr = sp_OADestroy @jsonO


END
GO