Parsing JSON with tExtractJSONFields

[Anonymous]

Hi all,

I try without success to do what i expect ...

I receive an XLM structured like this :

Informations are : email, Client ID, count clicks, count open, differents links he clicks :

[
{"recipient":
{"email":"a.cli1@mail.com",
"fields":{"CONTACT_ID":"ID_CLI1"}
}
,"clicks":26,
"opens":44,
"links":{"http://MySite1.com":2,
"http://MySite2.com":4,
"{{webcopy}}":19
}
}
{"recipient":
{"email":"a.cli2@mail.com",
"fields":{"CONTACT_ID":"ID_CLI2"}
}
,"clicks":2,
"opens":1,
"links":{"http://MySite1.com":22
}
}
{"recipient":
{"email":"a.cli3@mail.com",
"fields":{"CONTACT_ID":"ID_CLI3"}
}
,"clicks":2,
"opens":3
}
{"recipient":
{"email":"a.cli4@mail.com",
"fields":{"CONTACT_ID":"ID_CLI4"}
}
,"clicks":4,
"opens":4,
"links":{"http://MySite4.com":1
}
}
]

What i expect, it's an outuput like :

Email                          IdClient        open   MySite1.com    Mysite2.com      Mysite4.com    webcopy

a.cli1@mail.com         ID_CLI1          44               2                         4                                              19

a.cli2@mail.com         ID_CLI2            1             22                        

a.cli3@mail.com         ID_CLI3            3

a.cli4@mail.com         ID_CLI4            4                                                                4

But first with the xml, I tried several methods with tExtractJSONFields, but still without success...

Thanks for your help.

[Anonymous]

Hi
You need two tExtractJsonFields components to extract different information from the input string json data.
one tExtractJsonFields (set xpath loop element as: $[*].recipient) extract these information:
email, Client ID, count clicks, count open,
another tExtractJsonFields (set xpath loop element as: $[*].links) extract email, links and webcopy.
And then, merge the two output. The job design looks like:
...tReplicated--tExtractJsonFields1--tHashOutput1
                     --tExtractJsonFields2--tHashOutput2
|onsubjobok
tHashInput1---main---tMap....>
                                     |lookup
                              tHashInput2

On tMap, do an inner join base on email column to merge all columns from main flow and lookup flow.

Regards
Shong