(SQL Server) JSON Estimote Data

Demonstrates accessing some data from an Estimote REST API response. The Estimate REST API responds with a JSON array (i.e. something beginning with "[" and ending with "]"). To parse, this must be made into a JSON object by prepending "{"*":" and appending "}".

This example uses the following JSON object:

{"*":[{
        "id" : "B9407F30-F5F8-466E-AFF9-25556B57FE6D:28203:50324",
        "uuid" : "B9407F30-F5F8-466E-AFF9-25556B57FE6D",
        "major" : 28203,
        "minor" : 50324,
        "mac" : "dd6fc4946e2b",
        "settings" : {
            "battery" : 100,
            "interval" : 950,
            "hardware" : "D3.4",
            "firmware" : "A3.2.0",
            "basic_power_mode" : true,
            "smart_power_mode" : true,
            "timezone" : "America/Los_Angeles",
            "security" : false,
            "motion_detection" : true,
            "latitude": 37.7979,
            "longitude": -122.4408,
            "conditional_broadcasting" : "flip to stop",
            "broadcasting_scheme" : "estimote",
            "range" : -12,
            "power" : -12,
            "firmware_deprecated" : false,
            "firmware_newest" : true,
            "location" : null
        },
        "color" : "blueberry",
        "context_id" : 339488,
        "name" : "blueberry ibeacon1",
        "battery_life_expectancy_in_days" : 1377,
        "tags" : []
    }, {
        "id" : "B9407F30-F5F8-466E-AFF9-25556B57FE6D:25845:21739",
        "uuid" : "B9407F30-F5F8-466E-AFF9-25556B57FE6D",
        "major" : 25845,
        "minor" : 21739,
        "mac" : "ff5454eb64f5",
        "settings" : {
            "battery" : 100,
            "interval" : 950,
            "hardware" : "D3.4",
            "firmware" : "A3.2.0",
            "basic_power_mode" : false,
            "smart_power_mode" : true,
            "timezone" : "America/Los_Angeles",
            "security" : false,
            "motion_detection" : true,
            "conditional_broadcasting" : "flip to stop",
            "broadcasting_scheme" : "estimote",
            "range" : -12,
            "power" : -12,
            "firmware_deprecated" : false,
            "firmware_newest" : true,
            "location" : null
        },
        "color" : "blueberry",
        "context_id" : 339483,
        "name" : "blueberry2",
        "battery_life_expectancy_in_days" : 1168,
        "tags" : []
    }
]}

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

    EXEC sp_OASetProperty @json, 'EmitCompact', 0

    -- Assume the file contains the data as shown above..
    DECLARE @success int
    EXEC sp_OAMethod @json, 'LoadFile', @success OUT, 'qa_data/json/estimote.json'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @json, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    -- To get the value for "settings.battery" for the 1st array object:
    DECLARE @batteryVal int
    EXEC sp_OAMethod @json, 'IntOf', @batteryVal OUT, '*[0].settings.battery'

    PRINT 'battery: ' + @batteryVal

    -- To get the value for "settings.timezon" for the 1st array object:
    DECLARE @timeZone nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @timeZone OUT, '*[0].settings.timezone'

    PRINT 'timezone: ' + @timeZone

    -- To get the "settings.range" for the 2nd array object:
    DECLARE @rangeVal int
    EXEC sp_OAMethod @json, 'IntOf', @rangeVal OUT, '*[1].settings.range'

    PRINT 'range: ' + @rangeVal

    -- To  get the "settings.longitude" for the 1st array object:
    -- Note: Any primitie value can be retrieved as as string: integers, floating point numbers, booleans, etc.
    DECLARE @longitudeStr nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @longitudeStr OUT, '*[0].settings.longitude'

    PRINT 'longitude: ' + @longitudeStr

    EXEC @hr = sp_OADestroy @json


END
GO