JSON extract fields

[Anonymous]

My JSON is looking like :

"result": {
    "a": {
    "id": "1",
     "champ": "aaa"},
    "b": {
    "id": "2",
     "champ": "bbb"},

    "c": {
    "id": "3",
     "champ": "ccc"},

      .

      .

   What i want is to have in my tLogRow something like :

|id|champ|

|1|aaa|

|2|bbb|

|3|ccc|

But i don't how to do it because a,b,c are different.

I tried with a tExtractJSONFields but it was not working.

I don't you know if you understand what i mean but ask me if you need more information. 

[Anonymous · Accepted solution]

This looks like a very similar issue to this one here (https://community.talend.com/t5/Design-and-Development/how-can-i-parsing-json-array-and-create-the-t...). The problem you have is that the key to your complex type is dynamic. This can cause a bit of a problem. I recommended a solution here using code. Take a look and see if you can see the parallels. You should be able to use the same sort of solution.

[nfz11]

The solution is pretty straightforward.

Here is the input and output of my test job:

[statistics] connecting to socket on port 3671
[statistics] connected
.-----------------------------------------------------------------------------------------------------------------------.
|                                                     #1. tLogRow_2                                                     |
+------+----------------------------------------------------------------------------------------------------------------+
| key  | value                                                                                                          |
+------+----------------------------------------------------------------------------------------------------------------+
| json | {"result": { "a": {"id": "1","champ": "aaa"},"b": {"id": "2","champ": "bbb"},"c": {"id": "3","champ": "ccc"}}} |
+------+----------------------------------------------------------------------------------------------------------------+

.--+-----.
|tLogRow_1|
|=-+----=|
|id|champ|
|=-+----=|
|1 |aaa  |
|2 |bbb  |
|3 |ccc  |
'--+-----'

[statistics] disconnected

Notice the Loop Xpath query in the component details in the below screenshot of the job.  By using the wildcard '*', it doesn't matter that your elements are named differently, e.g. 'a', 'b', 'c':

[Anonymous]

Thank you very much rhall_2_0, it helped me a lot. It's almost working ahah !

Here is my job :

I send a post request to an API and i get a JSON that looks like i said before.

The description of my components :

As you can see i have 10 rows after the first tExtractJSONFields and after the second only 5. I don't really understand why and i need the 10 rows.

And i get this error in the console : (sorry for french)

Error on line 1 of document : Le contenu des éléments doit inclure un balisage ou des caractères au format correct. Nested exception: Le contenu des éléments doit inclure un balisage ou des caractères au format correct.

It's printed 5 times then i get 5 rows in the tab of the tLogRow that works well.

Do you have any idea to resolve this ?

Thank you ! 

[Anonymous]

I noticed that you are using XPath in the second tExtractJSONFields component. I'm not sure whether this would cause any massive problems, but it's worth checking out. The other thing to try is to add a tLogRow after the first tExtractJSONFields component. This will print out the JSON that is extracted from the first tExtractJSONFields component. From this you should be able to identify why you are only getting half the rows processed in the second tExtractJSONFields component. If you struggle with this, just post the data you are getting back from the tLogRow (if it is safe for you to do so...ie no private data) and we should be able to help from there.

[nfz11]

Guys, please see my proposal earlier in the thread.  It does not require any custom Java and is simple and meets the output of the OP.  Maybe I am missing something, but I think that is all we need.

[Anonymous]

Hi nfz11,

I tried at first time your solution but it wasn't working. My problem was a bit more complicated indeed.

Thank you for trying anyway !

[nfz11]

---

@mhodent wrote:

Hi nfz11,

I tried at first time your solution but it wasn't working. My problem was a bit more complicated indeed.

Thank you for trying anyway !

---

Well my solution exactly matched your requirements in the OP.

[Anonymous]

Hi @nfz11,

Sorry I was away this week and only looked in occasionally. I've just looked at your solution and I agree it would work. I misunderstood the requirement and thought that the "a", "b" and "c" would be required as well (the dynamic content). This is why I used code. Both ways will work, so I have awarded you the solution as well.

Thanks for your contributions to the Community

Regards

Richard