topic Re: Best practices for splitting complex json in Talend Studio

Best practices for splitting complex json

BooWseR — Wed, 13 Mar 2019 07:15:13 GMT

Hi guys,

i'm pretty new in talend and imported my first JSON. The problem is that this file is very complex and I don't know exactly how to read it right.

Let me give you an example:

  "data": [
    {
      "EANCode": "1234567",
      "Info": {
        "active": true,
        "articleID": 1234,
        "articleKey": "1234",
      },
      "attributes": [
        {
          "Key": "date",
          "Value": "2011-11-11"
        },
        {
          "Key": "color",
          "Value": "red"
        },
        {
          "Key": "Key@Aufmachung",
          "Value": "A12    @01"
        },
        {
          "Key": "Key@TkgNr",
          "Value": "A1@123"
        }
      ],
      "b2csku": "12345678

It's a huge file, around 22k lines, ~500 per item. My biggest problem is the value in "Key" and "Value" and the fact that they always contain different amounts of information which is why I can't 'hardcode' it. My current approach is a simple string manipulation to extract the key value values. My idea was to cut off the first characters in tmap, but I don't think the approach is very successful either. It also doesn't help me with the variable amount of data in "attributes". Do you have any tips or ideas for me?

Best regards,

BooWseR

Re: Best practices for splitting complex json

Anonymous — Thu, 14 Mar 2019 00:02:13 GMT

This post might give you a few ideas

https://community.talend.com/t5/Design-and-Development/downloading-JSON-in-structured-format/m-p/152303/highlight/true#M93081

Re: Best practices for splitting complex json

BooWseR — Tue, 26 Mar 2019 04:15:26 GMT

Thanks for your answer. I have now iterated the JSON in several runs.

This works quite well so far, but now I have problems merging the data into columns. For example, I use tMap to write the values into my own columns:

(data.attributes.Key.equals("date")) ? data.attributes.Value : null --> date

Now my output looks like:

EAN	date	color	Key@Aufmachung	Key@TkgNr
1234567	2011-11-11	null	null	null
1234567	null	red	null	null
1234567	null	null	A12 @01	null
1234567	null	null	null	A1@123

I would now like to merge the rows, but I have no idea how. I tried tDenormalize and select all columns except EAN, but it doesnt effect my output. I also tried tmemorizeRows, but even without success. What am I doing wrong?

Re: Best practices for splitting complex json

Anonymous — Tue, 26 Mar 2019 09:22:33 GMT

A simple way to do this (if you know that there is one row per EAN) is to use the tAggregateRow component. Group by the EAN column and output the other columns using the "first" function and tick the "Ignore null values" tickbox.