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

rsaritzky

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?

ftornero

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.

ftornero

Hello @rsaritzky

Ue this to get the ID of the first item a so on.

  Valor := JObject.SelectToken('item.CityAliases.Item[0].ID');

Regards

ftornero

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.

rsaritzky

Thanks for the suggestion - I'll try it!

rsaritzky

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?

ftornero

Hello @rsaritzky

Ue this to get the ID of the first item a so on.

  Valor := JObject.SelectToken('item.CityAliases.Item[0].ID');

Regards

nikhiltoor27

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

ftornero

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

rsaritzky

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;

juliocaetano

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",
},
]
}
}
}

shanabeywicrema

Hello guys, I am able to count how many objects available in the JSON response, Is there any possible way to read name of the element?

This is my JSON response
"27675":{
"entity_id":"27675",
"status":"complete",

},
"27676":{
"entity_id":"27676",
"status":"complete",

},
"27677":{
"entity_id":"27677",
"status":"complete",

can we read "27675", "27676"..... these numbers?