To homepage To homepage
Solved

REST connection with JSONROWS - Columns and rows within same array

  • 26 April 2023
  • 6 replies
  • 148 views
A
Rank
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

Thomas Lind
Rank
Userlevel 5
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

 

A
Rank
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?

Thomas Lind
Rank
Userlevel 5
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?

Thomas Lind
Rank
Userlevel 5
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.

Thomas Lind
Rank
Userlevel 5
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.

Christian Hauggaard
Rank
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


Terms and ConditionsCookie settings