Parsing JSON with tExtractJSONFields

Anonymous — Mon, 16 Dec 2019 16:59:52 GMT

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.

Re: Parsing JSON with tExtractJSONFields

Anonymous — Fri, 20 Dec 2019 05:51:41 GMT

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

topic Parsing JSON with tExtractJSONFields in Talend Studio

Parsing JSON with tExtractJSONFields

Re: Parsing JSON with tExtractJSONFields