Don't understand how to transform JSON response into table

[bonvin]

I have a JSON response that looks like this: 

{

  "data": {

    "field1": "value",

    "field2": "value",

    "field3": "value",

    "subtable1": {

      "field4": "value",

      "field5": "value"

    },

    "subtable2": {

      "field6": "value",

      "field7": "value"

    }

}

What's the statement that will let me turn all of that into one single table with 7 columns? 

[rbartley]

Hi, 

Usually when working with JSON, I use the Qlik REST connector.  Take a look here:

https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/Connectors_REST/REST-c....

Once you've set up your connection, you can select the data you want and Qlik will generate the query.

For example, here's the query generated for my REST connection to JIRA:

RestConnectorMasterTable:
SQL SELECT
"expand" AS "expand_u0",
"startAt" AS "startAt_u0",
"maxResults" AS "maxResults_u0",
"total" AS "total_u0",
"__KEY_root",
(SELECT
"expand",
"id" AS "id_u6",
"self" AS "self_u8",
"key" AS "key_u3",
"__KEY_issues",
"__FK_issues",
(SELECT
"summary",
"created",
"description" AS "description_u3",
"customfield_10583",
"customfield_10584",
"resolution",
"resolutiondate",
"updated",
"__KEY_fields",
"__FK_fields",
(SELECT
"self",
"id",
"description",
"iconUrl",
"name",
"subtask",
"avatarId",
"__FK_issuetype"
FROM "issuetype" FK "__FK_issuetype"),
(SELECT
"self" AS "self_u0",
"id" AS "id_u0",
"name" AS "name_u0",
"description" AS "description_u0",
"__FK_components"
FROM "components" FK "__FK_components"),
(SELECT
"self" AS "self_u1",
"name" AS "name_u1",
"key",
"emailAddress",
"displayName",
"active",
"timeZone",
"__KEY_reporter",
"__FK_reporter",
(SELECT
"48x48",
"24x24",
"16x16",
"32x32",
"__FK_avatarUrls"
FROM "avatarUrls" FK "__FK_avatarUrls")
FROM "reporter" PK "__KEY_reporter" FK "__FK_reporter"),
(SELECT
"self" AS "self_u2",
"iconUrl" AS "iconUrl_u0",
"name" AS "name_u2",
"id" AS "id_u1",
"__FK_priority"
FROM "priority" FK "__FK_priority"),
(SELECT
"@Value",
"__FK_customfield_12631"
FROM "customfield_12631" FK "__FK_customfield_12631" ArrayValueAlias "@Value"),
(SELECT
"@Value" AS "@Value_u0",
"__FK_labels"
FROM "labels" FK "__FK_labels" ArrayValueAlias "@Value_u0"),
(SELECT
"remainingEstimate",
"remainingEstimateSeconds",
"originalEstimate",
"originalEstimateSeconds",
"__FK_timetracking"
FROM "timetracking" FK "__FK_timetracking"),
(SELECT
"self" AS "self_u3",
"name" AS "name_u3",
"key" AS "key_u0",
"emailAddress" AS "emailAddress_u0",
"displayName" AS "displayName_u0",
"active" AS "active_u0",
"timeZone" AS "timeZone_u0",
"__KEY_assignee",
"__FK_assignee",
(SELECT
"48x48" AS "48x48_u0",
"24x24" AS "24x24_u0",
"16x16" AS "16x16_u0",
"32x32" AS "32x32_u0",
"__FK_avatarUrls_u0"
FROM "avatarUrls" FK "__FK_avatarUrls_u0")
FROM "assignee" PK "__KEY_assignee" FK "__FK_assignee"),
(SELECT
"self" AS "self_u5",
"description" AS "description_u1",
"iconUrl" AS "iconUrl_u1",
"name" AS "name_u5",
"id" AS "id_u3",
"__KEY_status",
"__FK_status",
(SELECT
"self" AS "self_u4",
"id" AS "id_u2",
"key" AS "key_u1",
"colorName",
"name" AS "name_u4",
"__FK_statusCategory"
FROM "statusCategory" FK "__FK_statusCategory")
FROM "status" PK "__KEY_status" FK "__FK_status"),
(SELECT
"self" AS "self_u6",
"id" AS "id_u4",
"description" AS "description_u2",
"name" AS "name_u6",
"__FK_resolution"
FROM "resolution" FK "__FK_resolution")
FROM "fields" PK "__KEY_fields" FK "__FK_fields"),
(SELECT
"startAt",
"maxResults",
"total",
"__KEY_changelog",
"__FK_changelog",
(SELECT
"id" AS "id_u5",
"created" AS "created_u0",
"__KEY_histories",
"__FK_histories",
(SELECT
"self" AS "self_u7",
"name" AS "name_u7",
"key" AS "key_u2",
"emailAddress" AS "emailAddress_u1",
"displayName" AS "displayName_u1",
"active" AS "active_u1",
"timeZone" AS "timeZone_u1",
"__KEY_author",
"__FK_author",
(SELECT
"48x48" AS "48x48_u1",
"24x24" AS "24x24_u1",
"16x16" AS "16x16_u1",
"32x32" AS "32x32_u1",
"__FK_avatarUrls_u1"
FROM "avatarUrls" FK "__FK_avatarUrls_u1")
FROM "author" PK "__KEY_author" FK "__FK_author"),
(SELECT
"field",
"fieldtype",
"from",
"fromString",
"to",
"toString",
"__FK_items"
FROM "items" FK "__FK_items")
FROM "histories" PK "__KEY_histories" FK "__FK_histories")
FROM "changelog" PK "__KEY_changelog" FK "__FK_changelog")
FROM "issues" PK "__KEY_issues" FK "__FK_issues")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION(Url $(vURL));

I hope this helps.

[rbartley]

In fact, I just found this support entry:

https://support.qlik.com/articles/000051967

Description

Is it possible to interpret JSON data directly with Qlik script when it does not come from an REST API? 

Environments:

Qlik Sense - all versions
QlikView - all versions

Cause

Working as Designed

Resolution

There is no way to interpret JSON data directly in the Qlik script. Simply the functionality - a parser - does not exists in Qlik Engine.

In order to load JSON data, please consider:

1. Host the JSON file on a web server, where the JSON file can be access via a http or https URL
2. Load the URL into QlikView or Qlik Sense using Qlik REST connector

REST connector is included in the default installation of Qlik Sense. For QlikView, REST connector can be downloaded from Qlik download site at https://eu-a.demo.qlik.com/download/. 

[bonvin]

I don't have any issues setting up the connection, I'm fetching the data just fine. I'm just trying to the syntax right in the data loader to make this JSON response into one neat table. I'm really struggling to understand this whole thing with primary keys and foreign keys and whatnot, and all the examples I can find are these massive complicated things (like yours) that are just more confusing to me. I think I just need a super basic example of this in action, where someone is building a table with like three columns (that are in different "nodes" or whatever it's called in JSON) to understand the concept, and then I can build from there. 

[rbartley]

In my JIRA app I build tables based on RestConnectorMasterTable, e.g.

[issuetype]:
LOAD [self],
[id],
[description],
[iconUrl],
[name],
[subtask],
[avatarId],
[__FK_issuetype] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_issuetype]);

In the RestConnectorMasterTable take a look at the __fk and __key fields.

If this is no help then please post your app so that it's easier to suggest a way forward.

[bonvin]

Right, well, this is about as far as I've come: 

RestConnectorMasterTable:

SQL SELECT
"__PK_data",
"id" AS "employeeId",
"firstName",
"lastName",
(SELECT
"__FK_custom",
"value" AS "systemId"
FROM "custom_212319" FK "__FK_custom")
FROM JSON (wrap off) "data" PK "__PK_data"
WITH CONNECTION (...)

And now I want to make a table called Employees that looks like this: 
[employeeId], [firstName], [lastName], [systemId]

But I don't have a clue how to make a load statement from this that would make that happen. 

[rbartley]

Can you upload your qvd, please?  It will make things a lot easier.  If necessary exclude any confidential data.

[bonvin]

I don't really want to do that. This is just a tiny part of a huge data load involving several SQL databases and most likely lots of confidential stuff that I don't want to go through to make sure I'm not sharing anything I shouldn't be. 

What I posted is the only relevant stuff for this particular part of the loader. I need an employees table for my app. That employees table should have those four columns. The systemId field is not stored at the same level as the other fields, and I don't know how to join them up. That's it. 

I can do this: 

[Employees]:
LOAD [employeeId], [firstName], [lastName]
RESIDENT [RestConnectorMasterTable];

That's working splendidly. But I need the [systemId] also. And this is not working whatsoever: 

[Employees]:
LOAD [employeeId], [firstName], [lastName], [systemId]
RESIDENT [RestConnectorMasterTable];

There is some  join operation thing I need to do here involving the keys, but I just don't get it. 

[rbartley]

Ok.  My main query begins here:

RestConnectorMasterTable:
SQL SELECT
"expand" AS "expand_u0",
"startAt" AS "startAt_u0",
"maxResults" AS "maxResults_u0",
"total" AS "total_u0",
"__KEY_root",

(SELECT
"expand",
"id" AS "id_u6",
"self" AS "self_u8",
"key" AS "key_u3",
"__KEY_issues",
"__FK_issues",

Later on, I have queries that return data specific to root and issues:

[root]:
LOAD [expand_u0] AS [expand_u0],
[startAt_u0] AS [startAt_u0],
[maxResults_u0] AS [maxResults_u0],
[total_u0] AS [total_u0],
[__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);

[issues]:
LOAD [expand],
[id_u6],
[self_u8],
[key_u3],
[key_u3] as "Ticket ID",
[__KEY_issues],
[__FK_issues] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_issues]);

i.e. the foreign key of issues is renamed to the primary key of root to create the join. 

So, in your case, try:

[System]:

"__FK_custom",
"value" AS "systemId",

 "__FK_custom" as "__PK_data"

RESIDENT [RestConnectorMasterTable];

[Employees]:
LOAD [employeeId], [firstName], [lastName],

"__PK_data"

RESIDENT [RestConnectorMasterTable];

If this works then you can always combine the tables into one.

Also, I'm not sure if this is relevant or not, but have you tried changing FROM JSON (wrap off) "data" PK "__PK_data" to FROM JSON (wrap on) "data" PK "__PK_data"? I couldn't find a good explanation of the difference between wrap on and wrap off

This is the best help page I could find: https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/Connectors_REST/Load-R... 

[bonvin]

Aha, I was on the right path then, because I've tried this: 

[Employees]:
LOAD [employeeId], [firstName], [lastName], [__PK_data] AS [key]
RESIDENT [RestConnectorMasterTable]
WHERE NOT ISNULL([__PK_data]);

JOIN([Employees])
LOAD [systemId], [__FK_custom] AS [key]
RESIDENT [RestConnectorMasterTable]
WHERE NOT ISNULL([__FK_custom]);

Sadly, this does not work either. My situation is a bit more complex. For whatever reason, this API only gives me the custom fields when I request a specific employeeId. I therefore need to use a FOR .. NEXT with the REST call and fetch the information for every single employeeId, one by one (and there are hundreds, so this takes forever). But that results in a situation where they all have the same key (which is 1), and I can't make the join happen obviously. Do you see any way around this? 

And yeah, I've tried with wrap on and wrap off. Some of the other API requests in the data load don't work with it on, and some don't work with it off. I have no clue what it does.