(SQL Server) Modify Parts of JSON Document

Demonstrates how to modify parts of a JSON document. This example uses the following JSON document:

{
   "fruit": [
      	{
         "kind": "apple",
	 "count": 24,
	 "fresh": true,
	 "extraInfo": null,
	 "listA": [ "abc", 1, null, false ],
	 "objectB": { "animal" : "monkey" }
      	},
	{
         "kind": "pear",
	 "count": 18,
	 "fresh": false,
	 "extraInfo": null
	 "listA": [ "xyz", 24, null, true ],
	 "objectB": { "animal" : "lemur" }
	}
    ],
    "list" : [ "banana", 12, true, null, "orange", 12.5, { "ticker": "AAPL" }, [ 1, 2, 3, 4, 5 ] ],
    "alien" : true
}

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

    -- Load the JSON from a file.
    DECLARE @success int
    EXEC sp_OAMethod @json, 'LoadFile', @success OUT, 'qa_data/json/modifySample.json'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @json, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    -- This example will not check for errors (i.e. null / false / 0 return values)...

    -- Get the "list" array:
    DECLARE @listA int
    EXEC sp_OAMethod @json, 'ArrayOf', @listA OUT, 'list'

    -- Modify values in the list.

    -- Change banana to plantain
    EXEC sp_OAMethod @listA, 'SetStringAt', @success OUT, 0, 'plantain'

    -- Change 12 to 24
    EXEC sp_OAMethod @listA, 'SetIntAt', @success OUT, 1, 24

    -- Change true to false
    EXEC sp_OAMethod @listA, 'SetBoolAt', @success OUT, 2, 0

    -- Is the 3rd item null?
    DECLARE @bNull int
    EXEC sp_OAMethod @listA, 'IsNullAt', @bNull OUT, 3

    -- Change "orange" to 32.
    EXEC sp_OAMethod @listA, 'SetIntAt', @success OUT, 4, 32

    -- Change 12.5 to 31.2
    EXEC sp_OAMethod @listA, 'SetNumberAt', @success OUT, 5, '31.2'

    -- Replace the { "ticker" : "AAPL" } object with { "ticker" : "GOOG" }
    -- Do this by deleting, then inserting a new object at the same location.
    EXEC sp_OAMethod @listA, 'DeleteAt', @success OUT, 6
    EXEC sp_OAMethod @listA, 'AddObjectAt', @success OUT, 6
    DECLARE @tickerObj int
    EXEC sp_OAMethod @listA, 'ObjectAt', @tickerObj OUT, 6
    EXEC sp_OAMethod @tickerObj, 'AddStringAt', @success OUT, -1, 'ticker', 'GOOG'

    EXEC @hr = sp_OADestroy @tickerObj

    -- Replace "[ 1, 2, 3, 4, 5 ]" with "[ "apple", 22, true, null, 1080.25 ]"
    EXEC sp_OAMethod @listA, 'DeleteAt', @success OUT, 7
    EXEC sp_OAMethod @listA, 'AddArrayAt', @success OUT, 7
    DECLARE @aa int
    EXEC sp_OAMethod @listA, 'ArrayAt', @aa OUT, 7
    EXEC sp_OAMethod @aa, 'AddStringAt', @success OUT, -1, 'apple'
    EXEC sp_OAMethod @aa, 'AddIntAt', @success OUT, -1, 22
    EXEC sp_OAMethod @aa, 'AddBoolAt', @success OUT, -1, 1
    EXEC sp_OAMethod @aa, 'AddNullAt', @success OUT, -1
    EXEC sp_OAMethod @aa, 'AddNumberAt', @success OUT, -1, '1080.25'
    EXEC @hr = sp_OADestroy @aa

    EXEC @hr = sp_OADestroy @listA

    -- Get the "fruit" array
    DECLARE @aFruit int
    EXEC sp_OAMethod @json, 'ArrayAt', @aFruit OUT, 0

    -- Get the 1st element:
    DECLARE @appleObj int
    EXEC sp_OAMethod @aFruit, 'ObjectAt', @appleObj OUT, 0

    -- Modify values by member name:
    EXEC sp_OAMethod @appleObj, 'SetStringOf', @success OUT, 'fruit', 'fuji_apple'
    EXEC sp_OAMethod @appleObj, 'SetIntOf', @success OUT, 'count', 46
    EXEC sp_OAMethod @appleObj, 'SetBoolOf', @success OUT, 'fresh', 0
    EXEC sp_OAMethod @appleObj, 'SetStringOf', @success OUT, 'extraInfo', 'developed by growers at the Tohoku Research Station in Fujisaki'

    EXEC @hr = sp_OADestroy @appleObj

    -- Modify values by index:
    DECLARE @pearObj int
    EXEC sp_OAMethod @aFruit, 'ObjectAt', @pearObj OUT, 1
    EXEC sp_OAMethod @pearObj, 'SetStringAt', @success OUT, 0, 'bartlett_pear'
    EXEC sp_OAMethod @pearObj, 'SetIntAt', @success OUT, 1, 12
    EXEC sp_OAMethod @pearObj, 'SetBoolAt', @success OUT, 2, 0
    EXEC sp_OAMethod @pearObj, 'SetStringAt', @success OUT, 3, 'harvested in late August to early September'
    EXEC @hr = sp_OADestroy @pearObj

    EXEC @hr = sp_OADestroy @aFruit

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

    EXEC @hr = sp_OADestroy @json


END
GO