Joao G. Ferreira
Reputation: 83
Accessing JSON dictionary with nested strings
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.
Upvotes: 0
Views: 80
Answers (2)
Joao G. Ferreira
Reputation: 83
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.
Upvotes: 1
Nkosi
Reputation: 247153
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"
Upvotes: 1
Related Questions
- Generating JSON from nested Dictionary in C#
- accessing dictionary from a Json string in c#
- Access nested values in json using System.Text.Json
- Dictionary in Json
- how to access nested object in JSON string
- How to iterate through nested Dictionary<string, object>?
- Access nested key value pairs
- Getting data out of a Dictionary<string,object>
- How can I iterate through nested dictionaries with JSON.NET?
- Dictionary in Json C#