JSON extract fields

Six Stars

JSON extract fields

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. 


Accepted Solutions
Community Manager

Re: JSON extract fields

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.

View solution in original post

Nine Stars

Re: JSON extract fields

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':

json-parse.png

--
Please give Kudos and mark topics as solved where appropriate.

View solution in original post


All Replies
Community Manager

Re: JSON extract fields

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.

View solution in original post

Nine Stars

Re: JSON extract fields

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':

json-parse.png

--
Please give Kudos and mark topics as solved where appropriate.

View solution in original post

Six Stars

Re: JSON extract fields

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

Here is my job :

job2.PNG

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

The description of my components :

extractjson1.PNG

 

 

extractjson2.PNG

 

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 ! 

 

 

Community Manager

Re: JSON extract fields

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.

Nine Stars

Re: JSON extract fields

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.

--
Please give Kudos and mark topics as solved where appropriate.
Six Stars

Re: JSON extract fields

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 ! Smiley Happy

Nine Stars

Re: JSON extract fields


@martintin11 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 ! Smiley Happy


Well my solution exactly matched your requirements in the OP.

--
Please give Kudos and mark topics as solved where appropriate.
Community Manager

Re: JSON extract fields

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

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog