Happy Business Starts Here

JSON invoice format needs to change using Workflow

Highlighted

JSON invoice format needs to change using Workflow

This is a use case that has arrived in an actual Customer scenario, wherein the Customer is having a tough time to get the Invoice data in a particular JSON format. We need to transform data in a particular JSON- format so that it can be used to leverage integration with other 3rd party systems

 

The current Zuora format for this is: 

{
"InvoiceNumber":" INV012345",
"InvoiceId":"abcdefgh12345678",
"InvoiceItemId":"1",
"sku":"ONL-WBX",
"billingModel":"Month", "ratePlanName",
"ONL-STARTER-M",
"taxationItemId":"1",
"taxRate":"0.008"
}

{
"InvoiceNumber":" INV012345",
"InvoiceId":"abcdefgh12345678",
"InvoiceItemId":"1",
"sku":"ONL-WBX",
"billingModel":"Month",
"ratePlanName",
"ONL-STARTER-M",
"taxationItemId":"2",
"taxRate":"0.008"
}

{
"InvoiceNumber":" INV012345",
"InvoiceId":"abcdefgh12345678",
"InvoiceItemId":"2",
"sku":"ONL-AUD"
"billingModel":"Month",
"ratePlanName",
"ONL-STARTER-M",
"taxationItemId":"1",
"taxRate":"0.008"
}

{
"InvoiceNumber":" INV012345",
"InvoiceId":"abcdefgh12345678",
"InvoiceItemId":"2",
"sku":"ONL-AUD"
"billingModel":"Month",
"ratePlanName",
"ONL-STARTER-M",
"taxationItemId":"2",
"taxRate":"0.008"
}

 

The requirement is for the format to change to: 

{ "Invoice":
{ "InvoiceNumber":"INV012345",
"InvoiceId":"abcdefgh12345678",

"InvoiceItems":

[{ "InvoiceItemId":"1",
"sku":"ONL-WBX",
"billingModel":"Month",
"ratePlanName":"ONL-STARTER-M",
"taxationItems":
[{ "taxationItemId":"1",
"taxRate":"0.008" },
{ "taxationItemId":"2",
"taxRate":"0.008" } ] },

{ "InvoiceItemId":"2",
"sku":"ONL-AUD",
"billingModel":"Month",
"ratePlanName":"ONL-STARTER-M",
"taxationItem":
[{ "taxationItemId":"1",
"taxRate":"0.008" },
{ "taxationItemId":"2",
"taxRate":"0.008" } ] } ] } }

 

Our team is having a hard time making this change. Any input would be much appreciated. 

3 REPLIES 3
Highlighted
Valued Scholar

Re: JSON invoice format needs to change using Workflow

Here is a sample you can play around with. It gets close to what you are asking for.

 

SELECT account.name,
       invoice.invoicenumber,
       zip(array_agg(invoiceitem.id), array_agg(invoiceitem.balance))
FROM invoice
INNER JOIN invoiceitem ON invoice.invoiceNumber = 'INV012345'
AND invoiceitem.invoiceid = invoice.id
INNER JOIN account ON account.id = invoice.accountid
GROUP BY invoice.invoicenumber,
         account.name
ORDER BY invoice.invoicenumber
Highlighted
Valued Scholar

Re: JSON invoice format needs to change using Workflow

The other thing you can think about is having an extra step after you use the Data Query task. You can use the JSONTransform/JSONata task to transform the data into a more consumable format.

 

Both the Data Query and JSONTransform tasks are really powerful on their own, put them together and you get even more control.

Highlighted
Master

Re: JSON invoice format needs to change using Workflow

Late response but I would like to iterate the previous response.

 

In workflows, always do any complex data manipulation and transformation using the Data Query task which is SQL based data extraction tool. 

In case you haven't used already try it at Platform->Data query to play with the the query, save it if needed. Its a great, handy tool.

 

Once you have retrieved the what you need, transform using the JSONTransform. If you need to get an ancillary effect on the data then Liquid expressions (Liquid task) can also be beneficial to get the same custom JSON that you are looking for. 

Tags (1)