topic Re: Select * from JSON - REST Connector in App Development

Select * from JSON - REST Connector

nelsonloureiro1 — Tue, 03 Dec 2024 18:13:53 GMT

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

Re: Select * from JSON - REST Connector

QFabian — Wed, 04 Dec 2024 00:13:16 GMT

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;

Re: Select * from JSON - REST Connector

nelsonloureiro1 — Wed, 04 Dec 2024 09:01:36 GMT

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!

Re: Select * from JSON - REST Connector

QFabian — Wed, 04 Dec 2024 16:46:18 GMT

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

Re: Select * from JSON - REST Connector

nelsonloureiro1 — Thu, 05 Dec 2024 08:45:53 GMT

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

Re: Select * from JSON - REST Connector

rwunderlich — Mon, 23 Dec 2024 17:57:45 GMT

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