SQL Server

JSON Paths that need Double Quotes

See more JSON Examples

This example explains and demonstrates the situations where parts within a JSON path need to be enclosed in double-quotes. SQL Server

-- 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 the following JSON:

    -- 	{ 
    -- 	  "Meta Data": { 
    -- 	    "1: Symbol": "MSFT",
    -- 	    "2: Indicator": "Relative Strength Index (RSI)",
    -- 	    "3: Last Refreshed": "2017-07-28 09:30:00",
    -- 	    "4: Interval": "15min",
    -- 	    "5: Time Period": 10,
    -- 	    "6: Series Type": "close",
    -- 	    "7: Time Zone": "US/Eastern Time"
    -- 	  },
    -- 	  "Technical Analysis: RSI": { 
    -- 	    "2017-07-28 09:30": { 
    -- 	      "RSI": "38.6964"
    -- 	    },
    -- 	    "2017-07-27 16:00": { 
    -- 	      "RSI": "50.0088"
    -- 	    } 
    -- 	} 

    -- The path to the RSI valud 38.6964 is Technical Analysis: RSI.2017-07-28 09:30.RSI

    -- Whenever a path part contains a SPACE or "." char, that part must be enclosed
    -- in double quotes.  For example:

    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @json, 'LoadFile', @success OUT, 'qa_data/json/rsi.json'


    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, '"Technical Analysis: RSI"."2017-07-28 09:30".RSI'
    PRINT 'RSI: ' + @sTmp0

    -- output is 38.6964

    EXEC @hr = sp_OADestroy @json


END
GO