c# – LINQ to JSON – 查询对象或数组

发布时间：2020-12-15 23:32:06 所属栏目：百科来源：网络整理

导读：我正在试图获得一份SEDOL ADP值.以下是我的json文字： { "DataFeed" : { "@FeedName" : "AdminData","Issuer" : [{ "id" : "1528","name" : "ZYZ.A a Test Company","clientCode" : "ZYZ.A","securities" : { "Security" : { "id" : "1537","sedol" : "SEDOL

我正在试图获得一份SEDOL& ADP值.以下是我的json文字：

{
    "DataFeed" : {
        "@FeedName" : "AdminData","Issuer" : [{
                "id" : "1528","name" : "ZYZ.A a Test Company","clientCode" : "ZYZ.A","securities" : {
                    "Security" : {
                        "id" : "1537","sedol" : "SEDOL111","coverage" : {
                            "Coverage" : [{
                                    "analyst" : {
                                        "@id" : "164","@clientCode" : "SJ","@firstName" : "Steve","@lastName" : "Jobs","@rank" : "1"
                                    }
                                },{
                                    "analyst" : {
                                        "@id" : "261","@clientCode" : "BG","@firstName" : "Bill","@lastName" : "Gates","@rank" : "2"
                                    }
                                }
                            ]
                        },"customFields" : {
                            "customField" : [{
                                    "@name" : "ADP Security Code","@type" : "Textbox","values" : {
                                        "value" : "ADPSC1111"
                                    }
                                },{
                                    "@name" : "Top 10 - Select one or many","@type" : "Dropdown,multiple choice","values" : {
                                        "value" : ["Large Cap","Cdn Small Cap","Income"]
                                    }
                                }
                            ]
                        }
                    }
                }
            },{
                "id" : "1519","name" : "ZVV Test","clientCode" : "ZVV=US","securities" : {
                    "Security" : [{
                            "id" : "1522","sedol" : "SEDOL112","coverage" : {
                                "Coverage" : {
                                    "analyst" : {
                                        "@id" : "79","@clientCode" : "MJ","@firstName" : "Michael","@lastName" : "Jordan","@rank" : "1"
                                    }
                                }
                            },"customFields" : {
                                "customField" : [{
                                        "@name" : "ADP Security Code","values" : {
                                            "value" : "ADPS1133"
                                        }
                                    },{
                                        "@name" : "Top 10 - Select one or many","values" : {
                                            "value" : ["Large Cap","Income"]
                                        }
                                    }
                                ]
                            }
                        },{
                            "id" : "1542","Income"]
                                        }
                                    }
                                ]
                            }
                        }
                    ]
                }
            }
        ]
    }
}

这是我到目前为止的代码：

var compInfo = feed["DataFeed"]["Issuer"]
.Select(p => new {  
    Id = p["id"],CompName = p["name"],SEDOL = p["securities"]["Security"].OfType<JArray>() ? 
        p["securities"]["Security"][0]["sedol"] : 
        p["securities"]["Security"]["sedol"]
    ADP = p["securities"]["Security"].OfType<JArray>() ? 
        p["securities"]["Security"][0]["customFields"]["customField"][0]["values"]["value"] : 
        p["securities"]["Security"]["customFields"]["customField"][0]["values"]["value"]
});

我得到的错误是：

Accessed JArray values with invalid key value: “sedol”. Int32 array
index expected

我想我真的很接近搞清楚了.我该怎么做来修复代码？如果有替代方法可以获得SEDOL和ADP值,请告诉我们？

[UPDATE1]我开始使用动态ExpandoObject.这是我到目前为止使用的代码：

dynamic obj = JsonConvert.DeserializeObject<ExpandoObject>(json,new ExpandoObjectConverter());
foreach (dynamic element in obj)
{
    Console.WriteLine(element.DataFeed.Issuer[0].id);
    Console.WriteLine(element.DataFeed.Issuer[0].securities.Security.sedol);
    Console.ReadLine();
}

但我现在得到错误’ExpandoObject’不包含’DataFeed’的定义,并且没有扩展方法’DataFeed’可以找到接受类型’ExpandoObject’的第一个参数.注意：我知道这个json文本格式不正确.一个实例有一个阵列&另一个是对象.我希望代码足够敏捷以处理这两个实例.

[UPDATE2]感谢@dbc帮助我使用我的代码到目前为止.我已经更新了上面的json文本,以便与我当前的环境紧密匹配.我现在能够得到SEDOL& ADP代码.但是,当我试图获得第一个分析师时,我的代码只适用于对象,并为作为数组一部分的分析人员生成空值.这是我目前的代码：

var compInfo = from issuer in feed.SelectTokens("DataFeed.Issuer").SelectMany(i => i.ObjectsOrSelf())
           let security = issuer.SelectTokens("securities.Security").SelectMany(s => s.ObjectsOrSelf()).FirstOrDefault()
           where security != null
           select new
           {
               Id = (string)issuer["id"],// Change to (string)issuer["id"] if id is not necessarily numeric.
               CompName = (string)issuer["name"],SEDOL = (string)security["sedol"],ADP = security["customFields"]
                .DescendantsAndSelf()
                .OfType<JObject>()
                .Where(o => (string)o["@name"] == "ADP Security Code")
                .Select(o => (string)o.SelectToken("values.value"))
                .FirstOrDefault(),Analyst = security["coverage"]
                .DescendantsAndSelf()
                .OfType<JObject>()
                .Select(jo => (string)jo.SelectToken("Coverage.analyst.@lastName"))
                .FirstOrDefault(),};

我需要更改为始终选择第一位分析师？

解决方法

如果你想要所有的SEDOL&每个ADP值与关联的发行者ID和CompName,您可以这样做：

var compInfo = from issuer in feed.SelectTokens("DataFeed.Issuer").SelectMany(i => i.ObjectsOrSelf())
               from security in issuer.SelectTokens("securities.Security").SelectMany(s => s.ObjectsOrSelf())
               select new
               {
                   Id = (long)issuer["id"],// Change to (string)issuer["id"] if id is not necessarily numeric.
                   CompName = (string)issuer["name"],ADP = security["customFields"]
                    .DescendantsAndSelf()
                    .OfType<JObject>()
                    .Where(o => (string)o["@name"] == "ADP Security Code")
                    .Select(o => (string)o.SelectToken("values.value"))
                    .FirstOrDefault(),};

使用扩展方法：

public static class JsonExtensions
{
    public static IEnumerable<JToken> DescendantsAndSelf(this JToken node)
    {
        if (node == null)
            return Enumerable.Empty<JToken>();
        var container = node as JContainer;
        if (container != null)
            return container.DescendantsAndSelf();
        else
            return new[] { node };
    }

    public static IEnumerable<JObject> ObjectsOrSelf(this JToken root)
    {
        if (root is JObject)
            yield return (JObject)root;
        else if (root is JContainer)
            foreach (var item in ((JContainer)root).Children())
                foreach (var child in item.ObjectsOrSelf())
                    yield return child;
        else
            yield break;
    }
}

然后

Console.WriteLine(JsonConvert.SerializeObject(compInfo,Formatting.Indented));

生产：

06003

但是,在您迄今为止编写的查询中,您似乎只是尝试返回第一个SEDOL&每个发行人的ADP.如果这真的是你想要的,那么：

var compInfo = from issuer in feed.SelectTokens("DataFeed.Issuer").SelectMany(i => i.ObjectsOrSelf())
               let security = issuer.SelectTokens("securities.Security").SelectMany(s => s.ObjectsOrSelf()).FirstOrDefault()
               where security != null
               select new
               {
                   Id = (long)issuer["id"],};

结果如下：

06005

顺便说一句,由于您的JSON是多态的(属性有时是对象的数组,有时只是对象),我不认为反序列化到类层次结构或ExpandoObject会更容易.

更新

根据您更新的JSON,您可以使用SelectTokens()和JSONPath recursive search operator ..来查找第一个分析师的姓氏,其中递归搜索运算符处理分析人员可能包含或不包含在数组中的事实：

var compInfo = from issuer in feed.SelectTokens("DataFeed.Issuer").SelectMany(i => i.ObjectsOrSelf())
               let security = issuer.SelectTokens("securities.Security").SelectMany(s => s.ObjectsOrSelf()).FirstOrDefault()
               where security != null
               select new
               {
                   Id = (string)issuer["id"],ADP = (string)security["customFields"]
                    .DescendantsAndSelf()
                    .OfType<JObject>()
                    .Where(o => (string)o["@name"] == "ADP Security Code")
                    .Select(o => o.SelectToken("values.value"))
                    .FirstOrDefault(),Analyst = (string)security.SelectTokens("coverage.Coverage..analyst.@lastName").FirstOrDefault(),};

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!