![]() |
Chilkat HOME Android™ AutoIt C C# C++ Chilkat2-Python CkPython Classic ASP DataFlex Delphi DLL Go Java Node.js Objective-C PHP Extension Perl PowerBuilder PowerShell PureBasic Ruby SQL Server Swift Tcl Unicode C Unicode C++ VB.NET VBScript Visual Basic 6.0 Visual FoxPro Xojo Plugin
(SQL Server) Insert JSON Array into another JSON ObjectSee more JSON ExamplesDemonstrates how to insert a JSON array into a JSON object.Note: This example requires Chilkat v11.0.0 or greater.
-- 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, '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 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 -- } -- ] -- } -- The destination is the empty "users" array, the source is the populated "users" array in jsonB. DECLARE @jsonUsersDest int EXEC @hr = sp_OACreate 'Chilkat.JsonArray', @jsonUsersDest OUT EXEC sp_OAMethod @jsonA, 'AppendArray2', @success OUT, 'users', @jsonUsersDest DECLARE @jsonUsersSrc int EXEC @hr = sp_OACreate 'Chilkat.JsonArray', @jsonUsersSrc OUT EXEC sp_OAMethod @jsonB, 'ArrayOf2', @success OUT, 'users', @jsonUsersSrc -- Copy the array items from source to dest EXEC sp_OAMethod @jsonUsersDest, 'AppendArrayItems', @success OUT, @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 EXEC @hr = sp_OADestroy @jsonUsersDest EXEC @hr = sp_OADestroy @jsonUsersSrc END GO |
© 2000-2025 Chilkat Software, Inc. All Rights Reserved.