How to filter JSON array in C#

Question

I have spent a lot of time to find a solution for my problem.

In this example, I have 2 records in SetNavRecords array. The first one is "Artikelnummer" : "21700" and the second one is "Artikelnummer" : "21701"

Each record have an array "OfflineVerkaufspreis".

Important is for me the field "Location_Code" in "OfflineVerkaufspreis" I only need the hole informations for one filtered Location Code.

How can I select the data for one Location Code, for example "MH"?

I am using C# and the Newtonsoft Class for JSON parsing.

I have tried some versions with LINQ but without success.

{ "SetNavRecords" : [ { "Artikelbeschreibung" : "Trikot \"Home\" 2012/2013",
    "Artikelbeschreibung2" : "weiß",
    "Artikelnummer" : "21700",
    "Artikelrabattgruppe" : "MERCH",
    "Gutschein" : false,
    "MwStProduktgruppe" : "VOLLNEU",
    "OfflineVerkaufspreis" : [ { "Allow_Line_Discount" : true,
          "Date" : "2014-05-16T00:00:00",
          "Item_No" : "21700",
          "Location_Code" : "BP",
          "Unit_Price" : 5.0
        },
        { "Allow_Line_Discount" : true,
          "Date" : "2014-05-16T00:00:00",
          "Item_No" : "21700",
          "Location_Code" : "MH",
          "Unit_Price" : 5.0
        },
        { "Allow_Line_Discount" : true,              
          "Date" : "2014-05-16T00:00:00",
          "Item_No" : "21700",
          "Location_Code" : "RY",
          "Unit_Price" : 5.0
        }
      ]
  },
  { "Artikelbeschreibung" : "Autogrammtrikot 2012/2013",
    "Artikelbeschreibung2" : "weiß",
    "Artikelnummer" : "21701",
    "Artikelrabattgruppe" : "MERCH",
    "Gutschein" : false,
    "MwStProduktgruppe" : "VOLLNEU",
    "OfflineVerkaufspreis" : [ { "Allow_Line_Discount" : false,
          "Date" : "2014-05-16T00:00:00",
          "Item_No" : "21701",
          "Location_Code" : "BP",
          "Unit_Price" : 69.99
        },
        { "Allow_Line_Discount" : false,
          "Date" : "2014-05-16T00:00:00",
          "Item_No" : "21701",
          "Location_Code" : "MH",
          "Unit_Price" : 69.99
        },
        { "Allow_Line_Discount" : false,
          "Date" : "2014-05-16T00:00:00",
          "Item_No" : "21701",
          "Location_Code" : "RY",
          "Unit_Price" : 69.99
        }
      ]
  }

] }

Here is my problem:

var tmpResult = JObject.Parse(File.ReadAllText(FileName));
var resultObject = tmpResult["SetNavRecords"]
      .Values("OfflineVerkaufspreis")
      .Values<JObject>()                                   
      .Where(n => n["Location_Code"].Value<string>() == "MH"); 

The filter works fine but the data is incomplete in tmpResult. I only get the data in "OfflineVerkaufspreis". I need the root data too.

Has anybody an idea?

Thanks!

Solution 1

This feels like a job that could be made a little easier by switching to serialization of the json into objects. To deserialize the json into an object you could use:

RootObject obj = JsonConvert.DeserializeObject<RootObject>(jsonString);

likewise, you can turn your object back into json using:

string jsonString = JsonConvert.SerializeObject(RootObject);

The structure of your object based on the json you provided in the question is such:

public class OfflineVerkaufsprei
{
    public bool Allow_Line_Discount { get; set; }
    public string Date { get; set; }
    public string Item_No { get; set; }
    public string Location_Code { get; set; }
    public double Unit_Price { get; set; }
}

public class SetNavRecord
{
    public string Artikelbeschreibung { get; set; }
    public string Artikelbeschreibung2 { get; set; }
    public string Artikelnummer { get; set; }
    public string Artikelrabattgruppe { get; set; }
    public bool Gutschein { get; set; }
    public string MwStProduktgruppe { get; set; }
    public List<OfflineVerkaufsprei> OfflineVerkaufspreis { get; set; }
}

public class RootObject
{
    public List<SetNavRecord> SetNavRecords { get; set; }
}

you can then iterate easily over your objects by saying:

for each(SetNavRecord i in obj.SetNavRecords)
{
    // do something to the record
}

Solution 2

var tmpResult = JObject.Parse(File.ReadAllText(FileName));
var resultObject = tmpResult["SetNavRecords"].Values("OfflineVerkaufspreis").Values<JObject>()                                   
            .Where(n => n["Location_Code"].Value<string>() == "MH");   

This code will navigate to OfflineVerkaufspreis.Values and then filter them. But (I guess) you want to select the location that matches your Where clause and then also selects the parent details.

You can use Select to select all the information you are really interested in.

var resultObject = tmpResult["SetNavRecords"].Values("OfflineVerkaufspreis").Values<JObject>()
           .Where(n => n["Location_Code"].Value<string>() == "MH")
           .Select(n => new { Location = n, Context = n.Parent }).ToArray();

Of course you can select just the information you really need instead of the entire Parent.

To make this code more readable, I would suggest to create some classes that match the JSon input so that you can map the JSon to this graph of objects. It will make your Linq alot easier to write, read and understand.

Update: In the comments is suggested to use: .Where(n => n["OfflineVerkaufspreis"]["Location_Code"] .... The problem with this approach is that in the end you don't know which of the OfflineVerkaufspreis actually matched your where clause...

Solution 3

I would have created classes by the name SetNavRecords and OfflineVerkaufspreis. Then I would have used JavaScriptSerializer from namespace System.Web.Script.Serialization.JavaScriptSerializer to deserialize it.

Code goes like this-

JavaScriptSerializer.Deserialize(String, Type);

Then I can operate it like simple generic collection eg. list.

Hope this will help you little bit.