API JSON

patrickbender — Wed, 22 Dec 2021 15:25:57 GMT

Hi,

I'm trying to extract data from an API and get the data in JSON format which leds to a rather nested LOAD script.

is there any way to avoid this:

RestConnectorMasterTable:
SQL SELECT
"name" AS "name_u2",
"owner_user_id",
"project_id",
"__KEY_root",
(SELECT
"name" AS "name_u1",
"description",
"form_id",
"__KEY_form_sections",
"__FK_form_sections",
(SELECT
"name" AS "name_u0",
"__KEY_form_section_records",
"__FK_form_section_records",
(SELECT
"__KEY_form_section_record_values",
"__FK_form_section_record_values",
(SELECT
"__KEY_form_section_record_input_values",
"__FK_form_section_record_input_values",
(SELECT
"last_editor_user_id" AS "last_editor_user_id_u2",
//"project_id" AS "project_id_u2",
"updated_at" AS "updated_at_u2",
"bigint_value" AS "bigint_value_u0",
"string_value" AS "string_value_u0",
"text_value" AS "text_value_u0",
"boolean_value" AS "boolean_value_u0",
"decimal_value" AS "decimal_value_u0",
"datetime_value" AS "datetime_value_u0",
"date_value" AS "date_value_u0",
"__FK_value"
FROM "value" FK "__FK_value")
FROM "form_section_record_input_values" PK "__KEY_form_section_record_input_values" FK "__FK_form_section_record_input_values")
FROM "form_section_record_values" PK "__KEY_form_section_record_values" FK "__FK_form_section_record_values")
FROM "form_section_records" PK "__KEY_form_section_records" FK "__FK_form_section_records")
FROM "form_sections" PK "__KEY_form_sections" FK "__FK_form_sections")
FROM JSON (wrap on) "root" PK "__KEY_root";

[value]:
LOAD [last_editor_user_id_u2] As LastEditedById,
//[project_id_u2] As ,
[updated_at_u2] As FormLastUpdated,

if(not IsNull([string_value_u0]), [string_value_u0],
if(not IsNull([text_value_u0]), [text_value_u0],
if(not IsNull([boolean_value_u0]), [boolean_value_u0],
if(not IsNull([decimal_value_u0]), [decimal_value_u0],
if(not IsNull([datetime_value_u0]), [datetime_value_u0],
if(not IsNull([date_value_u0]), [date_value_u0],
if(not isnull([bigint_value_u0]), [bigint_value_u0]
))))))) As FormDataValue,

[__FK_value] AS [__KEY_form_section_record_input_values]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_value]);

[form_section_record_input_values]:
LOAD [__KEY_form_section_record_input_values],
[__FK_form_section_record_input_values] AS [__KEY_form_section_record_values]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_form_section_record_input_values]);

more nested tables follow below I attach more of it if anyone have time to look at it 🙂

topic API JSON in Connectivity & Data Prep

API JSON