Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cognosbadri
New Contributor II

Qliksense: Fetching child arrays from JSON data

We have requirement to fetch data from Mongo into Qliksense for which we use rest connection to fetch it.

Our data looks like below:

{"C1":"O",

"Arr1":[{"Arr1_C1":"IT","Arr1_C2":true,"Arr1_C3":"Test1"}],

"C2":"A",

"C3":"MIL",

"Arr2":[{"Arr2_C1":"2015-06-15","Arr2_C2":"2015-06-15","Arr2_C3":"Test","Arr2_1":[{"Arr2_1_C1":true,"Arr2_1_C2":1455236}],"Arr2_2":[]}],

"C4":"1001"}

Load Script:

RestConnectorMasterTable:

SQL SELECT

    C1,

    C2,

    C3,

    C4,

    "__KEY_root",

    (SELECT

        Arr1_C1,

        Arr1_C2,

        Arr1_C3,

        "__FK_pCR"

    FROM Arr1 FK "__FK_pCR"),

    (SELECT

        Arr2_C1,

        Arr2_C2

        Arr2_C3,

    "__FK_pcC"

FROM Arr2 FK "__FK_pCR")

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

[pCR]:

LOAD    Arr1_C1,

    Arr1_C2,

    Arr1_C3,

    [__FK_pCR] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_pCR]);

[pcC]:

LOAD    Arr2_C1,

    Arr2_C2,

    Arr2_C3,

        [__FK_pcC] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_pcC]);

:

LOAD    C1,

    C2,

    [__KEY_root] AS [__KEY_root]

RESIDENT RestConnectorMasterTable;

It works upto this but now i want to fetch elements from child array Arr2_1 present inside array Arr2. I tried to add another SQL statement inside

  (SELECT

        Arr2_C1,

        Arr2_C2

        Arr2_C3,

    "__FK_pcC"

FROM Arr2 FK "__FK_pCR")

but its not working,any suggestion to fetch elements from child array in qliksense? petter-sswuehlgwassenaar

Tags (2)
1 Solution

Accepted Solutions
cognosbadri
New Contributor II

Re: Qliksense: Fetching child arrays from JSON data

Got the solution, below is the sample script to get the child element.

Load Script:

RestConnectorMasterTable:

SQL SELECT

    C1,

    C2,

    C3,

    C4,

    "__KEY_root",

    (SELECT

        Arr1_C1,

        Arr1_C2,

        Arr1_C3,

        "__FK_pCR"

    FROM Arr1 FK "__FK_pCR"),

    (SELECT

        Arr2_C1,

        Arr2_C2

        Arr2_C3,

    "__KEY_pcC",

    "__FK_pcC",

      (SELECT

            "Arr2_1_C1",

            "Arr2_1_C2",

            "__FK_Arr2_1"

        FROM "Arr2_1" FK "__FK_Arr2_1")

FROM Arr2 PK "__KEY_pcC" FK "__FK_pcC")

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

2 Replies
cognosbadri
New Contributor II

Re: Qliksense: Fetching child arrays from JSON data

stalwar1‌ any suggestions for above issue?

cognosbadri
New Contributor II

Re: Qliksense: Fetching child arrays from JSON data

Got the solution, below is the sample script to get the child element.

Load Script:

RestConnectorMasterTable:

SQL SELECT

    C1,

    C2,

    C3,

    C4,

    "__KEY_root",

    (SELECT

        Arr1_C1,

        Arr1_C2,

        Arr1_C3,

        "__FK_pCR"

    FROM Arr1 FK "__FK_pCR"),

    (SELECT

        Arr2_C1,

        Arr2_C2

        Arr2_C3,

    "__KEY_pcC",

    "__FK_pcC",

      (SELECT

            "Arr2_1_C1",

            "Arr2_1_C2",

            "__FK_Arr2_1"

        FROM "Arr2_1" FK "__FK_Arr2_1")

FROM Arr2 PK "__KEY_pcC" FK "__FK_pcC")

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

Community Browser