(SQL Server) Iterate JSON where Member Names are Data Values

Demonstrates how to parse JSON where member names are not keywords, but instead are data values.

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

    DECLARE @success int
    EXEC sp_OAMethod @json, 'LoadFile', @success OUT, 'qa_data/json/valuesAsNames.json'

    -- Imagine we have JSON such as the following:

    -- {
    --   "1680": {
    --     "entity_id": "1680",
    --     "type_id": "simple",
    --     "sku": "123"
    --   },
    --   "1701": {
    --     "entity_id": "1701",
    --     "type_id": "simple",
    --     "sku": "456"
    --   }
    -- }
    -- 

    -- This presents a parsing problem because the member names, such as "1680"
    -- are not keywords.  Instead they are data values.  We don't know what they
    -- may be in advance.  

    -- To solve, we iterate over the members, get the name of each, ...
    DECLARE @numMembers int
    EXEC sp_OAGetProperty @json, 'Size', @numMembers OUT
    DECLARE @i int

    SELECT @i = 0
    WHILE @i <= @numMembers - 1
      BEGIN

        DECLARE @name nvarchar(4000)
        EXEC sp_OAMethod @json, 'NameAt', @name OUT, @i


        PRINT @name + ':'
        DECLARE @jRecord int
        EXEC sp_OAMethod @json, 'ObjectAt', @jRecord OUT, @i


        EXEC sp_OAMethod @jRecord, 'StringOf', @sTmp0 OUT, 'entity_id'
        PRINT 'entity_id: ' + @sTmp0

        EXEC sp_OAMethod @jRecord, 'StringOf', @sTmp0 OUT, 'type_id'
        PRINT 'type_id: ' + @sTmp0

        EXEC sp_OAMethod @jRecord, 'StringOf', @sTmp0 OUT, 'sku'
        PRINT 'sku: ' + @sTmp0

        EXEC @hr = sp_OADestroy @jRecord

        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @json


END
GO