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

jerownimow — Tue, 12 Nov 2019 13:04:29 GMT

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.

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

manodwhb — Tue, 12 Nov 2019 13:23:54 GMT

@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-Data-Mapper/ta-p/38545

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

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

root — Tue, 19 Nov 2019 22:01:31 GMT

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.

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

Anonymous — Wed, 20 Nov 2019 02:39:01 GMT

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

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

jerownimow — Tue, 03 Dec 2019 10:50:40 GMT

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.

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

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

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

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

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

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