0

I am trying to read some data from the Eurostat server. I deserialize the string returned by the server using the Newtonsoft library:

Product product = JsonConvert.DeserializeObject<Product>(responseText);

The full JSON string is:

{
  "version": "2.0",
  "label": "Production from aquaculture excluding hatcheries and nurseries (from 2008 onwards)",
  "href": "http://ec.europa.eu/eurostat/wdds/rest/data/v2.1/json/en/fish_aq2a?precision=1&species=SAL&aquaenv=SEA&fishreg=0&fishreg=10&fishreg=27&fishreg=5&fishreg=9&fishreg=37&fishreg=34&fishreg=NSP&fishreg=4&fishreg=1&unit=EUR&unit=EUR_T&unit=TLW&aquameth=CAG",
  "source": "Eurostat",
  "updated": "2017-10-10",
  "status": {
    "0": ":",
    "3": ":",
    "5": ":",
    "6": ":",
    "7": ":",
    "13": ":",
    "14": ":",
    "15": ":",
    "17": ":",
    "18": ":",
    "19": ":",
    "20": ":",
    "21": ":",
    "22": ":",
    "23": ":",
    "24": ":",
    "27": ":",
    "28": ":",
    "29": ":",
    "30": ":",
    "31": ":",
    "37": ":",
    "38": ":",
    "39": ":",
    "40": ":",
    "45": ":",
    "46": ":",
    "47": ":",
    "48": ":",
    "51": ":",
    "53": ":",
    "54": ":",
    "55": ":",
    "61": ":",
    "62": ":",
    "63": ":",
    "65": ":",
    "66": ":",
    "67": ":",
    "68": ":",
    "69": ":",
    "70": ":",
    "71": ":",
    "72": ":",
    "75": ":",
    "76": ":",
    "77": ":",
    "78": ":",
    "79": ":",
    "85": ":",
    "86": ":",
    "87": ":",
    "88": ":",
    "93": ":",
    "94": ":",
    "95": ":",
    "96": ":",
    "99": ":",
    "101": ":",
    "102": ":",
    "103": ":",
    "109": ":",
    "110": ":",
    "111": ":",
    "113": ":",
    "114": ":",
    "115": ":",
    "116": ":",
    "117": ":",
    "118": ":",
    "119": ":",
    "120": ":",
    "123": ":",
    "124": ":",
    "125": ":",
    "126": ":",
    "127": ":",
    "133": ":",
    "134": ":",
    "135": ":",
    "136": ":",
    "141": ":",
    "142": ":",
    "143": ":",
    "144": ":",
    "147": ":",
    "149": ":",
    "150": ":",
    "151": ":",
    "157": ":",
    "158": ":",
    "159": ":",
    "161": ":",
    "162": ":",
    "163": ":",
    "164": ":",
    "165": ":",
    "166": ":",
    "167": ":",
    "168": ":",
    "171": ":",
    "172": ":",
    "173": ":",
    "174": ":",
    "175": ":",
    "181": ":",
    "182": ":",
    "183": ":",
    "184": ":",
    "189": ":",
    "190": ":",
    "191": ":",
    "192": ":",
    "195": ":",
    "197": ":",
    "198": ":",
    "199": ":",
    "205": ":",
    "206": ":",
    "207": ":",
    "209": ":",
    "210": ":",
    "211": ":",
    "212": ":",
    "213": ":",
    "214": ":",
    "215": ":",
    "216": ":",
    "219": ":",
    "220": ":",
    "221": ":",
    "222": ":",
    "223": ":",
    "229": ":",
    "230": ":",
    "231": ":",
    "232": ":",
    "237": ":",
    "238": ":",
    "239": ":",
    "240": ":",
    "243": ":",
    "245": ":",
    "246": ":",
    "247": ":",
    "253": ":",
    "254": ":",
    "255": ":",
    "257": ":",
    "258": ":",
    "259": ":",
    "260": ":",
    "261": ":",
    "262": ":",
    "263": ":",
    "264": ":",
    "267": ":",
    "268": ":",
    "269": ":",
    "270": ":",
    "271": ":",
    "277": ":",
    "278": ":",
    "279": ":",
    "280": ":",
    "285": ":",
    "286": ":",
    "287": ":",
    "288": ":",
    "291": ":",
    "293": ":",
    "294": ":",
    "295": ":",
    "301": ":",
    "302": ":",
    "303": ":",
    "305": ":",
    "306": ":",
    "307": ":",
    "308": ":",
    "309": ":",
    "310": ":",
    "311": ":",
    "312": ":",
    "315": ":",
    "316": ":",
    "317": ":",
    "318": ":",
    "319": ":",
    "325": ":",
    "326": ":",
    "327": ":",
    "328": ":",
    "333": ":",
    "334": ":",
    "335": ":",
    "336": ":",
    "339": ":",
    "341": ":",
    "342": ":",
    "343": ":",
    "349": ":",
    "350": ":",
    "351": ":",
    "353": ":",
    "354": ":",
    "355": ":",
    "356": ":",
    "357": ":",
    "358": ":",
    "359": ":",
    "360": ":",
    "363": ":",
    "364": ":",
    "365": ":",
    "366": ":",
    "367": ":",
    "373": ":",
    "374": ":",
    "375": ":",
    "376": ":",
    "381": ":",
    "382": ":",
    "383": ":",
    "384": ":",
    "387": ":",
    "389": ":",
    "390": ":",
    "391": ":",
    "397": ":",
    "398": ":",
    "399": ":",
    "401": ":",
    "402": ":",
    "403": ":",
    "404": ":",
    "405": ":",
    "406": ":",
    "407": ":",
    "408": ":",
    "411": ":",
    "412": ":",
    "413": ":",
    "414": ":",
    "415": ":",
    "421": ":",
    "422": ":",
    "423": ":",
    "424": ":",
    "429": ":",
    "430": ":",
    "431": ":"
  },
  "extension": {
    "datasetId": "fish_aq2a",
    "lang": "EN",
    "description": null,
    "subTitle": null,
    "status": {
      "label": {
        ":": "not available"
      }
    }
  },
  "class": "dataset",
  "value": {
    "1": 3243,
    "2": 356479,
    "4": 14938,
    "8": 6007500,
    "9": 529723936.38,
    "10": 585804684.37,
    "11": 677939444.53,
    "12": 641218586.15,
    "16": 6007500,
    "25": 65367944,
    "26": 70585535,
    "32": 1851387298,
    "33": 2311910972,
    "34": 3562084862,
    "35": 3454753657,
    "36": 3739702550,
    "41": 464352749,
    "42": 514862671,
    "43": 677939445,
    "44": 641179927,
    "49": 1989,
    "50": 4501,
    "52": 3734,
    "56": 4500,
    "57": 3376.93,
    "58": 3441.86,
    "59": 4286.31,
    "60": 3946.37,
    "64": 4500,
    "73": 5354,
    "74": 4499,
    "80": 2510,
    "81": 2679,
    "82": 3791,
    "83": 3241,
    "84": 3035,
    "89": 3210,
    "90": 3334,
    "91": 4286,
    "92": 3946,
    "97": 2,
    "98": 79,
    "100": 4,
    "104": 1335,
    "105": 156865.63,
    "106": 170200,
    "107": 158164,
    "108": 162483,
    "112": 1335,
    "121": 12210,
    "122": 15691,
    "128": 737694,
    "129": 862908,
    "130": 939575,
    "131": 1065975,
    "132": 1232095,
    "137": 144654,
    "138": 154430,
    "139": 158164,
    "140": 162479,
    "145": 3243,
    "146": 356479,
    "148": 14938,
    "152": 6007500,
    "153": 529723936.38,
    "154": 585804684.37,
    "155": 677939444.53,
    "156": 641218586.15,
    "160": 6007500,
    "169": 65367944,
    "170": 70585535,
    "176": 1851387298,
    "177": 2311910972,
    "178": 3562084862,
    "179": 3454753657,
    "180": 3739702550,
    "185": 464352749,
    "186": 514862671,
    "187": 677939445,
    "188": 641179927,
    "193": 1989,
    "194": 4501,
    "196": 3734,
    "200": 4500,
    "201": 3376.93,
    "202": 3441.86,
    "203": 4286.31,
    "204": 3946.37,
    "208": 4500,
    "217": 5354,
    "218": 4499,
    "224": 2510,
    "225": 2679,
    "226": 3791,
    "227": 3241,
    "228": 3035,
    "233": 3210,
    "234": 3334,
    "235": 4286,
    "236": 3946,
    "241": 2,
    "242": 79,
    "244": 4,
    "248": 1335,
    "249": 156865.63,
    "250": 170200,
    "251": 158164,
    "252": 162483,
    "256": 1335,
    "265": 12210,
    "266": 15691,
    "272": 737694,
    "273": 862908,
    "274": 939575,
    "275": 1065975,
    "276": 1232095,
    "281": 144654,
    "282": 154430,
    "283": 158164,
    "284": 162479,
    "289": 3243,
    "290": 356479,
    "292": 14938,
    "296": 6007500,
    "297": 529723936.38,
    "298": 585804684.37,
    "299": 677939444.53,
    "300": 641218586.15,
    "304": 6007500,
    "313": 65367944,
    "314": 70585535,
    "320": 1851387298,
    "321": 2311910972,
    "322": 3562084862,
    "323": 3454753657,
    "324": 3739702550,
    "329": 464352749,
    "330": 514862671,
    "331": 677939445,
    "332": 641179927,
    "337": 1989,
    "338": 4501,
    "340": 3734,
    "344": 4500,
    "345": 3376.93,
    "346": 3441.86,
    "347": 4286.31,
    "348": 3946.37,
    "352": 4500,
    "361": 5354,
    "362": 4499,
    "368": 2510,
    "369": 2679,
    "370": 3791,
    "371": 3241,
    "372": 3035,
    "377": 3210,
    "378": 3334,
    "379": 4286,
    "380": 3946,
    "385": 2,
    "386": 79,
    "388": 4,
    "392": 1335,
    "393": 156865.63,
    "394": 170200,
    "395": 158164,
    "396": 162483,
    "400": 1335,
    "409": 12210,
    "410": 15691,
    "416": 737694,
    "417": 862908,
    "418": 939575,
    "419": 1065975,
    "420": 1232095,
    "425": 144654,
    "426": 154430,
    "427": 158164,
    "428": 162479
  },
  "dimension": {
    "aquameth": {
      "label": "aquameth",
      "category": {
        "index": {
          "CAG": 0
        },
        "label": {
          "CAG": "Cages"
        }
      }
    },
    "aquaenv": {
      "label": "aquaenv",
      "category": {
        "index": {
          "SEA": 0
        },
        "label": {
          "SEA": "Seawater"
        }
      }
    },
    "species": {
      "label": "species",
      "category": {
        "index": {
          "SAL": 0
        },
        "label": {
          "SAL": "Atlantic salmon - Salmo salar"
        }
      }
    },
    "fishreg": {
      "label": "fishreg",
      "category": {
        "index": {
          "0": 0,
          "10": 1,
          "27": 2
        },
        "label": {
          "0": "Total fishing areas",
          "10": "Marine areas",
          "27": "Atlantic, Northeast"
        }
      }
    },
    "unit": {
      "label": "unit",
      "category": {
        "index": {
          "EUR": 0,
          "EUR_T": 1,
          "TLW": 2
        },
        "label": {
          "EUR": "Euro",
          "EUR_T": "Euro per tonne",
          "TLW": "Tonnes live weight"
        }
      }
    },
    "geo": {
      "label": "geo",
      "category": {
        "index": {
          "ES": 0,
          "EU28": 1,
          "FR": 2,
          "IE": 3,
          "NO": 4,
          "UK": 5
        },
        "label": {
          "ES": "Spain",
          "EU28": "European Union (28 countries)",
          "FR": "France",
          "IE": "Ireland",
          "NO": "Norway",
          "UK": "United Kingdom"
        }
      }
    },
    "time": {
      "label": "time",
      "category": {
        "index": {
          "2008": 0,
          "2009": 1,
          "2010": 2,
          "2011": 3,
          "2012": 4,
          "2013": 5,
          "2014": 6,
          "2015": 7
        },
        "label": {
          "2008": "2008",
          "2009": "2009",
          "2010": "2010",
          "2011": "2011",
          "2012": "2012",
          "2013": "2013",
          "2014": "2014",
          "2015": "2015"
        }
      }
    }
  },
  "id": [
    "aquameth",
    "aquaenv",
    "species",
    "fishreg",
    "unit",
    "geo",
    "time"
  ],
  "size": [
    1,
    1,
    1,
    3,
    3,
    6,
    8
  ]
}

In my Product class (MSVS 2015 C#) I have:

public Dictionary<string, dynamic> Extension { get; set; }

which works fine until I get to the "status" key, which gives an error.

The way I am getting around that is:

MyRow++;
MyCol = 0;
QueryOutputWS.ActiveWorksheet.Cells[MyRow, MyCol].Value = nameof(product.Extension) + ":";
foreach (string key in product.Extension.Keys)
{
    MyRow++;
    MyCol = 0;
    dynamic myvalue = key;
    QueryOutputWS.ActiveWorksheet.Cells[MyRow, MyCol].Value = myvalue;


    if (key == "status")
        break;

    MyCol = 1;
    myvalue = product.Extension[key];
    QueryOutputWS.ActiveWorksheet.Cells[MyRow, MyCol].Value = myvalue;
}

Note: the MyRow & MyCol variables are for output to a spreadsheet add-in, and not relevant to the issue. I've checked a number of examples on the web, mainly on stackoverflow, but cannot manage to resolve.

Improve this question
3
  • which gives an error. ... you forgot to include the exact error and stack trace. Commented Nov 25, 2017 at 20:00
  • Your json appears mal-formed. What is {"label":{":":"not available"}}} Commented Nov 26, 2017 at 1:56
  • Rene: apologies, the error I get is for the variable myvalue, which then gives the error if I send myvalue to the SpreadsheetGear workbook. The error output is: + $exception {"Invalid cell value type."} System.ArgumentException Shiva: I was avoiding listing the full json but have now done so in the original post (replaced the abridged listing that was there). Please see the section: "status":{"label":{":":"not available"}}} the string is the original from Eurostat. Commented Nov 27, 2017 at 10:07

2 Answers 2

Reset to default
1

Because of the ":" JSON key it will cause some problems with using dynamic and duck typing.

Consider creating a strong-type for that property Like below

public class Extension {
    public string datasetId { get; set; }
    public string lang { get; set; }
    public string description { get; set; }
    public string subTitle { get; set; }
    public ExtensionStatus status { get; set; }
}

public class ExtensionStatus {
    public Dictionary<string, string> label { get; set; }
}

and have

public Extension extension { get; set; }

in your Product class

For dimension property, what you need to do is look for the common pattern among the nested objects.

Based on provided JSON Dimension would resolve to the following

public class Dimension {
    public DimensionDetail aquameth { get; set; }
    public DimensionDetail aquaenv { get; set; }
    public DimensionDetail species { get; set; }
    public DimensionDetail fishreg { get; set; }
    public DimensionDetail unit { get; set; }
    public DimensionDetail geo { get; set; }
    public DimensionDetail time { get; set; }
}

public class DimensionDetail {
    public string label { get; set; }
    public Category category { get; set; }
}

public class Category {
    public Dictionary<string, int> index { get; set; }
    public Dictionary<string, string> label { get; set; }
}

With the property set on Product as

public Dimension dimension { get; set; }

That way after deserializing the string you can access it like 

var product = JsonConvert.DeserializeObject<Product>(responseText);
var value = product.extension.status.label[":"]; //value should equal "not available"
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Works perfectly, Nkosi. I tried the same approach with: "dimension",{"aquameth":{"label":"aquameth","category":{"index":{"CAG":0},"label":{"CAG":"Cages"}}} from the same json but am unable to get it to work. I don't know how to post the set of classes I created for that - it exceeds the comment size. If I could post them maybe you could help me understand why they won't work.
1

The way to read the json "dimension" component into a C# class is to create the following:

public class Dimension
    {
        public DimensionAquameth aquameth { get; set; }
    }
    public class DimensionAquameth
    {
        public string label { get; set; }
        public DimensionCategory category { get; set; }
    }

    public class DimensionCategory
    {
        public Dictionary<string, string> index { get; set; }
        public Dictionary<string, string> label { get; set; }
    }

and then add:

public Dimension dimension { get; set; }

to the Product class.

And in the code that parses the deserialized json:

// Dimension field
                MyRow++;
                MyCol = 0;
                QueryOutputWS.ActiveWorksheet.Cells[MyRow, MyCol].Value = nameof(product.dimension) + ":";
                // aquameth
                MyRow++;
                MyCol = 0;
                QueryOutputWS.ActiveWorksheet.Cells[MyRow, MyCol].Value = nameof(product.dimension.aquameth) + ":";
                // label
                MyRow++;
                MyCol = 0;
                QueryOutputWS.ActiveWorksheet.Cells[MyRow, MyCol].Value = nameof(product.dimension.aquameth.label) + ":";
                MyCol = 1;
                QueryOutputWS.ActiveWorksheet.Cells[MyRow, MyCol].Value = product.dimension.aquameth.label;
                // category
                MyRow++;
                MyCol = 0;
                QueryOutputWS.ActiveWorksheet.Cells[MyRow, MyCol].Value = nameof(product.dimension.aquameth.category) + ":";
                // category index
                MyRow++;
                MyCol = 0;
                QueryOutputWS.ActiveWorksheet.Cells[MyRow, MyCol].Value = nameof(product.dimension.aquameth.category.index) + ":";
                // CAG
                foreach (string key in product.dimension.aquameth.category.index.Keys)
                {
                    MyRow++;
                    MyCol = 0;
                    dynamic myvalue = key;
                    QueryOutputWS.ActiveWorksheet.Cells[MyRow, MyCol].Value = myvalue + ":";
                    MyCol = 1;
                    myvalue = product.dimension.aquameth.category.index[key];
                    QueryOutputWS.ActiveWorksheet.Cells[MyRow, MyCol].Value = myvalue;
                }

                MyRow++;
                MyCol = 0;
                QueryOutputWS.ActiveWorksheet.Cells[MyRow, MyCol].Value = nameof(product.dimension.aquameth.category.label) + ":";
                // CAG
                foreach (string key in product.dimension.aquameth.category.label.Keys)
                {
                    MyRow++;
                    MyCol = 0;
                    dynamic myvalue = key;
                    QueryOutputWS.ActiveWorksheet.Cells[MyRow, MyCol].Value = myvalue + ":";
                    MyCol = 1;
                    myvalue = product.dimension.aquameth.category.label[key];
                    QueryOutputWS.ActiveWorksheet.Cells[MyRow, MyCol].Value = myvalue;
                }

Please note that the QueryOutputWS reference, as well as MyRow, MyCol are specific to outputting the results decoded fields to a SpreadsheetGear worksheet - you can use any other output approach, including console outputs etc.

Improve this answer

5 Comments

So what about all the help provided by the answer I gave? If what I provided resolved your problem based on the fact that your answer basically uses exactly what I provide, the norm is to accept the answer and vote it up is it was useful to you.
As I said in my reply to your post, it works perfectly - I didn't say thanks because there is a specific guideline not to. I hardly ever ask questions on this site, and I looked for ways to accept the answer and rate you, couldn't work that out - tell me how to do that, and I'll happily do it.
FYI, I didn't see your answer to the dimension issue, (only saw it now), worked it out myself - based on how you'd helped solve the prior issue (extension), which I'm very grateful for. Let's be clear - I didn't post the answer as my own work because I saw your reply - I only posted because I wanted to help others just as you helped me.
Easy misunderstanding. It was not meant to be confrontational. That happens on this site a lot. My statement may have been misinterpreted. No worries. Happy coding.
You are very generous. I worked out how to flag your answer as correct, vote it up, and also for your comments. As far as I can tell, you should now have those scores.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.