Iterate JSON and parse into struct

RE: Iterate JSON and parse into struct

Hi Ummair!

Giving a subset of the data...

String json = "[
    {
      \"internalId\": 1,
      \"number\": \"1\",
      \"name\": \"One\",
      \"mainContact\": {
        \"contactId\": 1,
        \"name\": \"Someone\",
        \"attention\": \"ok\",
        \"email\": \"someone@somewhere.com\",
        \"web\": \"https://www.somesite.com\"
        }
  	},
    {
      \"internalId\": 2,
      \"number\": \"2\",
      \"name\": \"Two\",
      \"mainContact\": {
        \"contactId\": 2,
        \"name\": \"Sometwo\",
        \"attention\": \"okidoki\",
        \"email\": \"sometwo@somewhere.com\",
        \"web\": \"https://www.someothersite.com\"
        }
  	}
  ]";

And given the structs necessary to populate the data:

#setLanguageLevel 3;

struct MainContact {
	Integer contactId;
  String name;
  String attention;
  String email;
  String web;
};

struct CustomerInfo {
  Integer internalId;
  String number;
  String name;
  MainContact mainContact;
};

struct CustomerInfoCollection {
  CustomerInfo [] customers;
};

I thought it would be great if it was as easy as adding a fromJson method like this:

struct CustomerInfoCollection {
  CustomerInfo [] customers;
  
  // Wishful thinking!!!
  Void fromJson(String json) {
    this.fromXMLNode(parseJSON(json));
  }
};

Turns out though that neither parseJSON or parseJSON2 in fromXMLNode will populate the array without more mapping knowledge, etc.

 Maybe there's a better way, but the following code is what I came up with as a work around. Just iterate over the XMLNodes and manually populate the array.

#setLanguageLevel 3;

struct MainContact {
	Integer contactId;
  String name;
  String attention;
  String email;
  String web;
};

struct CustomerInfo {
  Integer internalId;
  String number;
  String name;
  MainContact mainContact;
};

struct CustomerInfoCollection {
  CustomerInfo [] customers;
  
  Void fromJson(String json) {
    XMLNode json = parseJSON2(json);
    XMLNode[] nodes = json.getChildren();
    
    for(Integer i = 0; i < nodes.length(); i++)
    {
      CustomerInfo ci;
      ci.fromXMLNode(nodes[i]);
      this.customers.pushBack(ci);
    }
  }
};

CustomerInfoCollection customerCollection;
customerCollection.fromJson(json);

printLine(customerCollection.customers.length().toString());

CustomerInfo[] customers = customerCollection.customers;
for(Integer i = 0; i < customers.length(); i++)
{
  printLine("Customer name: " + customers[i].name);
  printLine("Customer contact name: " + customers[i].mainContact.name);
}

// prints out:
//**********************************
// 2
// Customer name: One
// Customer contact name: Someone
// Customer name: Two
// Customer contact name: Sometwo

Obviously you will want to put in some error handling, etc. 

Hope this helps!

RE: Iterate JSON and parse into struct

Fantastic Tony!!

Its this getChildren function I wanted to avoid using, but you have simplified it really good. Will try it out tomorrow.

Thanks! 

RE: Iterate JSON and parse into struct

Hi guys,

You need to make sure the JSON and the Structs are exactly the same. If you have CustomerInfoCollection which contains an array of customers, then your JSON also needs to contains an array named "customers". The following code, modified from Tony's example, works:

#setLanguageLevel 3;

String json = "{ \"customers\": [
    {
      \"internalId\": 1,
      \"number\": \"1\",
      \"name\": \"One\",
      \"mainContact\": {
        \"contactId\": 1,
        \"name\": \"Someone\",
        \"attention\": \"ok\",
        \"email\": \"someone@somewhere.com\",
        \"web\": \"https://www.somesite.com\"
        }
  	},
    {
      \"internalId\": 2,
      \"number\": \"2\",
      \"name\": \"Two\",
      \"mainContact\": {
        \"contactId\": 2,
        \"name\": \"Sometwo\",
        \"attention\": \"okidoki\",
        \"email\": \"sometwo@somewhere.com\",
        \"web\": \"https://www.someothersite.com\"
        }
  	}
  ] }";

struct MainContact {
	Integer contactId;
  String name;
  String attention;
  String email;
  String web;
};

struct CustomerInfo {
  Integer internalId;
  String number;
  String name;
  MainContact mainContact;
};

struct CustomerInfoCollection {
  CustomerInfo [] customers;
  
  // Wishful thinking!!! And working as well :-) 
  Void fromJson(String json) {
    XMLNode root = parseJSON(json);
    this.fromXMLNode(root);
  }
};

CustomerInfoCollection c;
c.fromJson(json);
for (Integer i = 0; i < c.customers.length(); i++)
	printLine(c.customers[i].internalId.toString() + ", " + c.customers[i].name + ", " + c.customers[i].mainContact.name);

In general: if your JSON is an array at the outermost level, then you need to call fromXMLNode() on an array. If your JSON is an object at the outermost level, then you need to call fromXMLNode() on a struct instance.

Sverre

RE: Iterate JSON and parse into struct

Yeah, that's what I meant by "more mapping knowledge...".

It's not often external sources can be changed, so I chose to "assume" the source json couldn't be modified.

If it can be altered to include the customers array property, then this is definately the way to go! 

Cheers!

RE: Iterate JSON and parse into struct

Yes, but if you cannot change the JSON, then you need to change your structure, or where in the structure you try to deserialize. So, if you changed your code from

Void fromJson(String json) {
  this.fromXMLNode(parseJSON(json));
} 

to

Void fromJson(String json) {
  this.customers.fromXMLNode(parseJSON(json));
} 

then it would also have worked (since this.customers is an array of CustomerInfo, just like the JSON).

Sverre

RE: Iterate JSON and parse into struct

I seem like I always end up in a dead end every time I try to work with XML and get it in struct form.
It looks like I either lack some understanding here. Is there a red thread I should look for when forinstance making my structs from xml\json ?

This time the XML looks like this: 

<Invoicelist>
	<Invoice>
		<CustomerNo>121405</CustomerNo>
		<Customer>TEST</Customer>
		<DeliveryAddressNo>199</DeliveryAddressNo>
		<DeliveryAddress1>TEST AS</DeliveryAddress1>
		<DeliveryAddress2>Stokkamyrveien 22</DeliveryAddress2>
		<DeliveryPostCode>4313</DeliveryPostCode>
		<DeliveryPostOffice>SANDNES</DeliveryPostOffice>
		<InvoiceAddressNo>2889</InvoiceAddressNo>
		<InvoiceAddress1>TEST AS</InvoiceAddress1>
		<InvoiceAddress2>Stokkamyrveien 22</InvoiceAddress2>
		<InvoicePostCode>4313</InvoicePostCode>
		<InvoicePostOffice>SANDNES</InvoicePostOffice>
		<InvoiceNo>39</InvoiceNo>
		<OrderNo>375</OrderNo>
		<InvoiceDate>13.11.2020</InvoiceDate>
		<OrderDate>13.11.2020</OrderDate>
		<DeliveryDate>13.11.2020</DeliveryDate>
		<TotalNetAmount>0</TotalNetAmount>
		<TotalGrossAmount>0</TotalGrossAmount>
		<TotalDelivered>0</TotalDelivered>
		<OrderType>100</OrderType>
		<ChainNo>0</ChainNo>
		<TypeOfChain>2</TypeOfChain>
		<EmployeeNo>0</EmployeeNo>
		<ZUsrTilPeriode>0</ZUsrTilPeriode>
		<OrderStatus>1030</OrderStatus>
		<DepNo>0</DepNo>
		<ProjectNo>0</ProjectNo>
		<NoInvoiceLines>2</NoInvoiceLines>
		<InvoiceLine>
			<ArticleNo>3210</ArticleNo>
			<Name>Articlename</Name>
			<NetPrice>699</NetPrice>
			<Quantity>12</Quantity>
			<Invoiced>12</Invoiced>
			<Remainder>0</Remainder>
			<Amount>8388</Amount>
			<DeliveryDate>13.11.2020</DeliveryDate>
			<LotEntryNo>0</LotEntryNo>
			<LotNo>0</LotNo>
			<SeqNo>10010000</SeqNo>
			<UniqueID>243</UniqueID>
		</InvoiceLine>
		<InvoiceLine>
			<Name>Vår ref: 112408, Deres ref:</Name>
			<NetPrice>0</NetPrice>
			<Quantity>0</Quantity>
			<Invoiced>0</Invoiced>
			<Remainder>0</Remainder>
			<Amount>0</Amount>
			<DeliveryDate>01.01.1970</DeliveryDate>
			<LotEntryNo>0</LotEntryNo>
			<LotNo>0</LotNo>
			<SeqNo>10020000</SeqNo>
			<UniqueID>244</UniqueID>
		</InvoiceLine>
	</Invoice>
	<Invoice>
		<CustomerNo>121405</CustomerNo>
		<Customer>TEST</Customer>
		<DeliveryAddressNo>199</DeliveryAddressNo>
		<DeliveryAddress1>c/o FSS Revisjon</DeliveryAddress1>
		<DeliveryAddress2>Stokkamyrveien 22</DeliveryAddress2>
		<DeliveryPostCode>4313</DeliveryPostCode>
		<DeliveryPostOffice>SANDNES</DeliveryPostOffice>
		<InvoiceAddressNo>2889</InvoiceAddressNo>
		<InvoiceAddress1>TEST AS</InvoiceAddress1>
		<InvoiceAddress2>Stokkamyrveien 22</InvoiceAddress2>
		<InvoicePostCode>4313</InvoicePostCode>
		<InvoicePostOffice>SANDNES</InvoicePostOffice>
		<InvoiceNo>39</InvoiceNo>
		<OrderNo>374</OrderNo>
		<InvoiceDate>13.11.2020</InvoiceDate>
		<OrderDate>13.11.2020</OrderDate>
		<DeliveryDate>13.11.2020</DeliveryDate>
		<TotalNetAmount>16776</TotalNetAmount>
		<TotalGrossAmount>16776</TotalGrossAmount>
		<TotalDelivered>16776</TotalDelivered>
		<OrderType>100</OrderType>
		<ChainNo>0</ChainNo>
		<TypeOfChain>2</TypeOfChain>
		<EmployeeNo>0</EmployeeNo>
		<ZUsrTilPeriode>0</ZUsrTilPeriode>
		<OrderStatus>1030</OrderStatus>
		<DepNo>0</DepNo>
		<ProjectNo>0</ProjectNo>
		<NoInvoiceLines>2</NoInvoiceLines>
		<InvoiceLine>
			<ArticleNo>3210</ArticleNo>
			<Name>Medlemskap</Name>
			<NetPrice>699</NetPrice>
			<Quantity>12</Quantity>
			<Invoiced>12</Invoiced>
			<Remainder>0</Remainder>
			<Amount>8388</Amount>
			<DeliveryDate>13.11.2020</DeliveryDate>
			<LotEntryNo>0</LotEntryNo>
			<LotNo>0</LotNo>
			<SeqNo>10010000</SeqNo>
			<UniqueID>241</UniqueID>
		</InvoiceLine>
		<InvoiceLine>
			<Name>Vår ref: 107344, Deres ref:</Name>
			<NetPrice>0</NetPrice>
			<Quantity>0</Quantity>
			<Invoiced>0</Invoiced>
			<Remainder>0</Remainder>
			<Amount>0</Amount>
			<DeliveryDate>01.01.1970</DeliveryDate>
			<LotEntryNo>0</LotEntryNo>
			<LotNo>0</LotNo>
			<SeqNo>10020000</SeqNo>
			<UniqueID>242</UniqueID>
		</InvoiceLine>
	</Invoice>
	<Invoice>
		<CustomerNo>121405</CustomerNo>
		<Customer>TEST</Customer>
		<DeliveryAddressNo>199</DeliveryAddressNo>
		<DeliveryAddress1>Test</DeliveryAddress1>
		<DeliveryAddress2>Stokkamyrveien 22</DeliveryAddress2>
		<DeliveryPostCode>4313</DeliveryPostCode>
		<DeliveryPostOffice>SANDNES</DeliveryPostOffice>
		<InvoiceAddressNo>2889</InvoiceAddressNo>
		<InvoiceAddress1>TEST AS</InvoiceAddress1>
		<InvoiceAddress2>Stokkamyrveien 22</InvoiceAddress2>
		<InvoicePostCode>4313</InvoicePostCode>
		<InvoicePostOffice>SANDNES</InvoicePostOffice>
		<InvoiceNo>38</InvoiceNo>
		<OrderNo>373</OrderNo>
		<InvoiceDate>13.11.2020</InvoiceDate>
		<OrderDate>13.11.2020</OrderDate>
		<DeliveryDate>13.11.2020</DeliveryDate>
		<TotalNetAmount>8388</TotalNetAmount>
		<TotalGrossAmount>8388</TotalGrossAmount>
		<TotalDelivered>8388</TotalDelivered>
		<OrderType>100</OrderType>
		<ChainNo>0</ChainNo>
		<TypeOfChain>2</TypeOfChain>
		<EmployeeNo>1</EmployeeNo>
		<ZUsrTilPeriode>0</ZUsrTilPeriode>
		<OrderStatus>1030</OrderStatus>
		<DepNo>10</DepNo>
		<ProjectNo>0</ProjectNo>
		<NoInvoiceLines>2</NoInvoiceLines>
		<InvoiceLine>
			<ArticleNo>3210</ArticleNo>
			<Name>Articlename</Name>
			<NetPrice>699</NetPrice>
			<Quantity>12</Quantity>
			<Invoiced>12</Invoiced>
			<Remainder>0</Remainder>
			<Amount>8388</Amount>
			<DeliveryDate>13.11.2020</DeliveryDate>
			<LotEntryNo>0</LotEntryNo>
			<LotNo>0</LotNo>
			<SeqNo>10030000</SeqNo>
			<UniqueID>239</UniqueID>
		</InvoiceLine>
		<InvoiceLine>
			<Name>Vår ref: 112408, Deres ref:</Name>
			<NetPrice>0</NetPrice>
			<Quantity>0</Quantity>
			<Invoiced>0</Invoiced>
			<Remainder>0</Remainder>
			<Amount>0</Amount>
			<DeliveryDate>01.01.1970</DeliveryDate>
			<LotEntryNo>0</LotEntryNo>
			<LotNo>0</LotNo>
			<SeqNo>10040000</SeqNo>
			<UniqueID>240</UniqueID>
		</InvoiceLine>
	</Invoice>
	<Invoice>
		<CustomerNo>121405</CustomerNo>
		<Customer>TEST</Customer>
		<DeliveryAddressNo>199</DeliveryAddressNo>
		<DeliveryAddress1>Test</DeliveryAddress1>
		<DeliveryAddress2>Stokkamyrveien 22</DeliveryAddress2>
		<DeliveryPostCode>4313</DeliveryPostCode>
		<DeliveryPostOffice>SANDNES</DeliveryPostOffice>
		<InvoiceAddressNo>2889</InvoiceAddressNo>
		<InvoiceAddress1>TEST AS</InvoiceAddress1>
		<InvoiceAddress2>Stokkamyrveien 22</InvoiceAddress2>
		<InvoicePostCode>4313</InvoicePostCode>
		<InvoicePostOffice>SANDNES</InvoicePostOffice>
		<InvoiceNo>37</InvoiceNo>
		<OrderNo>372</OrderNo>
		<InvoiceDate>13.11.2020</InvoiceDate>
		<OrderDate>13.11.2020</OrderDate>
		<DeliveryDate>13.11.2020</DeliveryDate>
		<TotalNetAmount>699</TotalNetAmount>
		<TotalGrossAmount>699</TotalGrossAmount>
		<TotalDelivered>699</TotalDelivered>
		<OrderType>100</OrderType>
		<ChainNo>0</ChainNo>
		<TypeOfChain>2</TypeOfChain>
		<EmployeeNo>1</EmployeeNo>
		<ZUsrTilPeriode>0</ZUsrTilPeriode>
		<OrderStatus>1030</OrderStatus>
		<DepNo>10</DepNo>
		<ProjectNo>0</ProjectNo>
		<NoInvoiceLines>2</NoInvoiceLines>
		<InvoiceLine>
			<ArticleNo>3210</ArticleNo>
			<Name>Medlemskap</Name>
			<NetPrice>699</NetPrice>
			<Quantity>1</Quantity>
			<Invoiced>1</Invoiced>
			<Remainder>0</Remainder>
			<Amount>699</Amount>
			<DeliveryDate>13.11.2020</DeliveryDate>
			<LotEntryNo>0</LotEntryNo>
			<LotNo>0</LotNo>
			<SeqNo>10030000</SeqNo>
			<UniqueID>237</UniqueID>
		</InvoiceLine>
		<InvoiceLine>
			<Name>Vår ref: 107344, Deres ref:</Name>
			<NetPrice>0</NetPrice>
			<Quantity>0</Quantity>
			<Invoiced>0</Invoiced>
			<Remainder>0</Remainder>
			<Amount>0</Amount>
			<DeliveryDate>01.01.1970</DeliveryDate>
			<LotEntryNo>0</LotEntryNo>
			<LotNo>0</LotNo>
			<SeqNo>10040000</SeqNo>
			<UniqueID>238</UniqueID>
		</InvoiceLine>
	</Invoice>
	<ErrorMessage>
		<ErrorCode>0</ErrorCode>
		<Message>0 - OK</Message>
		<RecordCount>4</RecordCount>
	</ErrorMessage>
	<ClientInfo>
		<ClientId>Test</ClientId>
	</ClientInfo>
</Invoicelist>

And after trying back and forth, my CRMScript looks like this: 

#
setLanguageLevel 3;
/*
Finn medlem eller abonnent i SO
Finn faktura på denne i Global med år = 2020 (Artikkelnr, avdeling, dato, prosjekt)

Lag ny faktura med riktig kunde/kjede og artikkel


*/
struct InvoiceLine {
	String ArticleNo;
	String Name;
	String NetPrice;
	String Quantity;
	String Invoiced;
	String Remainder;
	String Amount;
	String DeliveryDate;
	String LotEntryNo;
	String LotNo;
	String SeqNo;
	String UniqueID;
	String OrderNo;
};
struct Invoice {
	String CustomerNo;
	String Customer;
	String DeliveryAddressNo;
	String DeliveryAddress1;
	String DeliveryAddress2;
	String DeliveryPostCode;
	String DeliveryPostOffice;
	String InvoiceAddressNo;
	String InvoiceAddress1;
	String InvoiceAddress2;
	String InvoicePostCode;
	String InvoicePostOffice;
	String InvoiceNo;
	String OrderNo;
	String InvoiceDate;
	String OrderDate;
	String DeliveryDate;
	String TotalNetAmount;
	String TotalGrossAmount;
	String TotalDelivered;
	String OrderType;
	String ChainNo;
	String TypeOfChain;
	String EmployeeNo;
	String ZUsrTilPeriode;
	String OrderStatus;
	String DepNo;
	String ProjectNo;
	String NoInvoiceLines;
	InvoiceLine[] InvoiceLine;
};
struct ErrorMessage {
	String ErrorCode;
	String Message;
	String RecordCount;
};
struct ClientInfo {
	String ClientId;
};
struct InvoiceList {
	Invoice[] Invoice;
	ErrorMessage ErrorMessage;
	ClientInfo ClientInfo;
};
struct InvoiceInfoCollection {
	InvoiceList InvoiceList;
	Void fromXml(String xml) {
		XMLNode xml = parseXML(xml);
		XMLNode[] nodes = xml.getChildren();
		printLine(nodes.length().toString());
		for(Integer i = 0; i < nodes.length(); i++) {
			printLine(nodes[i].getName() + " " + nodes[i].getText());
			Invoice in ; in .fromXMLNode(nodes[i]);
			this.InvoiceList.Invoice.pushBack( in );
		}
	}
	Void printthis() {
		printLine("INvoice list length: " + this.InvoiceList.Invoice.length().toString());
		for(Integer i = 0; i < this.InvoiceList.Invoice.length(); i++) {
			printLine("OrderNo: " + this.InvoiceList.Invoice[i].OrderNo + "  Customer: " + this.InvoiceList.Invoice[i].Customer);
			for(Integer j = 0; j < this.InvoiceList.Invoice[i].InvoiceLine.length(); j++) {
				printLine(" ArticleNo: " + this.InvoiceList.Invoice[i].InvoiceLine[j].Name);
			}
		}
	}
};

//Result from api
result1 = result1.utf8Decode();
print(result1);

InvoiceInfoCollection invoiceCollection;
invoiceCollection.fromXml(result1);
invoiceCollection.printthis();

So, I'm able to populate the InvoiceList with Invoices but then not the InvoicLines.
I'm stuck (again).....