(SQL Server) Insert JSON Object into another JSON Object

Demonstrates how to insert one JSON object into another. Effectively, the JSON object must be copied into the other..

Note: This example requires Chilkat v11.0.0 or greater.

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)
    -- Imagine we have two separate JSON objects.
    DECLARE @jsonA int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonA OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @jsonA, 'UpdateString', @success OUT, 'animal', 'zebra'
    EXEC sp_OAMethod @jsonA, 'UpdateString', @success OUT, 'colors[0]', 'white'
    EXEC sp_OAMethod @jsonA, 'UpdateString', @success OUT, 'colors[1]', 'black'

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

    -- jsonA contains:

    -- {
    --   "animal": "zebra",
    --   "colors": [
    --     "white",
    --     "black"
    --   ]
    -- }

    DECLARE @jsonB int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonB OUT

    EXEC sp_OAMethod @jsonB, 'UpdateString', @success OUT, 'type', 'mammal'
    EXEC sp_OAMethod @jsonB, 'UpdateBool', @success OUT, 'carnivore', 0

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

    -- jsonB contains:

    -- {
    --   "type": "mammal",
    --   "carnivore": false
    -- }

    -- Let's say we want to insert jsonB into jsonA to get this:

    -- {
    --   "animal": "zebra",
    --   "info" " {
    --       "type": "mammal",
    --       "carnivore": false
    -- 	},
    --   "colors": [
    --     "white",
    --     "black"
    --   ]
    -- }

    EXEC sp_OAMethod @jsonA, 'AddObjectCopyAt', @success OUT, 1, 'info', @jsonB

    EXEC sp_OAMethod @jsonA, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- The result is this:

    -- {
    --   "animal": "zebra",
    --   "info": {
    --     "type": "mammal",
    --     "carnivore": false
    --   },
    --   "colors": [
    --     "white",
    --     "black"
    --   ]
    -- }

    EXEC @hr = sp_OADestroy @jsonA
    EXEC @hr = sp_OADestroy @jsonB


END
GO