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!

Dont use the .Values("OfflineVerkaufspreis"), because then all you get is "OfflineVerkaufspreis" objects. Access the "OfflineVerkaufspreis" objects inside your Where clause, like .Where(n => n["OfflineVerkaufspreis"]["Location_Code"] ... (i believe, i haven't tested it) — user2819245
– user2819245, Commented May 16, 2014 at 14:58
That's because your code is telling it to get the values of just OfflineVerkaufspreis when it says .Values("OffilineVerkaufspreis"). Take out that and just do a where against the tmpResult["SetNavRecords"]. Untested, but that should work, just figure out the exact syntax. — bamblack
– bamblack, Commented May 16, 2014 at 14:58
Regarding the suggested approach in my comment above, well, it is wrong (see, believing is no substitute for thinking). Read Laoujin's answer below for a short explanation. — user2819245
– user2819245, Commented May 16, 2014 at 17:39

tezromania · Accepted Answer · 2014-05-16 15:18:11Z

5

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
}

answered May 16, 2014 at 15:18

tezromania

8171 gold badge6 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tezromania Over a year ago

I should also mention that the code above uses the json.net library which can be found through nuget packages and referenced at this link here: james.newtonking.com/json

Laoujin · Accepted Answer · 2014-05-16 15:17:10Z

3

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...

answered May 16, 2014 at 15:17

Laoujin

10.3k7 gold badges45 silver badges72 bronze badges

4 Comments

user3644868 Over a year ago

Thank you for your suggestion. unfortunately, it does not work. The problem is, that "Context = n.Parent" contains not the root data of filtered location code. Instead of it contains the first entry of "OfflineVerkaufspreis".

Laoujin Over a year ago

Add another .Parent and it should work? Also check tezromania's answer: If the format or what you want to do with it changes often, such approach will make it much easier to make changes (different where clauses for example) as you get rid of all the ["xxx"] and instead can use a more natural c# approach.

Laoujin Over a year ago

Note that with .Parent.Parent the Context property will contain ALL OfflineVerkaufspreis, not just the one that matched the where. The Location property contains just the matching location. So you might want to add another .Parent there aswell.

user3644868 Over a year ago

Thanks again for your quick response. I added some .Parent but the result was not was I expected. Then I changed my code so it is possible to use DeSerializeObject.

mg_dev · Accepted Answer · 2014-05-16 16:05:26Z

1

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.

answered May 16, 2014 at 16:05

mg_dev

1,40314 silver badges32 bronze badges

Collectives™ on Stack Overflow

How to filter JSON array in C#

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related