Five Stars

How to create this json output using tJSONDoc

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

Tags (2)
2 ACCEPTED SOLUTIONS

Accepted Solutions
Seventeen Stars

Re: How to create this json output using tJSONDoc

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)

 

Five Stars

Re: How to create this json output using tJSONDoc

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.

13 REPLIES
Seventeen Stars

Re: How to create this json output using tJSONDoc

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)

 

Five Stars

Re: How to create this json output using tJSONDoc

Many thanks,

Regarding issue1: you were right to point me to the multiple categoriesSmiley Happy 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

 

 

Seventeen Stars

Re: How to create this json output using tJSONDoc

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.

Five Stars

Re: How to create this json output using tJSONDoc

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).

 

Five Stars

Re: How to create this json output using tJSONDoc

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.

Five Stars

Re: How to create this json output using tJSONDoc

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

 

 

 

Capture2.JPG

 

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:

 

OverviewJob1.JPG

OverviewJob2.JPG

tFixedFlowInput1 and tFixflowInput5 contain the following data:

OverviewJob3.JPG

 

tMap2:

OverviewJob4.JPG

tFixedflowInput6 contain the values that I want to add:

OverviewJob5.JPG

 

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

Seventeen Stars

Re: How to create this json output using tJSONDoc

Hello,

 

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

Best regards

Jan Lolling

Five Stars

Re: How to create this json output using tJSONDoc

 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

Five Stars

Re: How to create this json output using tJSONDoc

Does anyone have an idea?

 

Thank in advance!

 

 

Seventeen Stars

Re: How to create this json output using tJSONDoc

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?

Five Stars

Re: How to create this json output using tJSONDoc

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

Re: How to create this json output using tJSONDoc

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.

Five Stars

Re: How to create this json output using tJSONDoc

Hi Hans,

 

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

 

Thnx!

 

Regards,

Remco