LINQ to JSON - Setup list from different arrays

Question

I have a polymorphic json string. Here's what it looks like:

{
    "Product" : {
        "Context" : {
            "IssuerDetails" : {
                "Issuer" : {
                    "@clientCode" : "BMTEST-CA",
                    "@companyName" : "zTest BM Company, Inc",
                    "@companyId" : "1",
                    "IssuerChanges" : [{
                            "@type" : "Book Value",
                            "@previous" : "$9.06",
                            "@current" : "$55.34"
                        }, {
                            "@type" : "Price Target",
                            "@previous" : "$50.00",
                            "@current" : "$199.00"
                        }, {
                            "@type" : "EPS",
                            "@previous" : "2.10",
                            "@current" : "2.09",
                            "@period" : "5",
                            "@year" : "2017"
                        }, {
                            "@type" : "Income Tax",
                            "@previous" : "56",
                            "@current" : "55",
                            "@period" : "5",
                            "@year" : "2015"
                        }
                    ],
                    "SecurityDetails" : {
                        "Security" : {
                            "@primaryIndicator" : "Yes",
                            "Clusters" : [{
                                    "@name" : "Company Data",
                                    "@rank" : "2",
                                    "FinancialValue" : [{
                                            "@financialsType" : "Dividend",
                                            "CurrentValue" : {
                                                "@displayValue" : "$5.02",
                                            }
                                        }, {
                                            "@financialsType" : "Book Value",
                                            "CurrentValue" : {
                                                "@displayValue" : "$55.34",
                                            },
                                            "PreviousValue" : {
                                                "@displayValue" : "$9.06",
                                                "@type" : "INCREASE",
                                            }
                                        }
                                    ]
                                }, {
                                    "@rank" : "1",
                                    "@name" : "AAPL & Market Data",
                                    "FinancialValue" : [{
                                            "@financialsType" : "Rating",
                                            "@shortCode" : "Mkt",
                                            "CurrentValue" : {
                                                "@displayValue" : "Market Perform",
                                            }
                                        }, {
                                            "@financialsType" : "Rating Qualifier",
                                            "CurrentValue" : {
                                                "@displayValue" : "Speculative",
                                            }
                                        }
                                    ]
                                }
                            ]
                        }
                    }
                }
            }
        }
    }
}

I'm using the following extension class:

public static class JsonExtensions
{
    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;
    }
}

Based on that, here's my query:

JObject feed = JObject.Parse(jsonText);

var compInfo = from issuer in feed.SelectTokens("Product.Context.IssuerDetails.Issuer").SelectMany(i => i.ObjectsOrSelf())
       let issuerChanges = issuer.SelectTokens("IssuerChanges").SelectMany(s => s.ObjectsOrSelf())
       where issuerChanges != null 
       let finValues = issuer.SelectTokens("SecurityDetails.Security.Clusters").SelectMany(s => s.ObjectsOrSelf())
       where finValues != null
       select new
        {
           Id = (int)issuer["@companyId"], 
           BMOTicker = (string)issuer["@clientCode"],
           CompName = (string)issuer["@companyName"],
           ChngsType = issuerChanges.Select(c => (string)c["@type"]),
           PrevChng = issuerChanges.Select(c => (string)c["@previous"]),
           CurrChng = issuerChanges.Select(c => (string)c["@current"]),
           Period = issuerChanges.Select(c => (string)c["@period"]),
           Year = issuerChanges.Select(c => (string)c["@year"]),
           FinValueName = finValues.Select(c => (string)c["@financialsType"])
    };

When I tried the query, I don't get an error but get the following (from LinqPad):

The end result should look like this (simulated in Excel):

Any idea how can I get the end result?

Please fix the image links or post the actual and expected result as text — Matias Cicero, Jul 20 '16 at 19:21
Sorry about that. It was a formatting issue that prevented showing the images. — inquisitive_one, Jul 20 '16 at 19:25
What is the relationship between the FinType/Value pairs and the rest of the data in your desired/simulated result? I don't see why the "Dividend" FinType goes with the "Book Value" ChgsType, and the "BookValue" FinType goes with the "Price Target" ChgsType, etc. — Brian Rogers, Jul 20 '16 at 20:50
There isn't a relationship between them. Does that mean that this isn't possible? — inquisitive_one, Jul 21 '16 at 02:46
Not necessarily, but it seems strange/unintuitive to have unrelated data in the same "table". — Brian Rogers, Jul 21 '16 at 02:53

Dmitry Egorov · Answer 1 · 2016-07-21T09:56:32.930

First, you want to get rid of those ChngsType, PrevChng, etc. nested tables in the compInfo. You may use SelectMany() (fluent syntax) or recurrent from keywords (query syntax) like in this example:

var compInfoTest1 =
    from issuer in feed.SelectTokens("Product.Context.IssuerDetails.Issuer")
    from change in issuer["IssuerChanges"]
    select new { Company = issuer["@companyName"], Type = change["@type"] };

You can see the compInfoTest1 contains a flat Company-Type table:

{ Company = {zTest BM Company, Inc}, Type = {Book Value} }
{ Company = {zTest BM Company, Inc}, Type = {Price Target} }
{ Company = {zTest BM Company, Inc}, Type = {EPS} }
{ Company = {zTest BM Company, Inc}, Type = {Income Tax} }

Please note the ObjectsOrSelf() extension method is not required to do this.

Second, the ObjectsOrSelf() fails on the double nested structure of SecurityDetails (object->array->object->array->object -- it stops on the second object level thus failing to reach @financialsType). To deal with this, you need to use the same flattening technics repeating from for the every next inner level:

var compInfoTest2 =
    from issuer in feed.SelectTokens("Product.Context.IssuerDetails.Issuer")
    from cluster in issuer["SecurityDetails"]["Security"]["Clusters"]
    from finVal in cluster["FinancialValue"]
    select new { Company = issuer["@companyName"], FinType = finVal["@financialsType"] };

Here is the query result:

{ Company = {zTest BM Company, Inc}, FinType = {Dividend} }
{ Company = {zTest BM Company, Inc}, FinType = {Book Value} }
{ Company = {zTest BM Company, Inc}, FinType = {Rating} }
{ Company = {zTest BM Company, Inc}, FinType = {Rating Qualifier} }

Third, since there is no relationship between the FinType/Value pairs and the rest of the data, you can't write any condition to match items in the change and finVal sets. Instead, you may use Enumerable.Zip() to glue the "corresponding" items.

So the final query is:

var compInfo =
    from issuer in feed.SelectTokens("Product.Context.IssuerDetails.Issuer")
    let finValues = (
        from cluster in issuer["SecurityDetails"]["Security"]["Clusters"]
        from finVal in cluster["FinancialValue"]
        select new {
            FinValueName = (string)finVal["@financialsType"],
            Value = (string)finVal["CurrentValue"]["@displayValue"],
        }
    )
    from changesAndFinValues in issuer["IssuerChanges"].Zip(finValues, (c,f) => new {
            ChngsType = (string)c["@type"],
            PrevChng = (string)c["@previous"],
            CurrChng = (string)c["@current"],
            Period = (string)c["@period"],
            Year = (string)c["@year"],
            f.FinValueName,
            f.Value
    })
    select new
    {
        Id = (int)issuer["@companyId"],
        BMOTicker = (string)issuer["@clientCode"],
        CompName = (string)issuer["@companyName"],
        changesAndFinValues.ChngsType,
        changesAndFinValues.PrevChng,
        changesAndFinValues.CurrChng,
        changesAndFinValues.Period,
        changesAndFinValues.Year,
        changesAndFinValues.FinValueName,
        changesAndFinValues.Value,
    };

It produces the following result on your sample data:

{ Id = 1, BMOTicker = "BMTEST-CA", CompName = "zTest BM Company, Inc", ChngsType = "Book Value", PrevChng = "$9.06", CurrChng = "$55.34", Period = null, Year = null, FinValueName = "Dividend", Value = "$5.02" }
{ Id = 1, BMOTicker = "BMTEST-CA", CompName = "zTest BM Company, Inc", ChngsType = "Price Target", PrevChng = "$50.00", CurrChng = "$199.00", Period = null, Year = null, FinValueName = "Book Value", Value = "$55.34" }
{ Id = 1, BMOTicker = "BMTEST-CA", CompName = "zTest BM Company, Inc", ChngsType = "EPS", PrevChng = "2.10", CurrChng = "2.09", Period = "5", Year = "2017", FinValueName = "Rating", Value = "Market Perform" }
{ Id = 1, BMOTicker = "BMTEST-CA", CompName = "zTest BM Company, Inc", ChngsType = "Income Tax", PrevChng = "56", CurrChng = "55", Period = "5", Year = "2015", FinValueName = "Rating Qualifier", Value = "Speculative" }

LINQ to JSON - Setup list from different arrays

1 Answers1