REST Connector - How to retain the API JSON response

[jmcy2408]

Hi,

Looking for some advise on using the REST connector, i have this fully working however the connector during the setup wizard wants to flatten the response into a useable table - which is great for so many use cases however i need my response to be kept in a JSON / text string (as my fields are dynamic so hard coding the script for specific fields doesnt work).

Does anyone have any experience in doing this or suggests an alternative?

Many thanks

John

[marksouzacosta]

Hi @jmcy2408,

If you know the full table schema, you can create an empty table with all the fields as placeholders and force a concatenation with your Rest Data.
Example:

MyTable:
LOAD * INLINE [
    Field1, Field2, Field3, Fiel4
];

LIB CONNECT TO 'YourRestConnection';

RestConnectorMasterTable:
SQL SELECT 
	"__KEY_root",
	(SELECT 
		"Field1",
		"Field2"
		"__FK_data"
	FROM "data" FK "__FK_data")
FROM JSON (wrap on) "root" PK "__KEY_root";

// Must force concatenation with the empty table
Concatenate([MyTable])
LOAD
    Field1,
    Field2
RESIDENT
    RestConnectorMasterTable
WHERE
    NOT IsNull([__FK_data])
;

DROP TABLE RestConnectorMasterTable;

In my example, Field3 and 4 will exist in the final combined table, but with null values.

Does that help you?

Regards,

Mark Costa