How to create this json output using tJSONDoc

[hvanderborg]

Hi,

Using the tJSONDoc components from jlolling (release 9.0 20170409) I'm currently trying to create this output in Talend Open Studio for Data Integration v6.3.0:

 (Edited for multiple categories)

{
  "category": [ {
    "id": 0,
    "name": "string",
    "is_active": true,
    "custom_attributes": [
      {
        "attribute_code": "string",
        "value": "string"
      } ]
  }, {
    "id": 1,
    "name": "string",
    "is_active": true,
    "custom_attributes": [
      {
        "attribute_code": "string",
        "value": "string"
    } ]
  } ] 
}

My input is a database table containing all the categories (columns like id, name, etcetera all available). The json output should be 1 document containing all the categories. The custom_attributes are also simply some columns from the input table, in the output they do need to be placed within the "custom_attributes" as shown above.

I'm having trouble to find out which (tJSONDOC) components to use in which order to get the single json document containing output above. I did check the manual http://jan-lolling.de/talend/components/help/tJSONDoc.pdf but I'm not succeeding. Hope someone can help me getting into the right direction with the right order of components I should use.  

EDIT:

So far I'm using:

tJsonDocOpen_1-->OnSubjoOk-->tJDBCInput-->tJsonDocOputput and get this:

{
  "category" : [ {
    "id" : "430",
    "name" : "my category",
    "is_active" : "false"
  }, {
    "id" : "429",
    "name" : "my category 2",
    "is_active" : "false"
  },
....

Issue one: The category is followed by a [ . I've set JSON path for the parent to "$.category" unsure whether that is the right way (no issue here, for multiple categories the [ is of course needed, thanks jlolling for pointing me to the mistake)
Issue two: the custom_attributes. I can add columns within 'custom_attributes' by adding a tJSONDocOutput_2, but not in this format:

      {
        "attribute_code": "string",
        "value": "string"
      }

: 

[Anonymous]

Hi, for me based on the screenshot it is not quite clear where in your job you are writing the optionalData - here I would expect also a schema column "value" with the mentioned String as data. Looks pretty normal and easy. 

Could you tell us this detail?

[rsmits]

Hi,

I am writing the optionalData somewhere in the middle (tJSONDocOutput_5). I know how to put the fieldvalues under the mergeTriggerRecords, but I don't know how to move the lines under the orderlineitems with the tJSONDocOutput. 

Regards,

Remco

This is how it should be:

{
   "mergeTriggerRecordData":{
      "mergeTriggerRecords":[
         {
            "fieldValues":[
               "mdi1234@foobar.com"
            ],
            "optionalData":[
               {
                  "name":"ADDRESS1",
                  "value":"AddressStreet 17"
               },
               {
                  "name":"FIRST_NAME",
                  "value":"jim_1"
               },
               {
                  "name":"LAST_NAME",
                  "value":"smith_1"
               },
               {
                  "name":"OrderLineItems",
                  "value":[
                     {
                        "productID":"A12345",
                        "description":"Red Dress",
                        "quantity":5,
                        "price":10.00,
                        "extPrice":50.00
                     },
                     {
                        "productID":"B98765",
                        "description":"Yellow Shoes",
                        "quantity":2,
                        "price":35.00,
                        "extPrice":75.00
                     }
                  ]
               }
            ]
         },
         {
            "fieldValues":[
               "mdi.1234@foobarcorp.com"
            ],
            "optionalData":[
               {
                  "name":"FIRST_NAME",
                  "value":"jim_2"
               },
               {
                  "name":"LAST_NAME",
                  "value":"smith_2"
               }
            ]
         }
      ],
      "fieldNames":[
         "EMAIL_ADDRESS_"
      ]
   },
   "mergeRule":{
      "htmlValue":"H",
      "matchColumnName1":"EMAIL_ADDRESS_",
      "matchColumnName2":"null",
      "optoutValue":"O",
      "insertOnNoMatch":true,
      "defaultPermissionStatus":"OPTIN",
      "rejectRecordIfChannelEmpty":"E",
      "optinValue":"I",
      "updateOnMatch":"REPLACE_ALL",
      "textValue":"T",
      "matchOperator":"NONE"
   }
}

[Anonymous]

Hi, this is pretty easy!

You have the actual object for die "value" attribute somewhere prepared.

You can always send a JSON as String (in your previous posts it looks like you have it this way) and you can activate in the tJSONDocOutput the option "Is a JSON object" (in the attributes configuration) and this will cause the String value will be taken as real JSON and not set as textual value.

[rsmits]

Hi Hans,

Everything worked out! I have added an howto to my TalendHowTo

Thnx!

Regards,

Remco