topic Re: How to create this json output using tJSONDoc in Talend Studio

How to create this json output using tJSONDoc

hvanderborg — Mon, 08 May 2017 20:11:28 GMT

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"
      }

Re: How to create this json output using tJSONDoc

Anonymous — Wed, 10 May 2017 00:02:39 GMT

I am a bit confused about your desired output and what you provide as input.

First of all, please take care you use the latest release: Download it best way from Github: https://github.com/jlolling/talendcomp_tJSONDoc/releases

Your output contains only one array and this looks like the custom_attributes but you have obviously more than one category!

I guess you have more than one category and every category has more than one custom_attributes. Is this correct?

How does your input looks like?

You can solve this job in various ways, If you can provide your input I can complete the examples:

1. You can do this:

tJSONDocOpen -OnSubjobOk->

tJDBCInput --flow--> tJSONDocOutput_1 (for categories) --flow--> tHashOutput --iterate--> tHashInput --flow--> tJSONDocOutput_2 (for custom_attributes)

Here take care tJSONDocOutput_2 is referencing tJSONDocOutput_1

2. The better way - level by level

tJSONDocOpen -OnSubjobOk->

tJDBCInput_1 (only categories) --flow--> tJSONDocOutput_1 (for categories only) (take care the id is set as key)

tJDBCInput_2 (only custom_attributes + category_id as foreign-key field + use the SQL-Key-List oftJSONDocOutput_1 in where condition) --flow--> tJSONDocOutput_2 (for custom_attr only) (take care the category_id is set as foreign-key field, it is not necessary to check the use option for this column)

Re: How to create this json output using tJSONDoc

hvanderborg — Thu, 25 May 2017 18:05:13 GMT

Many thanks,

Regarding issue1: you were right to point me to the multiple categories so issue 1 was non existing.

Regarding issue2:

solving the custom attributes, I tried your 'the better way' and it seems that fully solved my issue.

in tJSONDocOutput_ 2 I have used these settings:

'Parent JSON Document': tJSONDocOutput_1

checked Use foreign key column to address the parent nod.

Fireign key column: id

JSON path for the parent "custom_attributes"

Output structure: 'Array of new created json objects with the schema columns as attributes'

then selected 'use column' for all custom attributes

Also I've used the sql list in where clause you pointed out.

It seems you have provided exactly the right clues I needed

EDIT, I was too quick thinking it was solved:

I now have this:

{
  "category": [ {
    "id": 0,
    "name": "string",
    "is_active": true,
    "custom_attributes": [
      {
        "custom_attribute_abc": "string with value of this attribute", 
        "custom_attribute_xyz": "string with value of this attribute" 
    } ] 
 }, ...

So I still have the challenge to get to:

{
  "category": [ {
    "id": 0,
    "name": "string",
    "is_active": true,
    "custom_attributes": [
      {
        "attribute_code": "custom_attribute_abc", 
        "value": "string with value of this attribute" 
      },{ 
        "attribute_code": "custom_attribute_xyz", 
        "value": "string with value of this attribute" 
      } ] 
   }, {....

To stick to the example above: my input rows have the columns: id, custom_attribute_abc, custom_attribute_xyz

Re: How to create this json output using tJSONDoc

Anonymous — Thu, 01 Jun 2017 18:04:46 GMT

Ok, in the case you know the custom attribute names it is easy, you can simply get dynamically named attributes by using the alternative attribute name field and set here a globalMap variable.

In case you do not know which attribute you will get, I will have to provide a functionality. I have actually a good idea how to solve this. Please let me some days and I will provide a solution.

Re: How to create this json output using tJSONDoc

hvanderborg — Thu, 08 Jun 2017 19:51:54 GMT

EDIT: I think I know the attribute names beforehand, not 100% just yet.

Removed an example which was not dynamic (in the way meant by you).

Re: How to create this json output using tJSONDoc

hvanderborg — Fri, 23 Jun 2017 19:17:40 GMT

I can still move forward with known attributes. Although easy, I don't see the design just yet for known attribute names. I do know where to set an 'alternative name' in tJSONDocOutput and how to use a variable.

So my input table for example could be this:

product_id customattributeABC custom_attribute_DEF custom_attribute_XYZ

product1 yellow 1 17,3

product2 purple 0 99,0

product3 blue 1 2,7

So output needs to be: (showing only the custom_attributes part for product1)

"custom_attributes": [
      {
        "attribute_code": "custom_attribute_ABC", 
        "value": "yellow" 
      },{ 
        "attribute_code": "custom_attribute_DEF", 
        "value": 1 
      },{ 
        "attribute_code": "custom_attribute_XYZ", 
        "value": 17,3 
 } ]

What should my design look like?
I know the table comes from tJDBCInput, output should likely be tJSONDocOutput with used columns 'attribute_code' and 'value' and the 'Alternative name' shoud be a variable for the column 'attribute_code'. But the rest of the flow is unclear to me just yet.

EDIT, found a solution:

Solved it this way:

tJDBCInput-->tUnpivotRow (by wzawwin)-->tJSONDocOutput

In tUnpivotRow I indicated the product_id as 'Row key'. And in tJSONDocOutput I've set an 'Alternative name' for 'pivot_key' (attribute_code) and 'pivot_value' (value). As far as I can see now this brings the expected result.

Re: How to create this json output using tJSONDoc

rsmits — Mon, 25 Sep 2017 22:48:26 GMT

I'm also working with the tJSON components and succeeded to create the following JSON string:

The only thing that I want to add is replacing the null value of the OrderLineItems with the following value:

{
"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}]"
}

* I'm not going to use the same attributes, but the attributes below (tFixedFlowInput6).

This is how my job looks like right now:

tFixedFlowInput1 and tFixflowInput5 contain the following data:

tMap2:

tFixedflowInput6 contain the values that I want to add:

I tried different things but they did not give me the desired result. Any suggestions?

Re: How to create this json output using tJSONDoc

Anonymous — Mon, 25 Sep 2017 23:22:12 GMT

Hello,

sorry I am unable to spot tFixedFlowInput_6 in your job. Perhaps you send an update of the screenshot?

Best regards

Jan Lolling

Re: How to create this json output using tJSONDoc

rsmits — Tue, 26 Sep 2017 00:09:04 GMT

Hi Jan,

I have added a screenshot of this component before I deleted it. I was trying to add this data to the tJSONDocOutput_5 (bases on emailaddress), but I didn't knew how to loop the tFlowFixedInput_6 data into the value field (it can also contain 2 or more rows).

Thanks for your help!

Regards,

Remco

Re: How to create this json output using tJSONDoc

rsmits — Wed, 27 Sep 2017 22:25:06 GMT

Does anyone have an idea?

Thank in advance!

Re: How to create this json output using tJSONDoc

Anonymous — Wed, 27 Sep 2017 22:31:18 GMT

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?

Re: How to create this json output using tJSONDoc

rsmits — Wed, 27 Sep 2017 23:52:35 GMT

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"
   }
}

Re: How to create this json output using tJSONDoc

Anonymous — Thu, 28 Sep 2017 00:01:52 GMT

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.

Re: How to create this json output using tJSONDoc

rsmits — Sun, 22 Oct 2017 19:27:48 GMT

Hi Hans,

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

Thnx!

Regards,

Remco