Deserialize Complex Json in C# using Json.net

Question

I have some complex JSon that I am trying to parse into something meaningful. I'm attempting to deserialize using C# Json.net but I can't get the values that I need. What I need is the value from every ColData node except those in a "summary" section. I am able to deserialize into an object using but I am stuck there.

    string pandltext = @"{
  "Header": {
    "Time": "2017-08-24T08:32:58-07:00",
    "ReportName": "ProfitAndLoss",
    "ReportBasis": "Accrual",
    "StartPeriod": "2017-06-01",
    "EndPeriod": "2017-06-30",
    "SummarizeColumnsBy": "Total",
    "Currency": "USD",
    "Option": [
      {
        "Name": "AccountingStandard",
        "Value": "GAAP"
      },
      {
        "Name": "NoReportData",
        "Value": "false"
      }
    ]
  },
  "Columns": {
    "Column": [
      {
        "ColTitle": "",
        "ColType": "Account",
        "MetaData": [
          {
            "Name": "ColKey",
            "Value": "account"
          }
        ]
      },
      {
        "ColTitle": "Total",
        "ColType": "Money",
        "MetaData": [
          {
            "Name": "ColKey",
            "Value": "total"
          }
        ]
      }
    ]
  },
  "Rows": {
    "Row": [
      {
        "Header": {
          "ColData": [
            {
              "value": "Income"
            },
            {
              "value": ""
            }
          ]
        },
        "Rows": {
          "Row": [
            {
              "ColData": [
                {
                  "value": "Design income",
                  "id": "82"
                },
                {
                  "value": "975.00"
                }
              ],
              "type": "Data"
            },
            {
              "ColData": [
                {
                  "value": "Discounts given",
                  "id": "86"
                },
                {
                  "value": "-30.50"
                }
              ],
              "type": "Data"
            },
            {
              "Header": {
                "ColData": [
                  {
                    "value": "Landscaping Services",
                    "id": "45"
                  },
                  {
                    "value": "360.00"
                  }
                ]
              },
              "Rows": {
                "Row": [
                  {
                    "Header": {
                      "ColData": [
                        {
                          "value": "Job Materials",
                          "id": "46"
                        },
                        {
                          "value": ""
                        }
                      ]
                    },
                    "Rows": {
                      "Row": [
                        {
                          "ColData": [
                            {
                              "value": "Fountains and Garden Lighting",
                              "id": "48"
                            },
                            {
                              "value": "550.00"
                            }
                          ],
                          "type": "Data"
                        },
                        {
                          "ColData": [
                            {
                              "value": "Plants and Soil",
                              "id": "49"
                            },
                            {
                              "value": "1820.72"
                            }
                          ],
                          "type": "Data"
                        },
                        {
                          "ColData": [
                            {
                              "value": "Sprinklers and Drip Systems",
                              "id": "50"
                            },
                            {
                              "value": "30.00"
                            }
                          ],
                          "type": "Data"
                        }
                      ]
                    },
                    "Summary": {
                      "ColData": [
                        {
                          "value": "Total Job Materials"
                        },
                        {
                          "value": "2400.72"
                        }
                      ]
                    },
                    "type": "Section"
                  }
                ]
              },
              "Summary": {
                "ColData": [
                  {
                    "value": "Total Landscaping Services"
                  },
                  {
                    "value": "2760.72"
                  }
                ]
              },
              "type": "Section"
            },
            {
              "ColData": [
                {
                  "value": "Pest Control Services",
                  "id": "54"
                },
                {
                  "value": "-100.00"
                }
              ],
              "type": "Data"
            },
            {
              "ColData": [
                {
                  "value": "Sales of Product Income",
                  "id": "79"
                },
                {
                  "value": "44.00"
                }
              ],
              "type": "Data"
            },
            {
              "ColData": [
                {
                  "value": "Services",
                  "id": "1"
                },
                {
                  "value": "400.00"
                }
              ],
              "type": "Data"
            }
          ]
        },
        "Summary": {
          "ColData": [
            {
              "value": "Total Income"
            },
            {
              "value": "4049.22"
            }
          ]
        },
        "type": "Section",
        "group": "Income"
      },
      {
        "Summary": {
          "ColData": [
            {
              "value": "Gross Profit"
            },
            {
              "value": "4049.22"
            }
          ]
        },
        "type": "Section",
        "group": "GrossProfit"
      },
      {
        "Header": {
          "ColData": [
            {
              "value": "Expenses"
            },
            {
              "value": ""
            }
          ]
        },
        "Rows": {
          "Row": [
            {
              "Header": {
                "ColData": [
                  {
                    "value": "Automobile",
                    "id": "55"
                  },
                  {
                    "value": "19.99"
                  }
                ]
              },
              "Rows": {
                "Row": [
                  {
                    "ColData": [
                      {
                        "value": "Fuel",
                        "id": "56"
                      },
                      {
                        "value": "179.15"
                      }
                    ],
                    "type": "Data"
                  }
                ]
              },
              "Summary": {
                "ColData": [
                  {
                    "value": "Total Automobile"
                  },
                  {
                    "value": "199.14"
                  }
                ]
              },
              "type": "Section"
            },
            {
              "Header": {
                "ColData": [
                  {
                    "value": "Job Expenses",
                    "id": "58"
                  },
                  {
                    "value": "108.09"
                  }
                ]
              },
              "Rows": {
                "Row": [
                  {
                    "Header": {
                      "ColData": [
                        {
                          "value": "Job Materials",
                          "id": "63"
                        },
                        {
                          "value": ""
                        }
                      ]
                    },
                    "Rows": {
                      "Row": [
                        {
                          "ColData": [
                            {
                              "value": "Decks and Patios",
                              "id": "64"
                            },
                            {
                              "value": "88.09"
                            }
                          ],
                          "type": "Data"
                        }
                      ]
                    },
                    "Summary": {
                      "ColData": [
                        {
                          "value": "Total Job Materials"
                        },
                        {
                          "value": "88.09"
                        }
                      ]
                    },
                    "type": "Section"
                  }
                ]
              },
              "Summary": {
                "ColData": [
                  {
                    "value": "Total Job Expenses"
                  },
                  {
                    "value": "196.18"
                  }
                ]
              },
              "type": "Section"
            },
            {
              "Header": {
                "ColData": [
                  {
                    "value": "Legal & Professional Fees",
                    "id": "12"
                  },
                  {
                    "value": ""
                  }
                ]
              },
              "Rows": {
                "Row": [
                  {
                    "ColData": [
                      {
                        "value": "Accounting",
                        "id": "69"
                      },
                      {
                        "value": "75.00"
                      }
                    ],
                    "type": "Data"
                  },
                  {
                    "ColData": [
                      {
                        "value": "Lawyer",
                        "id": "71"
                      },
                      {
                        "value": "100.00"
                      }
                    ],
                    "type": "Data"
                  }
                ]
              },
              "Summary": {
                "ColData": [
                  {
                    "value": "Total Legal & Professional Fees"
                  },
                  {
                    "value": "175.00"
                  }
                ]
              },
              "type": "Section"
            },
            {
              "ColData": [
                {
                  "value": "Maintenance and Repair",
                  "id": "72"
                },
                {
                  "value": "185.00"
                }
              ],
              "type": "Data"
            },
            {
              "ColData": [
                {
                  "value": "Meals and Entertainment",
                  "id": "13"
                },
                {
                  "value": "5.66"
                }
              ],
              "type": "Data"
            },
            {
              "ColData": [
                {
                  "value": "Rent or Lease",
                  "id": "17"
                },
                {
                  "value": "900.00"
                }
              ],
              "type": "Data"
            },
            {
              "Header": {
                "ColData": [
                  {
                    "value": "Utilities",
                    "id": "24"
                  },
                  {
                    "value": ""
                  }
                ]
              },
              "Rows": {
                "Row": [
                  {
                    "ColData": [
                      {
                        "value": "Gas and Electric",
                        "id": "76"
                      },
                      {
                        "value": "114.09"
                      }
                    ],
                    "type": "Data"
                  },
                  {
                    "ColData": [
                      {
                        "value": "Telephone",
                        "id": "77"
                      },
                      {
                        "value": "74.36"
                      }
                    ],
                    "type": "Data"
                  }
                ]
              },
              "Summary": {
                "ColData": [
                  {
                    "value": "Total Utilities"
                  },
                  {
                    "value": "188.45"
                  }
                ]
              },
              "type": "Section"
            }
          ]
        },
        "Summary": {
          "ColData": [
            {
              "value": "Total Expenses"
            },
            {
              "value": "1849.43"
            }
          ]
        },
        "type": "Section",
        "group": "Expenses"
      },
      {
        "Summary": {
          "ColData": [
            {
              "value": "Net Operating Income"
            },
            {
              "value": "2199.79"
            }
          ]
        },
        "type": "Section",
        "group": "NetOperatingIncome"
      },
      {
        "Header": {
          "ColData": [
            {
              "value": "Other Expenses"
            },
            {
              "value": ""
            }
          ]
        },
        "Rows": {
          "Row": [
            {
              "ColData": [
                {
                  "value": "Miscellaneous",
                  "id": "14"
                },
                {
                  "value": "916.00"
                }
              ],
              "type": "Data"
            }
          ]
        },
        "Summary": {
          "ColData": [
            {
              "value": "Total Other Expenses"
            },
            {
              "value": "916.00"
            }
          ]
        },
        "type": "Section",
        "group": "OtherExpenses"
      },
      {
        "Summary": {
          "ColData": [
            {
              "value": "Net Other Income"
            },
            {
              "value": "-916.00"
            }
          ]
        },
        "type": "Section",
        "group": "NetOtherIncome"
      },
      {
        "Summary": {
          "ColData": [
            {
              "value": "Net Income"
            },
            {
              "value": "1283.79"
            }
          ]
        },
        "type": "Section",
        "group": "NetIncome"
      }
    ]
  }
}


// Deserialize to object
var rootObj = JsonConvert.DeserializeObject<ProfitLoss.Rootobject>( pandltext );

I've tried querying a JContainer like is mentioned in this post. I've tried deserlializing a fragment like is mentioned in the documentation and I've tried using linq as mentioned here in the documentation. So far all of my efforts have met varying degrees of "success" but none have yielded the values I'm trying to get. Eventually this data will be bound to a WPF DataGrid for viewing.

Edit: Added entire Json file

These are a couple attempts to get something, but I run into null values in both cases.

        // This always returns null
        var results2 = doc.Descendants()
                    .OfType<JObject>()
                    .Where( x => x[ "value" ] != null );


        // This gives me a null exception error 
        var doc1 = ( JContainer ) o[ "Rows" ];
        foreach ( var row in rootObj.Rows.Row )
        {
                // Get a null exception
                foreach ( var row2 in row.Rows.Row )
                {
                    Console.WriteLine( row2.ToString() );
                }

        }

Edit 2: Using what @Eser gave as a starting point, I am able to get a list of values, but unfortunately it's just a list of values. Instead of getting something like

"Design income", "975.00"
"Discounts given", "-30.50"

I get

"Design income"
"975"
"Discounts given"
"-30.50"

Here is the code I'm using to get a list of values:

var jObj = JObject.Parse( pandltext );
var results = jObj.SelectTokens( "$..Rows.Row[?(@.type == 'Data')]..value" ).ToList();

Please post some examples of what you've tried, with code, and describe how it is not working as expected. — Devin Goble
– Devin Goble, Commented Aug 25, 2017 at 20:35
Also, post a valid/complete json, so that people can work on it. — L.B
– L.B, Commented Aug 25, 2017 at 20:37
@Youssef13 you can deserialize to objects then use Linq. Why would you want to use regex for this. — Dispersia
– Dispersia, Commented Aug 25, 2017 at 20:44
@Youssef13 use the appropriate tool. regex is not the appropriate tool here. — user47589
– user47589, Commented Aug 25, 2017 at 21:05

Eser · Accepted Answer · 2017-09-02 20:20:57Z

2

var jObj = JObject.Parse(json);

var colData = jObj.SelectTokens("$..ColData")
                .Except(jObj.SelectTokens("$..Summary.ColData"))
                .ToList();

EDIT

foreach(var item in colData)
{
    Console.WriteLine(string.Join("=", item.Select(x => x["value"])));
}

or

var finalList = colData.Select(item => item.Select(x => (string)x["value"]).ToList())
               .ToList();

edited Sep 2, 2017 at 20:20

answered Aug 25, 2017 at 21:19

Eser

12.6k2 gold badges24 silver badges32 bronze badges

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

3 Comments

mack Over a year ago

Thanks @Eser. I used this as a starting point to get a list of values, but I'm still trying to make it usable. Using the code from your answer I get a value that looks like this {[ { "value": "Design income", "id": "82" }, { "value": "975.00" } ]} and I haven't been able to access the values from the list. I need "Design Income" and "975.00".

Eser Over a year ago

@mack Take the tricky part of the answer into your question and extend your question with your new inability. Is this the way of asking a question? Anyway I updated the answer once and far all.

mack Over a year ago

I appreciate you taking the time to answer this. I wasn't intending to change the question with my edit, only add new information to the original question. Sorry if that changed the original question. Thanks again for your help. Your answer got me moving in the right direction again.

PurTahan · Accepted Answer · 2017-08-27 17:06:03Z

    public class Option
    {
         public string Name { get; set; }
         public string Value { get; set; }
    }

    public class Header
    {
        public DateTime Time { get; set; }
        public string ReportName { get; set; }
        public string ReportBasis { get; set; }
        public string StartPeriod { get; set; }
        public string EndPeriod { get; set; }
        public string SummarizeColumnsBy { get; set; }
        public string Currency { get; set; }
        public IList<Option> Option { get; set; }
    }

    public class MetaData
    {
        public string Name { get; set; }
        public string Value { get; set; }
    }

    public class Column
    {
        public string ColTitle { get; set; }
        public string ColType { get; set; }
        public IList<MetaData> MetaData { get; set; }
    }

    public class Columns
    {
        public IList<Column> Column { get; set; }
    }

    public class ColData
    {
         public string value { get; set; }
         public string id { get; set; }
    }

    public class ColData
    {
         public string value { get; set; }
         public string id { get; set; }
    }

    public class Row
    {
        public IList<ColData> ColData { get; set; }
        public string type { get; set; }
    }

    public class Rows
    {
        public IList<Row> Row { get; set; }
    }

    public class ColData
    {
        public string value { get; set; }
    }

    public class Summary
    {
        public IList<ColData> ColData { get; set; }
    }

    public class ColData
    {
        public string value { get; set; }
        public string id { get; set; }
    }

    public class Row
    {
        public  Header { get; set; }
        public Rows Rows { get; set; }
        public Summary Summary { get; set; }
        public string type { get; set; }
        public IList<ColData> ColData { get; set; }
    }

    public class Rows
    {
        public IList<Row> Row { get; set; }
    }

    public class Row
    {
        public IList<ColData> ColData { get; set; }
        public string type { get; set; }
        public  Header { get; set; }
        public Rows Rows { get; set; }
        public  Summary { get; set; }
    }

    public class Rows
    {
        public IList<Row> Row { get; set; }
    }

    public class Row
    {
        public  Header { get; set; }
        public Rows Rows { get; set; }
        public  Summary { get; set; }
        public string type { get; set; }
        public string group { get; set; }
    }

    public class Rows
    {
        public IList<Row> Row { get; set; }
    }

    public class Example
    {
        public Header Header { get; set; }
        public Columns Columns { get; set; }
        public Rows Rows { get; set; }
    }

and use it with :

Example results =  Newtonsoft.JSON.JsonConvert.DeserializeObject<Example>(json);

Collectives™ on Stack Overflow

Deserialize Complex Json in C# using Json.net

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related