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

  • Data Integration
Tags (2)
1 ACCEPTED SOLUTION

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)

 

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

 

Four 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