Adding nodes with JsonTextWritter

aitorNAV · May 13, 2019, 7:15am

Hi everyone,
Probably is an easy question, but I can’t find the way to do this. This is the structure of an XMLPort:

I need to generate a JSON with that structure, and I’m trying in this way:

LOCAL CreateSimpleJsonFile(VAR JSonResponse : DotNet "Newtonsoft.Json.Linq.JObject";peShopItem : Record "eShop Item")

  JSonResponse := JSonResponse.JObject();
  JsonTextWriter := JSonResponse.CreateWriter();
  JsonTextWriter.WritePropertyName('Ref');
  JsonTextWriter.WriteValue(peShopItem."No.");
  JsonTextWriter.WritePropertyName('DeleForEshop');
  JsonTextWriter.WriteValue(peShopItem."Delete-for Eshop");
  JsonTextWriter.WritePropertyName('Picture1');
  JsonTextWriter.WriteValue(peShopItem."Picture 1");
  JsonTextWriter.WritePropertyName('Category');
  JsonTextWriter.WriteValue(peShopItem."Item Category Code");
  JsonTextWriter.WritePropertyName('Subcategory');
  JsonTextWriter.WriteValue(peShopItem."Product Group Code");
  JsonTextWriter.WritePropertyName('SubcDescription');
  JsonTextWriter.WriteValue(peShopItem."Item Group Description");
  JsonTextWriter.WritePropertyName('Range');
  JsonTextWriter.WriteValue(peShopItem.Range);
  JsonTextWriter.WritePropertyName('Reading');
  JsonTextWriter.WriteValue(peShopItem.Reading);
  JsonTextWriter.WritePropertyName('WeightKg');
  JsonTextWriter.WriteValue(peShopItem."Weight (Kg)");
  leShopItembyShop.RESET;
  leShopItembyShop.SETRANGE("Item No.",peShopItem."No.");
  IF leShopItembyShop.FINDSET THEN REPEAT
    JsonTextWriter.WritePropertyName('eShopURL');
    JsonTextWriter.WriteValue(leShopItembyShop."eShop URL");
    JsonTextWriter.WritePropertyName('Enabled');
    JsonTextWriter.WriteValue(leShopItembyShop.Enabled);
    JsonTextWriter.WritePropertyName('Description');
    JsonTextWriter.WriteValue(leShopItembyShop.Description);
    JsonTextWriter.WritePropertyName('Availability');
    JsonTextWriter.WriteValue(leShopItembyShop.Availability);
    JsonTextWriter.WritePropertyName('GenericPrice');
    JsonTextWriter.WriteValue(leShopItembyShop."Generic Price");
    JsonTextWriter.WritePropertyName('InTransit');
    JsonTextWriter.WriteValue(leShopItembyShop."Qty. on Purch. Order");
    JsonTextWriter.WritePropertyName('FromTransitDate');
    JsonTextWriter.WriteValue(leShopItembyShop."Next Reception Date");
    JsonTextWriter.WritePropertyName('InCentral');
    JsonTextWriter.WriteValue(leShopItembyShop."Qty. on Origin");
    JsonTextWriter.WritePropertyName('FromCentralDate');
    JsonTextWriter.WriteValue(leShopItembyShop."Recep. Date-from Origin");
    JsonTextWriter.WritePropertyName('FromCentralDateText');
    JsonTextWriter.WriteValue(leShopItembyShop."Recep.-from Origin (Text)");
    JsonTextWriter.WritePropertyName('URLTechnicalCard');
    JsonTextWriter.WriteValue(leShopItembyShop."Technical Card url");
  UNTIL leShopItembyShop.NEXT = 0;

As you can imagine, after the findset I should create another deeper level of the json, but this is not working. What sentence should I use?

Thank you very much

R-kwadraat · May 13, 2019, 10:47am

use Newtonsoft.Json.JsonConvert.‘Newtonsoft.Json, Version=6.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed’

and Newtonsoft.Json.Formatting.‘Newtonsoft.Json, Version=6.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed’

Run xmlport.EXPORT to create a XLDomDocument.

Convert the XML to JSon using:

JSonText := .SerializeXmlNode(XmlDocument.DocumentElement,JSONFormatting.Indented,TRUE);

aitorNAV · May 13, 2019, 10:53am

Thanks for your answer. This was my first approach:

ExporteShopItemJSON(VAR myXMLPort : Integer) myJSON : Text

blob.INIT;
blob.Blob.CREATEOUTSTREAM(myOutStream);
XMLPORT.EXPORT(myXMLPort, myOutStream);
ConvertXMLToJSON(blob);
CLEAR(gJSONBig);
myJSON := blob.ReadAsText('',TEXTENCODING::UTF8);


MyFile.CREATE('C:\Users\Public\Documents\eshop\JSON\JsonTest'+FORMAT(TODAY,0,'<Year,2><Day,2><Month,2>')+'_'+FORMAT(TIME,0,'<Hours24>.<Minutes,2>.<Seconds,2>')+'_DESA.json');
MyFile.CREATEOUTSTREAM(myOutStream);
myOutStream.WRITETEXT(myJSON);

 

LOCAL ConvertXMLToJSON(VAR TempBlob : Record TempBlob)

TempBlob.Blob.CREATEINSTREAM(myInStream);
XmlDocument := XmlDocument.XmlDocument;
XmlDocument.Load(myInStream);
myJSON := JSONConvert.SerializeXmlNode(XmlDocument.DocumentElement,JSONFormatting.Indented,TRUE);
 TempBlob.INIT;
 TempBlob.Blob.CREATEOUTSTREAM(myOutStream,TEXTENCODING::UTF8);
 myOutStream.WRITETEXT(myJSON);

But when the XML file is quite big, the process fails, and the next proceses to. This procedure is called from the winServer task manager. After I must send the json file to a rest api, and all the process is repeated every hour, so after one failure, all the processes are cracking.

I realized that the problem is that if the XML is too big, the conversion is failling, that’s why I’m trying to generate directly the json file

Alexander_Drogin · May 13, 2019, 5:54pm

JsonWriter is not aware that you want to write hierarchical structure, and of course, it will continue writing a linear list. To create a hierarchy, call WriteStartObject before the loop, and WriteEndObject after it.

JsonTextWriter.WriteValue(peShopItem."Weight (Kg)");
JsonTextWriter.WritePropertyName('ByShop');

JsonTextWriter.WriteStartObject;

leShopItembyShop.RESET;
leShopItembyShop.SETRANGE("Item No.",peShopItem."No.");
IF leShopItembyShop.FINDSET THEN
  REPEAT
  UNTIL...

JsonTextWriter.WriteEndObject;

aitorNAV · May 14, 2019, 6:45am

Yes, thank you!!! I knew it should be something that easty, really appreciated!!!

edit: I will always have to “Byshop” nodes, but with this code, I will always write the second one, even in the debugger I see that it’s finding both of them:

{
  "Ref": "1108-150",
  "DeleForEshop": false,
  "Picture1": "http://www.xxxx.com/epicture/xxxx/1108-150W.jpg",
  "Category": "xxxxx",
  "Subcategory": "02",
  "SubcDescription": "Calibres",
  "Range": "0-150mm/0-6\"",
  "Reading": "0.01mm/0.0005\"",
  "WeightKg": 0.3875,
  "ByShop": {
    "eShopURL": "eShop2",
    "Enabled": true,
    "Description": "Calibre Digital 0-150mm/0-6\"",
    "Availability": 335.0,
    "GenericPrice": 0.0,
    "InTransit": 0.0,
    "FromTransitDate": "0001-01-01T00:00:00",
    "InCentral": 0.0,
    "FromCentralDate": "0001-01-01T00:00:00",
    "FromCentralDateText": "",
    "URLTechnicalCard": "http://www.xxxxx.com/pdf/xxxxx/esp/1108.pdf"
  }
}

May be the jsonTextWritter is overwritting the first one?

aitorNAV · May 14, 2019, 7:45am

I’ve tried this code:

JSonResponse := JSonResponse.JObject();
JsonTextWriter := JSonResponse.CreateWriter();
JsonTextWriter.Formatting := 1;
JsonTextWriter.WritePropertyName('Ref');
JsonTextWriter.WriteValue(peShopItem."No.");

JsonTextWriter.WritePropertyName('ByShop');

leShopItembyShop.RESET;
leShopItembyShop.SETRANGE("Item No.",peShopItem."No.");
IF leShopItembyShop.FINDSET THEN REPEAT  
  
  JsonTextWriter.WriteStartObject;
  JsonTextWriter.WritePropertyName('eShopURL');
  JsonTextWriter.WriteValue(leShopItembyShop."eShop URL");

  JsonTextWriter.WriteEndObject;
UNTIL leShopItembyShop.NEXT = 0;

Adding writeStart and wirteEnd inside the repeat, but i get this error:

“Error in the cal … with the message…”

Alexander_Drogin · May 14, 2019, 10:09am

You still need to create a container for your objects before entering the loop - this code is trying to assign multiple values to a single property ‘ByShop’, but the property value must be atomic. If you want to create several objects inside the loop, then you need an array.

Leave the WriteStartObject and WriteEndObject inside the loop, but in this case, add WriteStartArray and WriteEndArray outside of it.

aitorNAV · May 14, 2019, 10:14am

Yes, you did it again! Really appreciated Alexander, Thank you very much!!!