SQL Server

Iterate JSON where Member Names are Data Values

See more JSON Examples

Demonstrates how to parse JSON where member names are not keywords, but instead are data values. 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

    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