Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 🙂