How to format JSON Structure correctly?

[jerownimow]

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!

[fdenis]

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.

[jerownimow]

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.