Use ID from API response instead of __KEY_root (REST Connector)

[RoyBatty]

Hi,

We use the REST Connector to load the data. Qlik recognizes and organizes the data from the API response: it creates multiple tables and adds a __KEY_root column to each table. That field (__KEY_root) is used for relations between those tables. 

So, __KEY_root is not from the API response, but it is the primary key that Qlik added to create relationships between the tables it generated based on the API response.

For example:

RestConnectorMasterTable:
SQL SELECT 
  "id", // <===== This is from API response
  "name",
  "__KEY_root", // <===== This is not from API response; Qlik added this
  (SELECT 
     "@Value",
     "__FK_domains" // <===== This is foreign key ("__KEY_root", see below)
    FROM "domains" FK "__FK_domains" ArrayValueAlias "@Value"),
  (SELECT 
     "__FK_custom_fields" // <===== This is foreign key ("__KEY_root", see below)
    FROM "custom_fields" FK "__FK_custom_fields")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION(Url "https://*******/api/v2/***");

[domains]:
LOAD 
  [@Value],
  [__FK_domains] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_domains]);


[custom_fields]:
LOAD 
  [__FK_custom_fields] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_custom_fields]);


[root]:
LOAD 
  [id],
  [name],
  [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);


DROP TABLE RestConnectorMasterTable;

Is it possible not to add __KEY_root but instead to use id from API response (which is already primary key/unique)? We ask this because later we have to load the data using the API and update all those tables, it's a complicated and long story 🙂

We tried this (we removed/replaced __KEY_root with id :

RestConnectorMasterTable:
SQL SELECT 
  "id",
  "name",
  (SELECT 
     "@Value",
     "__FK_domains"
   FROM "domains" FK "__FK_domains" ArrayValueAlias "@Value"),
  (SELECT 
     "__FK_custom_fields"
   FROM "custom_fields" FK "__FK_custom_fields")
FROM JSON (wrap on) "root" PK "id"
WITH CONNECTION(Url "https://****/api/v2/****");

[domains]:
LOAD 
  [@Value],
  [__FK_domains] AS [id]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_domains]);


[custom_fields]:
LOAD 
  [__FK_custom_fields] AS [id]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_custom_fields]);


[root]:
LOAD 
  [id],
  [name]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([id]);


DROP TABLE RestConnectorMasterTable;

But something is wrong, in these two "non root" tables the "id" column values ​​are still the values ​​generated by Qlik, starting from 1... (they are not actually the real "id" values ​​from the API response):

[RoyBatty]

If what we want is possible (to not add __KEY_root at all, but only to use id as a key) - then it is necessary to change this SQL somehow:

SQL SELECT 
  "id", // <===== This is from API response
  "name",
  "__KEY_root", // <===== This is not from API response; Qlik added this
  (SELECT 
     "@Value",
     "__FK_domains"
    FROM "domains" FK "__FK_domains" ArrayValueAlias "@Value"),
  (SELECT 
     "__FK_custom_fields"
    FROM "custom_fields" FK "__FK_custom_fields")
FROM JSON (wrap on) "root" PK "__KEY_root"

The problem seems to be in the sub-selects:

...
 (SELECT 
    "@Value",
    "__FK_domains"
  FROM "domains" FK "__FK_domains" ArrayValueAlias "@Value"),
 (SELECT 
    "__FK_custom_fields"
  FROM "custom_fields" FK "__FK_custom_fields")
FROM JSON (wrap on) "root" PK "__KEY_root"
...

specifically this syntax with "FK" (if we had to guess), but we are not sure how to change it to use id as foreign key in those sub-selects 🤔

[RoyBatty · Accepted solution]

RestConnectorMasterTable:
SQL SELECT 
  "id", 
  "name",
  "__KEY_root", 
  (SELECT 
    "@Value",
    "__FK_domains" 
  FROM "domains" FK "__FK_domains" ArrayValueAlias "@Value")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION(Url "https://********");

[domains]:
LOAD 
  [@Value],
  [__FK_domains] AS [__KEY_root] 
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_domains]);

// Add "id" and remove __KEY_root: <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
LEFT JOIN([domains])
LOAD
  [id],
  [__KEY_root]
RESIDENT RestConnectorMasterTable;
WHERE NOT IsNull([__KEY_root]);

DROP field [__KEY_root] FROM [domains];

[root]: // Removed [__KEY_root] <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
LOAD 
  [id],
  [name]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);

DROP TABLE RestConnectorMasterTable;