How to build the correct format of JSON structure in tWriteJSON field

[jerownimow]

I have this issue on tWriteJSONField wherein it generates an incomplete structure, maybe on my settings, can't find it!
Now I have this source data and conversion to JSON:

Scenario 1:
.-----------+--------------------+-------------+------------+--------------+-----------------+----------------+-------------+------------+---------+--------.
|                                                                                                 tLogRow_3                                                 |
|=----------+--------------------+-------------+------------+--------------+-----------------+----------------+-------------+------------+---------+-------=|
|userId     |ExpenseId		 |ExpenseItemId|refundAmount|totalExpense  |approverFirstName|approverLastName|userFirstName|userLastName|sessionId|lastPage|
|=----------+--------------------+-------------+------------+--------------+-----------------+----------------+-------------+------------+---------+-------=|
|123456789  |978654              |100643927004 |192.43      |0             |Meke             |Gossiengfao     |Majik        |Azerabas    |12345    |true    |
|123456789  |978654              |100643928004 |9.62        |0             |Meke             |Gossiengfao     |Majik        |Azerabas    |12345    |true    |
'-----------+--------------------+-------------+------------+--------------+-----------------+----------------+-------------+------------+---------+--------'

Scenario 1- tWriteJSONField Result:

{
    "AllExpenses": {
        "Expenses": {
            "userId": "123456789",
            "totalExpense": "0",
            "UserInfo": {
                "userLastName": "Azerabas",
                "userFirstName": "Majik",
                "approverLastName": "Gossiengfao",
                "approverFirstName": "Meke",
            },
            "ExpenseId": "978654",
            "ExpenseItems": [
                {
                    "refundAmount": "192.43",
                    "ExpenseItemId": "100643927004"
                },
                {
                    "refundAmount": "9.62",
                    "ExpenseItemId": "100643928004"
                }
			]
		}
    }
}

Scenario 2:
.-----------+--------------------+-------------+------------+--------------+-----------------+----------------+-------------+------------+---------+--------.
|                                                                                                 tLogRow_3                                                 |
|=----------+--------------------+-------------+------------+--------------+-----------------+----------------+-------------+------------+---------+-------=|
|userId     |ExpenseId		 |ExpenseItemId|refundAmount|totalExpense  |approverFirstName|approverLastName|userFirstName|userLastName|sessionId|lastPage|
|=----------+--------------------+-------------+------------+--------------+-----------------+----------------+-------------+------------+---------+-------=|
|123456789  |978654              |100643927004 |192.43      |0             |Meke             |Gossiengfao     |Majik        |Azerabas    |12345    |true    |
|123456789  |978654              |100643928004 |9.62        |0             |Meke             |Gossiengfao     |Majik        |Azerabas    |12345    |true    |
|123456789  |542367              |990387800041 |2.7         |0             |Meke             |Gossiengfao     |Majik        |Azerabas    |12345    |true    |
|123456789  |542367              |990387810043 |1.32        |0             |Meke             |Gossiengfao     |Majik        |Azerabas    |12345    |true    |
'-----------+--------------------+-------------+------------+--------------+-----------------+----------------+-------------+------------+---------+--------'

Scenario 2- tWriteJSONField Result: 
{
	"AllExpenses": [
		{
			"userId": "123456789",
			"totalExpense": "0",
			"UserInfo": {
				"userLastName": "Azerabas",
				"userFirstName": "Majik",
				"approverLastName": "Gossiengfao",
				"approverFirstName": "Meke",
			},
			"ExpenseId": "978654",
			"ExpenseItems": [
                {
                    "refundAmount": "192.43",
                    "ExpenseItemId": "100643927004"
                },
                {
                    "refundAmount": "9.62",
                    "ExpenseItemId": "100643928004"
                }
			]
		},
		{
			"userId": "123456789",
			"totalExpense": "0",
			"UserInfo": {
				"userLastName": "Azerabas",
				"userFirstName": "Majik",
				"approverLastName": "Gossiengfao",
				"approverFirstName": "Meke",
			},
			"ExpenseId": "542367",
			"ExpenseItems": [
				{
					"refundAmount": "2.7",
					"ExpenseItemId": "990387800041"
				},
				{
					"refundAmount": "1.32",
					"ExpenseItemId": "990387810043"
				}
			]
		}
	]
}

**For scenario 1 the JSON format is correct, but for scenario 2 the JSON format is a little bit wrong. What's lacking on 2nd scenario JSON output is the group  "Expenses": {

The component is configured as follow:

Now it only caters perfectly to the 1st scenario, but the 2nd scenario has a missing parent node as mentioned. Can you please suggest what's the best set-up to facilitate both requests?

Let me know if you need more info. Thank you so much.

[manodwhb]

@jerome29 , if you have Talend licensed version ,you can use Talend Data Mapper,it would be much easier to handle hierarchical format file. check the below links to know about talend   data mapper.

https://community.talend.com/t5/Design-and-Development/Talend-Data-Mapper-Intro-Part-1-What-is-the-D...

https://community.talend.com/t5/Design-and-Development/Talend-Data-Mapper-Intro-Part-2-Using-the-Dat...

[root]

The tWriteJsonField component seems a little lacking on features while working with complex json objects. I have tried using a custom Talend Component "tJSONDoc" ( https://github.com/jlolling/talendcomp_tJSONDoc)

The documentation can be a little confusing in the beginning.
HTH

PS: I am still learning this component, and so far, I have had multiple success overall using this component in creating complex objects.

[Anonymous]

Hi plumberg
If you have any question about tJSONDoc component, you can contact the author (jlolling) of the tJSONDoc component, I think jlolling is happy to help you, he is one of the top contributors on our community.

Regards
Shong

[jerownimow]

Thank you @mbocquet @manodwhb @shong  for all your responses. Appreciate it, I haven't tried the tJSONDoc, maybe next time.

What I did was to handle it via attributes and static values then I got the desired output.