Using Newtonsoft JSON Library to find specific key-value pairs in a JSON string

rsaritzkyrsaritzky Member Posts: 454
edited 2020-03-06 in NAV Three Tier
Hi everyone,

There are a number of posts here about parsing JSON files, but none that use the DotNet Newtonsoft JSON library.

Has anyone used the DotNet library Newtonsoft.Json.Linq.JObject to read specific key/value pairs from a JSON string?

I've loaded values into a Json Object:
{
  "item": {
    "ID": "72795",
    "Latitude": "34.045358",
    "Longitude": "-118.443574",
    "ZipCode": "90025",
    "CityAliasAbbr": null,
    "AreaCode": "310/424/818",
    "City": "LOS ANGELES",
    "CountyName": "LOS ANGELES",
    "CityAliases": {
      "Item": [
        {
          "ID": "72795",
          "Primary": "P",
          "CityType": "P",
          "CityAliasName": "LOS ANGELES",
          "MixedCaseAlias": "Los Angeles"
        },
        {
          "ID": "72796",
          "Primary": " ",
          "CityType": "N",
          "CityAliasName": "W LOS ANGELES",
          "MixedCaseAlias": "W Los Angeles",
        },
        {
          "ID": "72797",
          "Primary": " ",
          "CityType": "N",
          "CityAliasName": "WEST LOS ANGELES",
          "MixedCaseAlias": "West Los Angeles",
        },
      ]
    }
  }
}

I can retrieve the "item" key using:
IF JObject.TryGetValue('item',JToken) THEN BEGIN
  JProperty := JObject.Property(propertyName);
  value := JProperty.Value;
END;

The value returned is the entire Json string as above.

But I want to retrieve ONLY the "Latitude" and "Longitude" key-value pairs.

I've tried:
IF JObject.TryGetValue('[item][Longitude]',JToken) THEN BEGIN...
IF JObject.TryGetValue('item,Longitude',JToken) THEN BEGIN...

But both return <blank>.

There are numerous examples out on the web for C# or Javascript that say this can be done, i.e. you don't have to parse the entire string and check each key-value pair for the one you want. But the syntax doesn't translate to C/AL. for example:
JObject rss = JObject.Parse(json);
string rssTitle = (string)rss["item"]["Longitude"];

Does anyone know how to read a specific key-value pair in C/AL that is "nested" inside another key?
Ron

Best Answers

Answers

  • rsaritzkyrsaritzky Member Posts: 454
    Thanks for the suggestion - I'll try it!
    Ron
  • rsaritzkyrsaritzky Member Posts: 454
    ftornero wrote: »
    Hello @rsaritzky ,

    Have you try to use SelectToken.
    Latitude := JObject.SelectToken('item.Latitude');
    

    Latitude must to be a variant variable and deal with it.

    Regards.

    OK, the above line worked great for the keys in the "first level" like "Latitude".
    Now I want to loop through the "CityAliases"
     "CityAliases": {
          "Item": [
            {
              "ID": "72795",
              "Primary": "P",
              "CityType": "P",
              "CityAliasName": "LOS ANGELES",
              "MixedCaseAlias": "Los Angeles"
            },
            {
              "ID": "72796",
              "Primary": " ",
              "CityType": "N",
              "CityAliasName": "W LOS ANGELES",
              "MixedCaseAlias": "W Los Angeles",
            },
            {
              "ID": "72797",
              "Primary": " ",
              "CityType": "N",
              "CityAliasName": "WEST LOS ANGELES",
              "MixedCaseAlias": "West Los Angeles",
            },
    

    I can move the "City Aliases" key and all its sub-values into a separate JsonObject, but when I try to query for values within each "Item" in a FOREACH loop, I get NULL, e.g.
    JsonObject2 := JSONObject.SelectToken('item.CityAliases');
    
    FOREACH Element IN JSONObject2 DO
      BEGIN
        Value := JSONObject2.SelectToken('Item.CityAliasName');
        IF CONFIRM('AliasName = ' + FORMAT(Value)) THEN;  <<--Returns NULL
      END;
    

    Any ideas?
    Ron
  • nikhiltoor27nikhiltoor27 Member Posts: 8
    Hi @ftornero ,

    While using the

    JObject1 := JObject1.SelectToken('packDoneDetails.packingBoxDetails');

    I am getting the below error. Can you please help me out.

    A DotNet variable has not been instantiated. Attempting to call Newtonsoft.Json.Linq.JObject.SelectToken in CodeUnit MAVEN Integration: ParseJSONResponse
  • ftorneroftornero Member Posts: 413
    Hello @nikhiltoor27 ,

    You need to change this
    JObject1 := JObject1.SelectToken('packDoneDetails.packingBoxDetails');
    

    to this
    JObject1 := JObject1.SelectToken('packDoneDetails[0].packingBoxDetails');
    

    packDoneDetails, in your JSON data, is an array.

    Regards
  • rsaritzkyrsaritzky Member Posts: 454
    Thanks to help from ftornero, I was able to work through my question. I thought I'd post my solution.

    See the first message in this thread to see the full JSON being retrieved. It has a "nested" array (set of key-value pairs) for "CityAliases". I want to loop through those Aliases.

    We're working in NAV2016, so I took the Json Management codeunit from BC and downgraded it to C/AL, but the functions are the same.

    The "JsonString" parameter passed is the full JSON result as shown in the first message:

    There may be a more "efficient" way of moving the JSON CityAliases Subset to an array for looping through, but I was using the functions in the JSON Management codeunit.

    In a JSON Array, the first element is [0], so my loop takes my counter which starts at 1 and subtracts 1 to get the array member.
    LOCAL AddCityAliases(ZipCode : Code[20];ZipCodeCity : Text[30];JsonString : Text)
    
    LoadJsonStringToJsonObject(JsonString,JSONObject);
    JProperty := JSONObject.SelectToken('item.CityAliases');
    IF ISNULL(JProperty) THEN  //If the key-value array is empty then do nothing
      EXIT;
    Value := JSONObject.SelectToken('item.CityAliases.Item');  //Find the array of aliases only
    NewJSONString := FORMAT(Value);   //Load the object into a JSONString
    LoadJsonStringToJsonArray(NewJSONString,JsonArray);  //Move the JSONString to a JSONarray 
    Ctr := JsonArray.Count; //Count how many array members there are
    JSONMgt.InitializeCollectionFromJArray(JsonArray);  //Loads the array into the JsonMgt codeunit global
    FOR i := 1 TO Ctr DO BEGIN
      JSONMgt.GetJObjectFromCollectionByIndex(JsonObject2,i-1);  //Retrieve the next array member into
                                                                 //its own object
      JSONMgt.GetPropertyValueFromJObjectByName(JsonObject2,'MixedCaseAlias',Value); //Retrieve the value
      AliasName := FORMAT(Value);
      CreatePostCodeAlias(ZipCode,ZipCodeCity,AliasName);  //My function that creates a record in NAV
    END;
    


    Ron
  • juliocaetanojuliocaetano Member Posts: 2
    edited 2021-03-12
    Could someone help me with a small example of how I create the item details, using Newtonsoft Json, from the example below:
    {
    "item": {
    "ID": "72795",
    "ZipCode": "90025",
    "City": "LOS ANGELES",
    "CityAliases": {
    "Item": [
    {
    "ID": "72795",
    "Primary": "P",
    "CityType": "P",
    },
    {
    "ID": "72796",
    "Primary": "",
    "CityType": "N",
    },
    {
    "ID": "72797",
    "Primary": "",
    "CityType": "N",
    },
    ]
    }
    }
    }
Sign In or Register to comment.