REST connection with JSONROWS - Columns and rows within same array

Hi Andreas

Did you see my guide about this.

• Connect to JSON Tutorial

REST and JSON can be used interchangeably, so it will work for this one too.

I could not really make it work with the file you shared. I changed it to this.

{
	"response":{
		"columns": [
			{"name":"test1"},
			{"name":"test2"}
		],
		"rows": [
			[
				1,
				2
			],
			[
				3,
				4
			],
			[
				5,
				6
			]
		]
	}
}

As you can see I added a columns and a rows part to the file.

Then using this JSONPath and having the JSON type set to JSONRows it worked.

JSONPath: column:$.response.columns;columnname:$.response.columns.name;row:$.response.rows

Hi Thomas, 

Yes, it works fine if columns and rows are put in separate arrays, but changing the response from the API is not an option here. 

I guess there is no way around external scripts in this case?

I thought about that too, I am not sure how to specify what is columns and what is rows, when there is nothing to point at.

The JSONPath would have to be like this. column:$.response;columnname:$.response.name;row:$.response

I can’t see how it would know where the data is, when no field specifies it.

I can ask CData if they know of some workaround to make it work?

Hi Anders

I have asked CData for help with this. I will let you know if they know a way to do this.

Hi @andreas.skagestad have you tried with the latest version of the provider? Please let us know if this resolves your issue