How to format JSON Structure correctly?

jerownimow — Sat, 16 Nov 2024 03:49:10 GMT

Hi Folks,

Requesting your assistance on this JSON Problem. I have a job which extracts JSON from a Table.
Flow:
Process->tWriteJSONField->out

Here's a sample input:

invoiceId	lineNo	licenseId	itemGroupId	Step4_itemGroupId	maxRefundAmount	taxItemId
785001	13	28638001	708170001	708170001	1.00	4880001
785001	13	28638001	708170001	708170001	1.00	4883001
785001	8	28637001	708169001	708169001	4.00	4880001
785001	8	28637001	708169001	708169001	4.00	4883001

Expected Output:

{
	"body": {
		"invoices": [
			{
				"invoiceId": "785001",
				"items": [
					{
						"lineNo": "13",
						"licenseId": "28638001",
						"itemGroupId": "708170001"
					},
					{
						"itemGroupId": 708170001,
						"maxRefundAmount": 1.00,
						"taxItemId": 4880001
					},
					{
						"itemGroupId": 708170001,
						"maxRefundAmount": 1.00,
						"taxItemId": 4883001						
					},
					{
						"lineNo": "8",
						"licenseId": "28637001",
						"itemGroupId": "708169001"
					},
					{
						"itemGroupId": 708169001,
						"maxRefundAmount": 4.00,
						"taxItemId": 4880001						
					},
					{
						"itemGroupId": 708169001,
						"maxRefundAmount": 4.00,
						"taxItemId": 4883001
					}
				]
			}
		]
	}
}

Right now, I can only achieve this:

{
	"body": {
		"invoices": [
			{
				"invoiceId": "785001",
				"items": [
					{
						"lineNo": "13",
						"licenseId": "28638001",
						"itemGroupId": "708170001",
						"response": [
							{
								"itemGroupId": null,
								"maxRefundAmount": null
							},
							{
								"itemGroupId": null,
								"maxRefundAmount": null
							}
						]
					},
					{
						"lineNo": "8",
						"licenseId": "28637001",
						"itemGroupId": "708169001",
						"response": [
							{
								"itemGroupId": null,
								"maxRefundAmount": null
							},
							{
								"itemGroupId": null,
								"maxRefundAmount": null
							}
						]
					}
				]
			}
		]
	}
}

With this configuration on tExtractJSONFields:

Thank you so much!

Re: How to format JSON Structure correctly?

fdenis — Mon, 16 Dec 2019 16:09:19 GMT

hi,
your expected json format do not match your data format.
in your expected format lineno and itemGroupId are on the same column level but you did not want that.
so there is 2 way modify your data to have 6 lines.
or manual build your json using document type to store Json data.

Re: How to format JSON Structure correctly?

jerownimow — Tue, 17 Dec 2019 00:51:15 GMT

Hi @fdenis ,

Thank you for your feedback. Yes they should be on the same column level, and the other ItemGroupId is separate as they were from different source fields.

topic Re: How to format JSON Structure correctly? in Talend Studio

How to format JSON Structure correctly?

Re: How to format JSON Structure correctly?

Re: How to format JSON Structure correctly?