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 · Accepted solution]

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)

[hvanderborg]

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

[Anonymous]

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.

[hvanderborg]

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

[hvanderborg]

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.

[rsmits]

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?

[Anonymous]

Hello,

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

Best regards

Jan Lolling

[rsmits]

 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

[rsmits]

Does anyone have an idea?

Thank in advance!