twriteJSONField loop and group

[BenG]

Hi Talend Community,

I work a lot with JSON files and REST API. I have a requirement where I would need some help.

I get data via a SQL table which looks like:

Address | Function | Number

1    | A    | 20

1    | B    | 20

1    | C    | 23

3    | A    | 21

4    | A    | 22

The JSON string which I need to send to the REST-API should look like:

[{

"paths":{

"Number": {

      "key": 20

    },

"Address": {

      "key": 1

    },

    "Function": [

      {

        "uniqueId": "A"

      },

      {

        "uniqueId": "B"

      }

    ]  

},

"paths":{

"Number": {

      "key": 23

    },

"Address": {

      "key": 1

    },

    "Function": [

      {

        "uniqueId": "C"

      }

    ]  

},

"paths":{

"Number": {

      "key": 21

    },

"Address": {

      "key": 3

    },

    "Function": [

      {

        "uniqueId": "A"

      }

    ]  

},

"paths":{

"Number": {

      "key": 22

    },

"Address": {

      "key": 4

    },

    "Function": [

      {

        "uniqueId": "A"

      }

    ]  

}

}]

I set the loop element on the Function and a group on the paths. But this gives me the following:

The paths tag is only once in the JSON and additionally a [ ] is added after paths.

[{

"paths":[{

"Number": {

      "key": 20

    },

"Address": {

      "key": 1

    },

    "Function": [

      {

        "uniqueId": "A"

      },

      {

        "uniqueId": "B"

      }

    ]  

},

{

"Number": {

      "key": 23

    },

"Address": {

      "key": 1

    },

    "Function": [

      {

        "uniqueId": "C"

      }

    ]  

},

{

"Number": {

      "key": 21

    },

"Address": {

      "key": 3

    },

    "Function": [

      {

        "uniqueId": "A"

      }

    ]  

},

{

"Number": {

      "key": 22

    },

"Address": {

      "key": 4

    },

    "Function": [

      {

        "uniqueId": "A"

      }

    ]  

}]

}]

Any ideas how to achieve this?

Thanks & Cheers

Ben

[Anonymous]

Hello @Benjamin Gnädig​ ,

Maybe you can create one additional column 'loop' with value 'x' , then setup it as Group By /Input Column for tWriteJSONField component

for more details , please refer to the below article

https://help.talend.com/r/en-US/8.0/processing/twritejsonfield-tmap-trowgenerator-trowgenerator-converting-rows-to-json-array-to

Best regards

Aiming

[BenG]

Hi Achen,

I did that already. That doesn't helped. I do it now with manual replacements. Not very nice.

I think this component has a lot of potential but is not there yet.

Thx

Ben