查询JSON字符串以获取Date字段的最大值(小于或等于当前日期)对应的所有行

Question

我需要查询一个JSON字符串，以获取日期字段最大值（小于或等于当前日期）对应的所有行

我的JSON字符串如下，

[{"ID":"1","EffectiveDate":"27/04/2014","Channel":"Buy","Group":"UK","Rate":"7.0700","MinCharge":null,"MaxCharge":null},{"ID":"1","EffectiveDate":"28/04/2014","Channel":"Buy","Group":"UK","Rate":"6.8194","MinCharge":null,"MaxCharge":null},{"ID":"1","EffectiveDate":"27/04/2014","Channel":"Buy","Group":"UK","Rate":"15.3539","MinCharge":null,"MaxCharge":null},{"ID":"1","EffectiveDate":"23/04/2014","Channel":"Buy","Group":"UK","Rate":"2.1100","MinCharge":null,"MaxCharge":null},{"ID":"1","EffectiveDate":"22/04/2014","Channel":"Buy","Group":"UK","Rate":"2.0385","MinCharge":null,"MaxCharge":null},{"ID":"1","EffectiveDate":"23/04/2014","Channel":"Buy","Group":"UK","Rate":"2.1100","MinCharge":null,"MaxCharge":null},{"ID":"1","EffectiveDate":"27/04/2014","Channel":"Buy","Group":"UK","Rate":"2.0357","MinCharge":null,"MaxCharge":null},{"ID":"1","EffectiveDate":"29/04/2014","Channel":"Buy","Group":"UK","Rate":"3.8600","MinCharge":null,"MaxCharge":null},{"ID":"1","EffectiveDate":"18/04/2014","Channel":"Buy","Group":"UK","Rate":"3.7213","MinCharge":null,"MaxCharge":null},{"ID":"1","EffectiveDate":"27/04/2014","Channel":"Buy","Group":"UK","Rate":"151.0686","MinCharge":null,"MaxCharge":null},{"ID":"1","EffectiveDate":"26/04/2014","Channel":"Buy","Group":"UK","Rate":"2.7700","MinCharge":null,"MaxCharge":null},{"ID":"1","EffectiveDate":"27/04/2014","Channel":"Buy","Group":"UK","Rate":"2.6717","MinCharge":null,"MaxCharge":null},{"ID":"1","EffectiveDate":"27/04/2014","Channel":"Buy","Group":"UK","Rate":"0.7500","MinCharge":null,"MaxCharge":null},{"ID":"1","EffectiveDate":"26/04/2014","Channel":"Buy","Group":"UK","Rate":"0.7164","MinCharge":null,"MaxCharge":null},{"ID":"1","EffectiveDate":"27/04/2014","Channel":"Buy","Group":"UK","Rate":"1.9400","MinCharge":null,"MaxCharge":null},{"ID":"1","EffectiveDate":"23/04/2014","Channel":"Buy","Group":"UK","Rate":"2.5500","MinCharge":null,"MaxCharge":null}]

然后，需要在JSON数组中查询具有"最大有效日期值"one_answers"有效日期值小于或等于当前日期"的行

即，假设CurrentDate为2014年4月24日，则输出应为

[{"ID":"1","EffectiveDate":"23/04/2014","Channel":"Buy","Group":"UK","Rate":"2.1100","MinCharge":null,"MaxCharge":null},{"ID":"1","EffectiveDate":"23/04/2014","Channel":"Buy","Group":"UK","Rate":"2.1100","MinCharge":null,"MaxCharge":null},{"ID":"1","EffectiveDate":"23/04/2014","Channel":"Buy","Group":"UK","Rate":"2.5500","MinCharge":null,"MaxCharge":null}]

我正在使用Newtonsoft.Json编写LINQ查询。

在这里寻找一些帮助，因为我是JSON 的新手

Answer 1

我想在这种情况下我应该做的是将JSON反序列化为强类型对象的列表，对该列表进行操作，然后将其序列化回JSON。Net使序列化/反序列化部分变得非常容易，所以您只需要担心介于两者之间的查询。

首先，为项目定义一个简单的类：

class Item
{
    public string ID { get; set; }
    public DateTime EffectiveDate { get; set; }
    public string Channel { get; set; }
    public string Group { get; set; }
    public string Rate { get; set; }
    public object MinCharge { get; set; }
    public object MaxCharge { get; set; }
}

然后，您可以使用以下代码获得您想要的结果，其中json包含问题中的原始JSON：

// A date converter is necessary to handle the non-default date format
IsoDateTimeConverter dateConverter = new IsoDateTimeConverter
{
    DateTimeFormat = "dd/MM/yyyy"
};
// deserialize the JSON into a list of Item objects
List<Item> items = 
    JsonConvert.DeserializeObject<List<Item>>(json, dateConverter);
// find the max date among the items that is strictly less than today's date
DateTime maxDate = 
    items.Where(i => i.EffectiveDate < DateTime.Today).Max(i => i.EffectiveDate);
// with this max date in hand, filter the list to items matching that date
List<Item> result = 
    items.Where(i => i.EffectiveDate == maxDate).ToList();
// serialize the resulting list back to JSON
string jsonResult =
    JsonConvert.SerializeObject(result, Formatting.Indented, dateConverter);
// print out the new JSON (or whatever you need to do with it)
Console.WriteLine(jsonResult);