Remote dataset JSON array

Get Help
1

I have tried to follow the examples to configure a remote dataset, but having trouble getting the columns to populate with data.
I have the dataset defined and when I click Test data URL I get results

image
image846×784 19.4 KB

This is the full JSON that is returned. I have tried to define the root as series or data and neither of them work. Ultimately I am interested in getting the data arrays as my 2 columns.

{
“request”: {
“command”: “series”,
“series_id”: “PET.EMD_EPD2D_PTE_NUS_DPG.W”
},
“series”: [
{
“series_id”: “PET.EMD_EPD2D_PTE_NUS_DPG.W”,
“name”: “U.S. No 2 Diesel Retail Prices, Weekly”,
“units”: “Dollars per Gallon”,
“f”: “W”,
“unitsshort”: “$/gal”,
“description”: “U.S. No 2 Diesel Retail Prices”,
“copyright”: “None”,
“source”: “EIA, U.S. Energy Information Administration”,
“iso3166”: “USA”,
“geography”: “USA”,
“start”: “19940321”,
“end”: “20180507”,
“updated”: “2018-05-07T16:43:09-0400”,
“data”: [
[
“20180507”,
3.171
],
[
“20180430”,
3.157
],
[
“20180423”,
3.133
],
[
“20180416”,
3.104
],
[
“20180409”,
3.043
],
[
“20180402”,
3.042
],
[
“20180326”,
3.01
],
[
“20180319”,
2.972
],
[
“20180312”,
2.976
],
[
“20180305”,
2.992
],
],
}
],
}

2

Still looking for some guidance on working with remote JSON for DataSets.

This is the output from the API I am calling to get weekly fuel price information.

{
    "request": {
	    "command": "series",
	    "series_id": "PET.EMD_EPD2D_PTE_NUS_DPG.W"
    },
    "series": [{
	    "series_id": "PET.EMD_EPD2D_PTE_NUS_DPG.W",
	    "name": "U.S. No 2 Diesel Retail Prices, Weekly",
	    "units": "Dollars per Gallon",
	    "f": "W",
	    "unitsshort": "$\/gal",
	    "description": "U.S. No 2 Diesel Retail Prices",
	    "copyright": "None",
	    "source": "EIA, U.S. Energy Information Administration",
	    "iso3166": "USA",
	    "geography": "USA",
	    "start": "19940321",
	    "end": "20181029",
	    "updated": "2018-10-29T16:25:04-0400",
	    "data": [["20181029",
	    3.355],
	    ["20181022",
	    3.38],
	    ["20181015",
	    3.394],
	    ["20181008",
	    3.385],
	    ["20181001",
	    3.313],
	    ["20180924",
	    3.271],
	    ["20180917",
	    3.268],
	    ["20180910",
	    3.258],
	    ["20180903",
	    3.252],
	    ["20180827",
	    3.226]]
    }]
}

The data that I want to put into columns is contained in the data array

"data": [["20181029",
		3.355],
		["20181022",
		3.38],
		["20181015",
		3.394],
		["20181008",
		3.385],
	["20181001",
	3.313],
	["20180924",
	3.271],
	["20180917",
	3.268],
	["20180910",
	3.258],
	["20180903",
	3.252],
	["20180827",
	3.226]]

I believe that the correct jsonpath for these 2 columns would be

$..series[0].data[*].0 for date
$..series[0].data[*].1 for price

I have tried various combinations of the data root and columns, but the data set is always empty. My latest attempt was to specify the root as “series” and used jsonpath to specify the columns as $.[0].data[].0 and $.[0].data[].1

any help is appreciated.

3

I don’t think you can.

It would expect to have something like:

"data": [[
    "date": "20181029",
    "price": "3.355",]
    ]

So the column names could be matched.