How to decode Json response [Solved]

[gerardosamara]

Hi ,

Domoticz V4.11763
Rasbian Buster
RPI3

I have created a dzVents script to display on a text device the EurUsd exchange rate from the API of www.alphavantage.com

https://www.alphavantage.co/documentation/

As per the header this is a JSON response ( Content-Type application/json )
I could not figure out how to extract the "currency exchange rate" in the forum or the dzvents wiki ?

Here is the code

Code: Select all

-- Cours EURUSD
-- 
--
ALPHA_APIKEY = 'xxxx'
-- ALPHA_URL = 'https://www.alphavantage.co/query?function=FX_INTRADAY&from_symbol=EUR&to_symbol=USD&interval=5min&apikey=' .. ALPHA_APIKEY
ALPHA_URL = 'https://www.alphavantage.co/query?function=CURRENCY_EXCHANGE_RATE&from_currency=EUR&to_currency=USD&apikey=' .. ALPHA_APIKEY


return {
	on = {
		timer = {
			'every 5 minutes' --  to trigger the request
		},
		httpResponses = {
			'forex_eurusd' -- must match with the callback passed to the openURL command
		}
	},
	logging = {
    level = domoticz.LOG_DEBUG,
    marker = "EurUsd"
    },
	execute = function(domoticz, item)

		if (item.isTimer) then
			domoticz.openURL({
				url = ALPHA_URL, 
				method = 'GET',
				callback = 'forex_eurusd', -- see httpResponses above.
			})
		end

		if (item.isHTTPResponse) then

			if (item.statusCode == 200) then
			    
			    domoticz.log('item content =', domoticz.LOG_DEBUG)
			    domoticz.log(item, domoticz.LOG_DEBUG)
				
				if (item.isJSON) then
				    
				   domoticz.log('item.JSON content =', domoticz.LOG_DEBUG)
				   domoticz.log(item.json , domoticz.LOG_DEBUG)
				   -- Decode exchange rate
				   -- ???
				   domoticz.log('EUR USD Exchange rate =', domoticz.LOG_DEBUG)
                   
					-- update Dummy text device in Domoticz
					-- domoticz.devices('EurUsd').updateText(exchange_rate)
				end
			else
				domoticz.log('There was a problem handling the request', domoticz.LOG_ERROR)
				domoticz.log(item, domoticz.LOG_ERROR)
			end

		end

	end
}

Here is the log showing the JSON response

Code: Select all

2020-03-08 21:00:00.132 Status: EventSystem: Script event triggered: /home/pi/domoticz/dzVents/runtime/dzVents.lua
2020-03-08 21:00:01.036 Status: dzVents: Info: Handling httpResponse-events for: "forex_eurusd"
2020-03-08 21:00:01.037 Status: dzVents: Info: EurUsd: ------ Start internal script: EurUsd: HTTPResponse: "forex_eurusd"
2020-03-08 21:00:01.038 Status: dzVents: Debug: EurUsd: item content =
2020-03-08 21:00:01.038 Status: dzVents: Debug: EurUsd: {["json"]={["Realtime Currency Exchange Rate"]={["4. To_Currency Name"]="United States Dollar", ["5. Exchange Rate"]="1.13950000", ["2. From_Currency Name"]="Euro", ["6. Last Refreshed"]="2020-03-09 03:00:00", ["7. Time Zone"]="UTC", ["1. From_Currency Code"]="EUR", ["8. Bid Price"]="1.13940000", ["3. To_Currency Code"]="USD", ["9. Ask Price"]="1.13950000"}}, ["callback"]="forex_eurusd", ["headers"]={["Content-Type"]="application/json", ["Via"]="1.1 vegur", ["Transfer-Encoding"]="chunked", ["Connection"]="keep-alive", ["Vary"]="Cookie", ["Date"]="Mon, 09 Mar 2020 03:00:00 GMT", ["Server"]="gunicorn/19.7.0", ["Allow"]="GET, HEAD, OPTIONS", ["X-Frame-Options"]="SAMEORIGIN"}, ["baseType"]="httpResponse", ["trigger"]="forex_eurusd", ["isSecurity"]=false, ["ok"]=true, ["isCustomEvent"]=false, ["statusText"]="OK", ["isHTTPResponse"]=true, ["isJSON"]=true, ["_contentType"]="application/json", ["isXML"]=false, ["isHardware"]=false, ["isSystem"]=false, ["protocol"]="HTTP/1.1", ["isTimer"]=false, ["data"]="{
2020-03-08 21:00:01.038 "Realtime Currency Exchange Rate": {
2020-03-08 21:00:01.038 "1. From_Currency Code": "EUR",
2020-03-08 21:00:01.038 "2. From_Currency Name": "Euro",
2020-03-08 21:00:01.038 "3. To_Currency Code": "USD",
2020-03-08 21:00:01.038 "4. To_Currency Name": "United States Dollar",
2020-03-08 21:00:01.038 "5. Exchange Rate": "1.13950000",
2020-03-08 21:00:01.038 "6. Last Refreshed": "2020-03-09 03:00:00",
2020-03-08 21:00:01.038 "7. Time Zone": "UTC",
2020-03-08 21:00:01.038 "8. Bid Price": "1.13940000",
2020-03-08 21:00:01.038 "9. Ask Price": "1.13950000"
2020-03-08 21:00:01.038 }
2020-03-08 21:00:01.038 }", ["isVariable"]=false, ["isScene"]=false, ["isDevice"]=false, ["isGroup"]=false, ["statusCode"]=200}
2020-03-08 21:00:01.038 Status: dzVents: Debug: EurUsd: item.JSON content =
2020-03-08 21:00:01.038 Status: dzVents: Debug: EurUsd: {["Realtime Currency Exchange Rate"]={["4. To_Currency Name"]="United States Dollar", ["5. Exchange Rate"]="1.13950000", ["2. From_Currency Name"]="Euro", ["6. Last Refreshed"]="2020-03-09 03:00:00", ["7. Time Zone"]="UTC", ["1. From_Currency Code"]="EUR", ["8. Bid Price"]="1.13940000", ["3. To_Currency Code"]="USD", ["9. Ask Price"]="1.13950000"}}
2020-03-08 21:00:01.038 Status: dzVents: Debug: EurUsd: EUR USD Exchange rate =
2020-03-08 21:00:01.039 Status: dzVents: Info: EurUsd: ------ Finished EurUsd 

Please anybody may suggest a solution ?
Thank you

[waaren]

I have created a dzVents script to display on a text device the EurUsd exchange rate from the API of www.alphavantage.com
Please anybody may suggest a solution ?

I always use domoticz.utils.dumpTable() when the structure of the the JSON is not clear at first sight. Best to look at the log on OS level as that will also show the indention (= nesting in table)

See below.

Code: Select all

-- Cours EURUSD
-- 
--

local scriptVar = 'forex_eurusd'

return 
{
    on = 
    {
        timer = 
        {
            'every 5 minutes' --  to trigger the request
        },

        devices  = 
        {
            'JSONTrigger' --  to trigger the request during development / test 
        },

        httpResponses = 
        {
            scriptVar,            -- must match with the callback passed to the openURL command
        },
    },

    logging = 
    {
        level = domoticz.LOG_DEBUG,
        marker = scriptVar,
    },

    execute = function(dz, item)

        ALPHA_APIKEY = '7EBxxxxx'
        -- ALPHA_URL = 'https://www.alphavantage.co/query?function=FX_INTRADAY&from_symbol=EUR&to_symbol=USD&interval=5min&apikey=' .. ALPHA_APIKEY
        ALPHA_URL = 'https://www.alphavantage.co/query?function=CURRENCY_EXCHANGE_RATE&from_currency=EUR&to_currency=USD&apikey=' .. ALPHA_APIKEY

        if item.isTimer or item.isDevice then
            dz.openURL(
                {
                    url = ALPHA_URL, 
                    method = 'GET',
                    callback = scriptVar, -- see httpResponses above.
                })
        else
            if item.ok then
                myJSON = ( item.isJSON and item.json ) or dz.utils.fromJSON(item.data) -- JSON to Lua table ( also when not recognized as JSON )

                if _G.logLevel == dz.LOG_DEBUG then
                     dz.utils.dumpTable(myJSON) -- show structure of converted JSON -- ( only for debug )
                end

                local exchangeRateText = 'EUR USD Exchange rate = ' .. dz.utils.round(myJSON["Realtime Currency Exchange Rate"]["5. Exchange Rate"], 4)
                dz.log(exchangeRateText,dz.LOG_DEBUG)

                -- update Dummy text device in dz
                dz.devices('EurUsd').updateText(exchangeRateText)

            else
                dz.log('There was a problem handling the request', dz.LOG_ERROR)
                dz.log(item, dz.LOG_ERROR)
            end
        end
    end
}

[gerardosamara]

Thanks a lot @waaren for the updated script
It works very well as per attached screenshot
And thanks again for this great dzvents framework

Capture eurusd.PNG (66.53 KiB) Viewed 1023 times

[waaren]

And thanks again for this great dzVents framework

YW but please note that most of the credits for the original idea and initial dzVents framework should go to @dannybloe and @jvandenbroek