2 Replies Latest reply: Mar 5, 2018 9:43 AM by badri narayanan RSS

    Qliksense: Fetching child arrays from JSON data

    badri narayanan

      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]);

       

       

      [c]:

      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-s swuehl gwassenaar