(SQL Server) Create more Complex JSON Document

Sample code to create the following JSON document:

    {  
        "Title": "The Cuckoo's Calling",  
        "Author": "Robert Galbraith",  
        "Genre": "classic crime novel",  
        "Detail": {  
            "Publisher": "Little Brown",  
            "Publication_Year": 2013,  
            "ISBN-13": 9781408704004,  
            "Language": "English",  
            "Pages": 494  
        },  
        "Price": [  
            {  
                "type": "Hardcover",  
                "price": 16.65  
            },  
            {  
                "type": "Kindle Edition",  
                "price": 7.00  
            }  
        ]  
    }  

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

    -- The only reason for failure in the following lines of code would be an out-of-memory condition..

    -- An index value of -1 is used to append at the end.
    DECLARE @index int
    SELECT @index = -1

    EXEC sp_OAMethod @json, 'AddStringAt', @success OUT, -1, 'Title', 'The Cuckoo''s Calling'
    EXEC sp_OAMethod @json, 'AddStringAt', @success OUT, -1, 'Author', 'Robert Galbraith'
    EXEC sp_OAMethod @json, 'AddStringAt', @success OUT, -1, 'Genre', 'classic crime novel'

    -- Let's create the Detail JSON object:
    EXEC sp_OAMethod @json, 'AddObjectAt', @success OUT, -1, 'Detail'
    DECLARE @detail int
    EXEC sp_OAGetProperty @json, 'Size', @iTmp0 OUT
    EXEC sp_OAMethod @json, 'ObjectAt', @detail OUT, @iTmp0 - 1
    EXEC sp_OAMethod @detail, 'AddStringAt', @success OUT, -1, 'Publisher', 'Little Brown'
    EXEC sp_OAMethod @detail, 'AddIntAt', @success OUT, -1, 'Publication_Year', 2013
    EXEC sp_OAMethod @detail, 'AddNumberAt', @success OUT, -1, 'ISBN-13', '9781408704004'
    EXEC sp_OAMethod @detail, 'AddStringAt', @success OUT, -1, 'Language', 'English'
    EXEC sp_OAMethod @detail, 'AddIntAt', @success OUT, -1, 'Pages', 494
    EXEC @hr = sp_OADestroy @detail

    -- Add the array for Price
    EXEC sp_OAMethod @json, 'AddArrayAt', @success OUT, -1, 'Price'
    DECLARE @aPrice int
    EXEC sp_OAGetProperty @json, 'Size', @iTmp0 OUT
    EXEC sp_OAMethod @json, 'ArrayAt', @aPrice OUT, @iTmp0 - 1

    -- Entry entry in aPrice will be a JSON object.

    -- Append a new/empty ojbect to the end of the aPrice array.
    EXEC sp_OAMethod @aPrice, 'AddObjectAt', @success OUT, -1
    -- Get the object that was just appended.
    DECLARE @priceObj int
    EXEC sp_OAGetProperty @aPrice, 'Size', @iTmp0 OUT
    EXEC sp_OAMethod @aPrice, 'ObjectAt', @priceObj OUT, @iTmp0 - 1
    EXEC sp_OAMethod @priceObj, 'AddStringAt', @success OUT, -1, 'type', 'Hardcover'
    EXEC sp_OAMethod @priceObj, 'AddNumberAt', @success OUT, -1, 'price', '16.65'
    EXEC @hr = sp_OADestroy @priceObj

    EXEC sp_OAMethod @aPrice, 'AddObjectAt', @success OUT, -1
    EXEC sp_OAGetProperty @aPrice, 'Size', @iTmp0 OUT
    EXEC sp_OAMethod @aPrice, 'ObjectAt', @priceObj OUT, @iTmp0 - 1
    EXEC sp_OAMethod @priceObj, 'AddStringAt', @success OUT, -1, 'type', 'Kindle Edition'
    EXEC sp_OAMethod @priceObj, 'AddNumberAt', @success OUT, -1, 'price', '7.00'
    EXEC @hr = sp_OADestroy @priceObj

    EXEC @hr = sp_OADestroy @aPrice

    EXEC sp_OASetProperty @json, 'EmitCompact', 0
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @json


END
GO