(SQL Server) Insert JSON Array into another JSON Object

See more JSON Examples

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
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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, 'ciphertext', 'encryptedData'
    EXEC sp_OAMethod @jsonA, 'UpdateInt', @success OUT, 'status', 200
    EXEC sp_OAMethod @jsonA, 'UpdateString', @success OUT, 'error', 'errorMsg'

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

    -- jsonA contains:

    -- {
    --   "ciphertext": "encryptedData",
    --   "status": 200,
    --   "error": "errorMsg"
    -- }

    DECLARE @jsonB int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonB OUT

    EXEC sp_OAMethod @jsonB, 'UpdateString', @success OUT, 'users[0].role', 'Surgeon'
    EXEC sp_OAMethod @jsonB, 'UpdateNewArray', @success OUT, 'users[0].sub_roles'
    EXEC sp_OAMethod @jsonB, 'UpdateBool', @success OUT, 'users[0].viewable_for_sharing', 1
    EXEC sp_OAMethod @jsonB, 'UpdateInt', @success OUT, 'users[0].eula_create_date', 123
    EXEC sp_OAMethod @jsonB, 'UpdateString', @success OUT, 'users[1].role', 'Support'
    EXEC sp_OAMethod @jsonB, 'UpdateString', @success OUT, 'users[1].sub_roles[0]', 'Tech'
    EXEC sp_OAMethod @jsonB, 'UpdateString', @success OUT, 'users[1].sub_roles[1]', 'Service'
    EXEC sp_OAMethod @jsonB, 'UpdateBool', @success OUT, 'users[1].viewable_for_sharing', 1
    EXEC sp_OAMethod @jsonB, 'UpdateInt', @success OUT, 'users[1].eula_create_date', 123

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

    -- jsonB contains:

    -- {
    --   "users": [
    --     {
    --       "role": "Surgeon",
    --       "sub_roles": [],
    --       "viewable_for_sharing": true,
    --       "eula_create_date": 1649108922482
    --     },
    --     {
    --       "role": "Support",
    --       "sub_roles": [
    --         "Tech",
    --         "Service"
    --       ],
    --       "viewable_for_sharing": true,
    --       "eula_create_date": 1649108951523
    --     }
    --   ]
    -- }

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

    -- {
    --   "ciphertext": "encryptedData",
    --   "status": 200,
    --   "error": "errorMsg",
    --   "users": [
    --     {
    --       "role": "Surgeon",
    --       "sub_roles": [],
    --       "viewable_for_sharing": true,
    --       "eula_create_date": 1649108922482
    --     },
    --     {
    --       "role": "Support",
    --       "sub_roles": [
    --         "Tech",
    --         "Service"
    --       ],
    --       "viewable_for_sharing": true,
    --       "eula_create_date": 1649108951523
    --     }
    --   ]
    -- }

    -- First add an empty array at the desired location (-1 means append to end).
    EXEC sp_OAMethod @jsonA, 'AddArrayAt', @success OUT, -1, 'users'

    -- The destination is the empty "users" array, the source is the populated "users" array in jsonB.
    DECLARE @jsonUsersDest int
    EXEC sp_OAMethod @jsonA, 'ArrayOf', @jsonUsersDest OUT, 'users'
    DECLARE @jsonUsersSrc int
    EXEC sp_OAMethod @jsonB, 'ArrayOf', @jsonUsersSrc OUT, 'users'
    -- Copy the array items from source to dest
    EXEC sp_OAMethod @jsonUsersDest, 'AppendArrayItems', @success OUT, @jsonUsersSrc
    EXEC @hr = sp_OADestroy @jsonUsersDest

    EXEC @hr = sp_OADestroy @jsonUsersSrc

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

    -- The end result is this:

    -- {
    --   "ciphertext": "encryptedData",
    --   "status": 200,
    --   "error": "errorMsg",
    --   "users": [
    --     {
    --       "role": "Surgeon",
    --       "sub_roles": [
    --       ],
    --       "viewable_for_sharing": true,
    --       "eula_create_date": 123
    --     },
    --     {
    --       "role": "Support",
    --       "sub_roles": [
    --         "Tech",
    --         "Service"
    --       ],
    --       "viewable_for_sharing": true,
    --       "eula_create_date": 123
    --     }
    --   ]
    -- }

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


END
GO