Solved

REST connection with JSONROWS - Columns and rows within same array

  • 26 April 2023
  • 6 replies
  • 164 views

Badge

Hi,

I am trying to parse a response using the REST connector from an API which returns a JSONROWS-format where the first row contains the name of the columns, while the following rows contain the values.

The response is on the form:

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

]
}

I have tried several different xPath-configurations. The following gives the correct columns, but returns zero rows:
xPath:        column:/response;columnname:/response.name;row:/response

I was hoping that at least this would have parsed all the values as strings with the first row containing the {“name”:”testN”} in the corresponding columns.

How do I work with this response?

icon

Best answer by Thomas Lind 17 October 2023, 15:54

View original

6 replies

Userlevel 6
Badge +5

Hi Andreas

Did you see my guide about this.

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

 

Badge

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?

Userlevel 6
Badge +5

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?

Userlevel 6
Badge +5

Hi Anders

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

Userlevel 6
Badge +5

Hi @andreas.skagestad 

I seem to have forgotten to give an update on this.

I got a new version of the provider called 23.0.8595.0 that should have resolved this issue.

Userlevel 6
Badge +5

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

Reply