Json Parsing

Business Central/NAV Developers Forum
1

I am working on parsing a Json response in NAV2015.

Thanks to Gunar’s blog post http://www.dynamics.is/?p=2303#comment-1747 , and suggested modifications in the comments, I was able to parse one single Json object that had nested arrays.
Example: There are several accomplished events…

[{
	"waybill": {
		"status": null,
		"number": "123456",
		"date": "2018-05-07",
		"flipAuth": false,
		"storageCharges": {
			"chargesApplicable": "N/A"
		},
		"accomplishedEvents": [{
			"name": "Interchange Received ",
			"dateTime": "2018-05-07T17:00",
			"trainIdCarrierOrReason": "UP received from CCT",
			"location": {
				"city": "Ptstockto",
				"state": "CA"
			}
		},
		{
			"name": "Yard Departure",
			"dateTime": "2018-05-08T22:03",
			"trainIdCarrierOrReason": "YSTST  08",
			"location": {
				"city": "Ptstockto",
				"state": "CA"
			}
		}]
	}
}]

But now what happens if I were to get several waybills back in my response?

Using the Posting Exch. Field Table 1221 to store the data after parsing, I think that I might be able to use the line number to differentiate between the different waybills, but haven’t found a way to implement it.

Does anyone have any suggestions?
Here is the code I use to parse the Json response.

    PROCEDURE ReadJSon@5(VAR String@1000 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.String";VAR TempPostingExchField@1002 : Record 1221);
    VAR
      JsonToken@1001 : DotNet "'Newtonsoft.Json, Version=4.5.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed'.Newtonsoft.Json.JsonToken";
      PrefixArray@1006 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Array";
      PrefixString@1005 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.String";
      PropertyName@1008 : Text;
      ColumnNo@1003 : Integer;
      InArray@1007 : ARRAY [250] OF Boolean;
      ArrayDepth@1102610000 : Integer;
    BEGIN
      PrefixArray := PrefixArray.CreateInstance(GETDOTNETTYPE(String),250);
      StringReader := StringReader.StringReader(String);
      JsonTextReader := JsonTextReader.JsonTextReader(StringReader);
      WHILE JsonTextReader.Read DO
        CASE TRUE OF
          JsonTextReader.TokenType.CompareTo(JsonToken.StartObject) = 0 :
            ;
          JsonTextReader.TokenType.CompareTo(JsonToken.StartArray) = 0 :
            BEGIN
              InArray[JsonTextReader.Depth + 1] := TRUE;
              ColumnNo := 0; 
              ArrayDepth += 1;
            END;
          JsonTextReader.TokenType.CompareTo(JsonToken.StartConstructor) = 0 :
            ;
          JsonTextReader.TokenType.CompareTo(JsonToken.PropertyName) = 0 :
            BEGIN
               PrefixArray.SetValue(JsonTextReader.Value,JsonTextReader.Depth - ArrayDepth);
              IF JsonTextReader.Depth > 1 THEN BEGIN
                PrefixString := PrefixString.Join('.',PrefixArray,0,JsonTextReader.Depth - ArrayDepth);
                IF PrefixString.Length > 0 THEN
                  PropertyName := PrefixString.ToString + '.' + FORMAT(JsonTextReader.Value,0,9)
                ELSE
                  PropertyName := FORMAT(JsonTextReader.Value,0,9);
              END ELSE
                PropertyName := FORMAT(JsonTextReader.Value,0,9);
            END;
          JsonTextReader.TokenType.CompareTo(JsonToken.String) = 0 ,
          JsonTextReader.TokenType.CompareTo(JsonToken.Integer) = 0 ,
          JsonTextReader.TokenType.CompareTo(JsonToken.Float) = 0 ,
          JsonTextReader.TokenType.CompareTo(JsonToken.Boolean) = 0 ,
          JsonTextReader.TokenType.CompareTo(JsonToken.Date) = 0 ,
          JsonTextReader.TokenType.CompareTo(JsonToken.Bytes) = 0 :
            BEGIN
              NewValue := FORMAT(JsonTextReader.Value,0,9);
              TempPostingExchField."Posting Exch. No." := JsonTextReader.Depth;
              TempPostingExchField."Line No." := JsonTextReader.LineNumber;
              TempPostingExchField."Column No." := ColumnNo;
              TempPostingExchField."Node ID" := PropertyName;
              TempPostingExchField.Value := FORMAT(JsonTextReader.Value,0,9);
              TempPostingExchField."Posting Exch. Line Def Code" := JsonTextReader.TokenType.ToString;
              IF NOT TempPostingExchField.INSERT THEN BEGIN
                //TODO
              END;
            END;
          JsonTextReader.TokenType.CompareTo(JsonToken.EndConstructor) = 0 :
            LineNo += 1;
          JsonTextReader.TokenType.CompareTo(JsonToken.EndArray) = 0 :
            BEGIN //add
              InArray[JsonTextReader.Depth + 1] := FALSE;
              ArrayDepth -= 1;
            END;
          JsonTextReader.TokenType.CompareTo(JsonToken.EndObject) = 0 :
            IF JsonTextReader.Depth > 0 THEN
              IF InArray[JsonTextReader.Depth] THEN 
                ColumnNo += 1;

        END;
    END;
2

Hi Tonya,

Have you tried to convert JSON to XML and vice versa? It’s really easy and works well.

JSON to XML:

ConvertJsonToXml(NVInStream : InStream;VAR XmlDocument : DotNet "System.Xml.XmlDocument")
  IF NOT JsonToXML(NVInStream,XmlDocument) THEN
    IF NOT JsonToXMLCreateDefaultRoot(NVInStream,XmlDocument) THEN
      ERROR(GETLASTERRORTEXT);

LOCAL [TryFunction] JsonToXML(JsonInStream : InStream;VAR XmlDocument : DotNet "System.Xml.XmlDocument")
  WHILE JsonInStream.READTEXT(NewContent) > 0 DO
    FileContent += NewContent;

  XmlDocument := NewtonsoftJson.DeserializeXmlNode(FileContent,'root');

LOCAL [TryFunction] JsonToXMLCreateDefaultRoot(JsonInStream : InStream;VAR XmlDocument : DotNet "System.Xml.XmlDocument")
  WHILE JsonInStream.READTEXT(NewContent) > 0 DO
    FileContent += NewContent;

  FileContent := '{"root":' + FileContent + '}';

  XmlDocument := NewtonsoftJson.DeserializeXNode(FileContent);

XML to JSON:

ConvertXmlToJson(VAR XmlDocument : DotNet "System.Xml.XmlDocument") JsonTxt : Text
  IF NOT XMLToJson(JsonTxt,XmlDocument) THEN
    ERROR(GETLASTERRORTEXT);

LOCAL [TryFunction] XMLToJson(VAR JsonTxt : Text;VAR XmlDocument : DotNet "System.Xml.XmlDocument")
  JsonFormatting := JsonFormatting.None;
  JsonTxt := NewtonsoftJson.SerializeXmlNode(XmlDocument,JsonFormatting,TRUE);

With this you can work with XML. In NAV use XML is easier than using JSON directly.

3

The JSON response won’t always come back in the same order (it’s being dynamically created), so I am unable to use an XMLPort.

I already have the JSON parsed correctly for ONE waybill…I just can’t figure out how to handle receiving multiple waybills in a response. Obviously if there is a way to work with what I already have, that is preferred, rather than having to rewrite everything to parse an XML file.

4

Hi Tonya,

I don’t recomend use XMLPort in this case, but you can use XML dotnet vars to process information. Every time I tried to process a JSON from NAV I ended frustrated, and when discovered the way to convert a JSON to XML and a XML to JSON, was like to see the light at the end of the tunnel.

If you can’t go ahead with JSON, you have to think if would be better to redesign using XML instead.

Sorry, I can offer the solution you are looking for, but perhaps this alternative will help you.

5

I was able to figure it out! Here is the solution in case anyone else is looking how to parse JSON.

PROCEDURE ReadJSon@5(VAR String@1000 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.String";VAR TempPostingExchField@1002 : Record 1221);

VAR  JsonToken@1001 : DotNet "'Newtonsoft.Json, Version=4.5.0.0, Culture=neutral,PublicKeyToken=30ad4fe6b2a6aeed'.Newtonsoft.Json.JsonToken";
  PrefixArray@1006 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Array";
  PrefixString@1005 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.String";
  PropertyName@1008 : Text;
  ColumnNo@1003 : Integer;
  InArray@1007 : ARRAY [250] OF Boolean;
  ArrayDepth@1102610000 : Integer;
  ActualLineNumber@1102610001:Integer;
  TempLineNumber@1102610002:Integer;
BEGIN
  PrefixArray := PrefixArray.CreateInstance(GETDOTNETTYPE(String),250);
  StringReader := StringReader.StringReader(String);
  JsonTextReader := JsonTextReader.JsonTextReader(StringReader);
  ActualLineNumber:=1;
  TempLineNumber:=0;
  WHILE JsonTextReader.Read DO
	CASE TRUE OF
	  JsonTextReader.TokenType.CompareTo(JsonToken.StartObject) = 0 :
		BEGIN
		   TempLineNumber +=1;
		END;
	  JsonTextReader.TokenType.CompareTo(JsonToken.StartArray) = 0 :
		BEGIN
		  InArray[JsonTextReader.Depth + 1] := TRUE;
		  ColumnNo := 0; 
		  ArrayDepth += 1;
		END;
	  JsonTextReader.TokenType.CompareTo(JsonToken.StartConstructor) = 0 :
		;
	  JsonTextReader.TokenType.CompareTo(JsonToken.PropertyName) = 0 :
		BEGIN
		   PrefixArray.SetValue(JsonTextReader.Value,JsonTextReader.Depth - ArrayDepth);
		  IF JsonTextReader.Depth > 1 THEN BEGIN
			PrefixString := PrefixString.Join('.',PrefixArray,0,JsonTextReader.Depth - ArrayDepth);
			IF PrefixString.Length > 0 THEN
			  PropertyName := PrefixString.ToString + '.' + FORMAT(JsonTextReader.Value,0,9)
			ELSE
			  PropertyName := FORMAT(JsonTextReader.Value,0,9);
		  END ELSE
			PropertyName := FORMAT(JsonTextReader.Value,0,9);
		END;
	  JsonTextReader.TokenType.CompareTo(JsonToken.String) = 0 ,
	  JsonTextReader.TokenType.CompareTo(JsonToken.Integer) = 0 ,
	  JsonTextReader.TokenType.CompareTo(JsonToken.Float) = 0 ,
	  JsonTextReader.TokenType.CompareTo(JsonToken.Boolean) = 0 ,
	  JsonTextReader.TokenType.CompareTo(JsonToken.Date) = 0 ,
	  JsonTextReader.TokenType.CompareTo(JsonToken.Bytes) = 0 :
		BEGIN
		  NewValue := FORMAT(JsonTextReader.Value,0,9);
		  TempPostingExchField."Posting Exch. No." := JsonTextReader.Depth;
		  TempPostingExchField."Line No." := ActualLineNumber;
		  TempPostingExchField."Column No." := ColumnNo;
		  TempPostingExchField."Node ID" := PropertyName;
		  TempPostingExchField.Value := FORMAT(JsonTextReader.Value,0,9);
		  TempPostingExchField."Posting Exch. Line Def Code" := JsonTextReader.TokenType.ToString;
		  IF NOT TempPostingExchField.INSERT THEN BEGIN
			//TODO
		  END;
		END;
	  JsonTextReader.TokenType.CompareTo(JsonToken.EndConstructor) = 0 :
		LineNo += 1;
	  JsonTextReader.TokenType.CompareTo(JsonToken.EndArray) = 0 :
		BEGIN
		  InArray[JsonTextReader.Depth + 1] := FALSE;
		  ArrayDepth -= 1;
		END;
		
	  JsonTextReader.TokenType.CompareTo(JsonToken.EndObject) = 0 :
	  BEGIN
	    TempLineNumber -=1;
	    IF TempLineNumber = 0 THEN
		BEGIN
		     ActualLineNumber += 1;
		END;
		IF JsonTextReader.Depth > 0 THEN
		  IF InArray[JsonTextReader.Depth] THEN 
			ColumnNo += 1;
      END;
	END;
END;
6

JSON to XML how To Convert Date