Manipulating data returned by REST connector

mattgow — Mon, 07 Nov 2022 14:12:35 GMT

Hi guys,

I'm attempting to pull data from a bookings management system called Checkfront.

The endpoint is documented at https://api.checkfront.com/ref/booking.html

The way the data is formatted means that the "SQL" query the REST connector generates has to know the id of each booking. That makes it unusable. I'm wondering if there are any magic methods to manipulate things so it would know that the 'id' that preceeds each booking node can be ignored.

Here's a little bit of json payload. See how the booking id is shown outside the { with no label.

{ "version": "3.0", "request": { "page": 1, "pages": 3216 }, "booking/index": { "3": { "booking_id": 3, "code": "ZZVK-120619", "status_id": "STOP" }, "10": { "booking_id": 10, "code": "DCQH-040919", "status_id": "STOP" } } }

This is the kind of query the REST connector generates when I use it to attempt selection of the data. The "FROM" clauses need to specify the actual booking_id of each node. It's not feasible to use a query for data if the query itself needs to specify each booking_id. Any ideas to trick the REST connector into ignoring the id's that are messing things up?

RestConnectorMasterTable: SQL SELECT "version", "__KEY_root", (SELECT "page", "pages", "__KEY_request", "__FK_request", (SELECT "@Value", "__FK_path" FROM "path" FK "__FK_path" ArrayValueAlias "@Value") FROM "request" PK "__KEY_request" FK "__FK_request"), (SELECT "__FK_booking/index", "__KEY_booking/index", (SELECT "booking_id", "code", "status_id", "__FK_3" FROM "3" FK "__FK_3"), (SELECT "booking_id" AS "booking_id_u0", "code" AS "code_u0", "status_id" AS "status_id_u0", "__FK_10" FROM "10" FK "__FK_10") FROM "booking/index" PK "__KEY_booking/index" FK "__FK_booking/index") FROM JSON (wrap on) "root" PK "__KEY_root" WITH CONNECTION(Url "https://etc...");

topic Manipulating data returned by REST connector in Connectivity & Data Prep

Manipulating data returned by REST connector