JSON tabular format parsing in c#

1
votes

I'm trying to work with CPI data from the Bureau of Labor and Statistics and I am having trouble deserializing the JSON data below:

{ "status": "REQUEST_SUCCEEDED", "responseTime": 66, "message": [], "Results": { "series": [ { "seriesID": "CUUR0000SA0", "data": [ { "year": "2015", "period": "M10", "periodName": "October", "value": "237.838", "footnotes": [ {} ] }, { "year": "2015", "period": "M09", "periodName": "September", "value": "237.945", "footnotes": [ {} ] }, { "year": "2015", "period": "M08", "periodName": "August", "value": "238.316", "footnotes": [ {} ] }, { "year": "2015", "period": "M07", "periodName": "July", "value": "238.654", "footnotes": [ {} ] }, { "year": "2015", "period": "M06", "periodName": "June", "value": "238.638", "footnotes": [ {} ] }, { "year": "2015", "period": "M05", "periodName": "May", "value": "237.805", "footnotes": [ {} ] }, { "year": "2015", "period": "M04", "periodName": "April", "value": "236.599", "footnotes": [ {} ] }, { "year": "2015", "period": "M03", "periodName": "March", "value": "236.119", "footnotes": [ {} ] }, { "year": "2015", "period": "M02", "periodName": "February", "value": "234.722", "footnotes": [ {} ] }, { "year": "2015", "period": "M01", "periodName": "January", "value": "233.707", "footnotes": [ {} ] }, { "year": "2014", "period": "M13", "periodName": "Annual", "value": "236.736", "footnotes": [ {} ] }, { "year": "2014", "period": "M12", "periodName": "December", "value": "234.812", "footnotes": [ {} ] }, { "year": "2014", "period": "M11", "periodName": "November", "value": "236.151", "footnotes": [ {} ] }, { "year": "2014", "period": "M10", "periodName": "October", "value": "237.433", "footnotes": [ {} ] }, { "year": "2014", "period": "M09", "periodName": "September", "value": "238.031", "footnotes": [ {} ] }, { "year": "2014", "period": "M08", "periodName": "August", "value": "237.852", "footnotes": [ {} ] }, { "year": "2014", "period": "M07", "periodName": "July", "value": "238.250", "footnotes": [ {} ] }, { "year": "2014", "period": "M06", "periodName": "June", "value": "238.343", "footnotes": [ {} ] }, { "year": "2014", "period": "M05", "periodName": "May", "value": "237.900", "footnotes": [ {} ] }, { "year": "2014", "period": "M04", "periodName": "April", "value": "237.072", "footnotes": [ {} ] }, { "year": "2014", "period": "M03", "periodName": "March", "value": "236.293", "footnotes": [ {} ] }, { "year": "2014", "period": "M02", "periodName": "February", "value": "234.781", "footnotes": [ {} ] }, { "year": "2014", "period": "M01", "periodName": "January", "value": "233.916", "footnotes": [ {} ] }, { "year": "2013", "period": "M13", "periodName": "Annual", "value": "232.957", "footnotes": [ {} ] }, { "year": "2013", "period": "M12", "periodName": "December", "value": "233.049", "footnotes": [ {} ] }, { "year": "2013", "period": "M11", "periodName": "November", "value": "233.069", "footnotes": [ {} ] }, { "year": "2013", "period": "M10", "periodName": "October", "value": "233.546", "footnotes": [ {} ] }, { "year": "2013", "period": "M09", "periodName": "September", "value": "234.149", "footnotes": [ {} ] }, { "year": "2013", "period": "M08", "periodName": "August", "value": "233.877", "footnotes": [ {} ] }, { "year": "2013", "period": "M07", "periodName": "July", "value": "233.596", "footnotes": [ {} ] }, { "year": "2013", "period": "M06", "periodName": "June", "value": "233.504", "footnotes": [ {} ] }, { "year": "2013", "period": "M05", "periodName": "May", "value": "232.945", "footnotes": [ {} ] }, { "year": "2013", "period": "M04", "periodName": "April", "value": "232.531", "footnotes": [ {} ] }, { "year": "2013", "period": "M03", "periodName": "March", "value": "232.773", "footnotes": [ {} ] }, { "year": "2013", "period": "M02", "periodName": "February", "value": "232.166", "footnotes": [ {} ] }, { "year": "2013", "period": "M01", "periodName": "January", "value": "230.280", "footnotes": [ {} ] } ] } ] } }

My c# is below... what is interesting is that if I use the RootObject class I actually return the status and response time but if I use the Datum class I get nothing:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Net;
using System.Data.SqlClient;
using System.Web;
using System.Configuration;
using System.Data;
using System.Globalization;
using System.ComponentModel;
using Newtonsoft.Json;
using System.Web.Script.Serialization;
using System.IO;


namespace ConsoleApplication5
{

public class Footnote
{
}

public class Datum
{
    public string year { get; set; }
    public string period { get; set; }
    public string periodName { get; set; }
    public string value { get; set; }
    public List<Footnote> footnotes { get; set; }

    public override string ToString()
    {
        return string.Format("Year: {0}", year);
    }
}

public class Series
{
    public string seriesID { get; set; }
    public List<Datum> data { get; set; }

    public override string ToString()
    {
        return string.Format("Series: {0} \n Data: {1}", seriesID, data);
    }
}

public class Results
{
    public List<Series> series { get; set; }
}

public class RootObject
{
    public string status { get; set; }
    public int responseTime { get; set; }
    public List<object> message { get; set; }
    public Results Results { get; set; }

    public override string ToString()
    {
        return string.Format("Status: {0} \n Response Time: {1}", status, responseTime);
    }
}

class Program
{

    static void Main(string[] args)
    {
        TryParse();
    }


    static void getJson()
    {

    }

    static void TryParse()
    {
        //WebRequest request = WebRequest.Create("http://api.bls.gov/publicAPI/v1/timeseries/data/CUUR0000SA0");
        //WebResponse response = request.GetResponse();

        //string jsonString = response.ToString();

         string jsonString = File.ReadAllText(@"C:\Users\Desktop\json.txt");


        ////This returns nothing
        Datum p1 = JsonConvert.DeserializeObject<Datum>(jsonString);


        ////This works and returns the data
        //RootObject p1 = JsonConvert.DeserializeObject<RootObject>(jsonString);



        Console.WriteLine(p1);
        Console.ReadLine();


        }

    }
}

I would like to be able to list out the year, periodName, and values for all of the Datum. Could anyone help me extract this data from this JSON?

4
c#jsonparsingdeserialization

4 Answers

2
votes

You must start with RootObject because that is how the json is formatted. Then you can navigate to the Datum Array.

RootObject p1 = JsonConvert.DeserializeObject<RootObject>(jsonString);
Console.WriteLine(p1.Results.series[0].data[0].year);

To List all values, you can loop:

foreach(Datum d in p1.Results.series[0].data)
{
      Console.WriteLine(d.year + " : " + d.period + " : " + d.periodName + " : " + d.value);
}
1
votes

I believe your information is there you just need to go dig a little bit. In your root object class you could access your Datum Information like so, 

RootObject p1 = JsonConvert.DeserializeObject<RootObject>(jsonString);
// Keep in mind this will only iterate over first Series, if you have multiple
// series you would need to do a nested loop over that collection.
foreach(Datum item in p1.Results.series.First().data)
{
     //display Item info.
}
1
votes

if you have a full json like the one you posted, you can't deserialize from it straight your datum, you need to parse the whole structure, so this is why

RootObject p1 = JsonConvert.DeserializeObject<RootObject>(jsonString);

works and returns data

at this point you have just to navigate the RootObject to find the data you need

and Datum isn't a single data but it's nested into the object hierarcy so you need to get it from the right place

Your RootObject has a list of Series (series property), and each Series has a list of Datum (data property), so you have to do an aggregate function or get a specific series and then inspect it's Datum list

giving your json, this should be enough (untested)

RootObject p1 = JsonConvert.DeserializeObject<RootObject>(jsonString);
var datas = p1.Result.series[0].data;

update: I've created a fiddle for you that show you the full process to read your datum: https://dotnetfiddle.net/dxel3K

e.g. to dump in console your datum this is what you need

RootObject p1 = JsonConvert.DeserializeObject<RootObject>(jsonString);

    foreach(var data in p1.Results.series[0].data){
        Console.WriteLine(String.Format("year: {0}; period: {1}; periodName: {2}", data.year,data.period,data.periodName));
    }
0
votes

Using the Javascript Serializer :

var parser = new JavaScriptSerializer();
var data = parser.Deserialize<dynamic>(missionGeoData);

foreach (var temp in data){       
     yourList.Add(temp["fieldName"].ToString());
}