(SQL Server) JSON: Array of Objects

Here we have a JSON object that contains an array, where each element in the array is a JSON object. This example demonstrates how to access the objects contained within an array.

{ 
  "employees":[
    {"firstName":"John", "lastName":"Doe"},
    {"firstName":"Anna", "lastName":"Smith"},
    {"firstName":"Peter","lastName":"Jones"}
  ]
}

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 @sTmp1 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 = '{"employees":[{"firstName":"John", "lastName":"Doe"},{"firstName":"Anna", "lastName":"Smith"},{"firstName":"Peter","lastName":"Jones"}]}'

    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 "employees" array.
    DECLARE @employees int
    EXEC sp_OAMethod @json, 'ArrayOf', @employees OUT, 'employees'
    EXEC sp_OAGetProperty @json, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN

        PRINT 'employees member not found.'
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    -- Iterate over each employee, getting the JSON object at each index.
    DECLARE @numEmployees int
    EXEC sp_OAGetProperty @employees, 'Size', @numEmployees OUT
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @numEmployees
      BEGIN

        DECLARE @empObj int
        EXEC sp_OAMethod @employees, 'ObjectAt', @empObj OUT, @i



        EXEC sp_OAMethod @empObj, 'StringOf', @sTmp0 OUT, 'firstName'

        EXEC sp_OAMethod @empObj, 'StringOf', @sTmp1 OUT, 'lastName'
        PRINT 'employee[' + @i + '] = ' + @sTmp0 + ' ' + @sTmp1
        EXEC @hr = sp_OADestroy @empObj

        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @employees


    EXEC @hr = sp_OADestroy @json


END
GO