Parsing JSON array based on values from another array

[Anonymous]

Hello Community,

So I have entire JSON data stored in DB. I am trying to Parse the following data:

[
  {
    "type": "ALO",
    "sessionInfo": {
      "test_id": 111,
      "test_type": 3
    },
    "data": {
      "id": 0,
      "name": "ABCDEF",
      "len": 4,
      "loopone": [
        {
          "id": 1,
          "name": "JKL",
          "ord": 23
        },
        {
          "id": 5,
          "name": "LMN",
          "ord": 24
        },
        {
          "id": 7,
          "name": "OPQ",
          "ord": 25
        },
        {
          "id": 8,
          "name": "RST",
          "ord": 26
        }        
      ],
      "looptwo": [
        {
          "id": 1,
          "name": "ZZ1",
          "start_id": 1,
          "end_id": 5
        },
        {
          "id": 2,
          "name": "ZZ2",
          "len": 15207,
          "start_id": 5,
          "end_id": 7
        }
      ]
    }
  }
]

What I have done currently is use a tDBInput -> tExtractJSONField1 with Loop Jsonpath Query set to "$", this extracts the type, sessionInfo and Data objects for me.

tExtractJSONField2 is looping over "$.data.loopone[*]" which extracts all fields from loopone

Now i am facing issue in tExtractJSONField3. In array "Looptwo", the "start_id" object represents "id" from "loopone". Since there are only two values present for "start_id" (1,5 for loopone ID), what I want is to show NULL for id "8" and get a total of 4 records as output, having null for values not present in looptwo. At the moment, I am getting 8 rows in output with "start_id" repeating.

Would appreciate if someone can give immediate response on this. Thanks in advance.

[Anonymous]

bump

[Anonymous · Accepted solution]

Hi OmarAmjad
My opinion is to extract data from array Looptwo first, and stored them to memory (tHashOutput) for used later.
Next, extract data from array Loopone, and do inner join or left outer join with the data from Looptwo, filter the data if needed.

Regards
Shong

[Anonymous]

Hi Shong,

That worked like a charm. Thanks !

I was trying to do it with a tReplicate at first but it didn't let me join as a lookup. Can you please explain why it doesn't work when I'm using same tDBInput to replicate and then later on join?