Select * from JSON - REST Connector

[nelsonloureiro1]

Hello everybody, 

Does anybody know how to select * from a json that is being returned by the use of Qlik REST Connector?

The problem arrises because I don't know the json's structure that is being responded in advance. And I want to generalize the code to handle multiple json's structures. So, the idea is to get all fields from a json without knowing a single thing about its structure.

Attached is an example of how we currently select fields from a json that is being returned by an API request.

Thank you, regards,

Nelson 

[QFabian]

Hi @nelsonloureiro1 , maybe you are talking about using the connector from script : 

for example, i chose monitor_apps_REST_app connection :

then, when you click on root to get selected every checkbox , and then you get the entire SQL SELECT, that have all the possible fields from that query

and then you can modify the script to get some tables from the initial query select :

LIB CONNECT TO 'monitor_apps_REST_app';

RestConnectorMasterTable:

SQL SELECT 

"id" AS "id_u1",

"createdDate",

"modifiedDate",

"modifiedByUserName",

"name" AS "name_u1",

"appId",

"sourceAppId",

"targetAppId",

"publishTime",

"published",

"description",

"stream",

"fileSize",

"lastReloadTime",

"thumbnail",

"savedInProductVersion",

"migrationHash",

"dynamicColor",

"availabilityStatus",

"lastDataDistribution",

"staticByteSize",

"privileges" AS "privileges_u1",

"schemaPath",

"__KEY_root",

(SELECT 

"@Value",

"__FK_customProperties"

FROM "customProperties" FK "__FK_customProperties" ArrayValueAlias "@Value"),

(SELECT 

"id",

"userId",

"userDirectory",

"userDirectoryConnectorName",

"name",

"privileges",

"__FK_owner"

FROM "owner" FK "__FK_owner"),

(SELECT 

"@Value" AS "@Value_u0",

"__FK_tags"

FROM "tags" FK "__FK_tags" ArrayValueAlias "@Value_u0"),

(SELECT 

"id" AS "id_u0",

"name" AS "name_u0",

"privileges" AS "privileges_u0",

"__FK_stream"

FROM "stream" FK "__FK_stream")

FROM JSON (wrap on) "root" PK "__KEY_root";

[customProperties]:

LOAD [@Value],

[__FK_customProperties] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_customProperties]);

[owner]:

LOAD [id],

[userId],

[userDirectory],

[userDirectoryConnectorName],

[name],

[privileges],

[__FK_owner] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_owner]);

[tags]:

LOAD [@Value_u0] AS [@Value_u0],

[__FK_tags] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_tags]);

[stream]:

LOAD [id_u0] AS [id_u0],

[name_u0] AS [name_u0],

[privileges_u0] AS [privileges_u0],

[__FK_stream] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_stream]);

[root]:

LOAD [id_u1] AS [id_u1],

[createdDate],

[modifiedDate],

[modifiedByUserName],

[name_u1] AS [name_u1],

[appId],

[sourceAppId],

[targetAppId],

[publishTime],

[published],

[description],

[stream],

[fileSize],

[lastReloadTime],

[thumbnail],

[savedInProductVersion],

[migrationHash],

[dynamicColor],

[availabilityStatus],

[lastDataDistribution],

[staticByteSize],

[privileges_u1] AS [privileges_u1],

[schemaPath],

[__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__KEY_root]);

DROP TABLE RestConnectorMasterTable;

[nelsonloureiro1]

Hello @QFabian thank you for your answer.

Yes, I am talking about using the connector from script. 
I know of the possibility to choose some fields and then using the code generated. 

Well, that's not quite what I want. I want to be able to load all the fields of the json without specifically typing the json's structure.

Thank you!

[QFabian]

What you want is exactly what happens when you click on root, all the structure is selected as it was created, with the sub selects . It ius not just one table with all the fields, is about several tables related by keys according to the json structure. after that you could joins the tables in a sinlge structura using joins and resident with the resulting tables from the json execution

[nelsonloureiro1]

Hello @QFabian !

No, that is not what I want. 
I want to be able to select all the fields of a json without knowing it's structure in advance.

That is because I want to select from an endpoint that depending on a parameter value returns me slighly different json structures. 

Example:

For value A in the parameter's endpoint, it returns me the following json:

{
  "A": [
    {
      "b": "bbbbb"
    }
  ]
}

For value B in the parameter's endpoint, it returns me the following json:

{
  "B": [
    {
      "b": "bbbbb"
    }
  ]
}

So, I don't want to specify its structure but to select for example a field that I know will always be present (e.g.: field "b").

Thank you, regards, 

Nelson

[rwunderlich]

I would think you could do this with some combination of the Qlik JSON script functions like JsonGet.  Load the json as a string and then use JsonGet to get the values you are looking for. It appears the JsonGet path does not support wildcards so you will either have to dynamically generate the path parameter based on your endpoint or list all possible combinations in Coalesce. Something like:

TestData:
LOAD concat(j,'',RecNo()) as j Inline [
j
{
  "A": [
    {
      ""b": "bbbbb""
    }
  ]]
}
];

LOAD concat(j,'',RecNo()) as j Inline [
j
{
  "B": [
    {
      ""b": "bbbbb""
    }
  ]]
}
];

Results:
LOAD j,
 Coalesce(JsonGet(j, '/A/0/b'), JsonGet(j, '/B/0/b')) as b
Resident TestData; 
Drop Table TestData;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com