Retrieving Mixpanel data from JSON in C#

Question

I am trying to get data from a Mixpanel API. The JSON result looks like this:

{"computed_at": "2019-11-19T10:36:33.908802+00:00", "legend_size": 1, "data": {"series": ["2019-11-11", "2019-11-12", "2019-11-13"], "values": {"page views": {"2019-11-12": 111, "2019-11-11": 573, "2019-11-13": 209}}}}

I am struggling with nested JSON (despite looking at a fair few StackOverflow articles on this) and I want to try and get the values 111,573 and 209 for the page views. How do I access these values?

I've tried the following:

var result = {"computed_at": "2019-11-19T10:36:33.908802+00:00", "legend_size": 1, "data": {"series": ["2019-11-11", "2019-11-12", "2019-11-13"], "values": {"page views": {"2019-11-12": 111, "2019-11-11": 573, "2019-11-13": 209}}}}
var rawData = result["data"]["values"]["page views"][0]

but this doesn't work and I can't figure out how to access the individual values. Can anyone help please?

EDIT:

When using the json to C# converter (as suggested in the comments to this post) the outcome is as follows:

public class PageViews
{
    public int __invalid_name__2019-11-20 { get; set; }
    public int __invalid_name__2019-11-19 { get; set; }
}

public class Values
{
    public PageViews __invalid_name__page views { get; set; }
}

public class Data
{
    public List<string> series { get; set; }
    public Values values { get; set; }
}

public class RootObject
{
    public DateTime computed_at { get; set; }
    public int legend_size { get; set; }
    public Data data { get; set; }
}

and I am still unsure how to get the numbers from this?

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

There are two approaches you could take. First, I'd deserialize the json string using this library - Newtonsoft.

You can then go through the json using JObjects, or you can create a C#-Class that represents your json data and then deserialize it into a corresponding object.

Via a class:

To create the json class there is this website, which creates the classes (also nested) just as the json needs them to be: http://json2csharp.com/

Then you use:

var myObject = JsonConvert.DeserializeObject<MyClass>(jsonString);

and then simply go through your object properties.

Via JObject

JObjects allow you to search through the json string easily as such:

JObject myObject = JObject.Parse(json);

var results = myObject["data"]["values"]["page views"].First();

Edit: Since your Json contains keys, which begin with a number (and are not valid c# variable names) its a bit more tricky but doable. The automatic converter failed on this.

  string json = "{\"computed_at\": \"2019 - 11 - 19T10: 36:33.908802 + 00:00\", \"legend_size\": 1, \"data\": {\"series\": [\"2019 - 11 - 11\", \"2019 - 11 - 12\", \"2019 - 11 - 13\"], \"values\": {\"page views\": {\"2019 - 11 - 12\": 111, \"2019 - 11 - 11\": 573, \"2019 - 11 - 13\": 209}}}}";

  var myObject = JsonConvert.DeserializeObject<RootObject>(json);

  foreach (var view in myObject.data.series)
  {
     int number = myObject.data.values.page_views[view];
     Console.WriteLine($"{number} views on {view}");
  }

With the RootObject class being:

 public class Values
{
    [JsonProperty("page views")]
    public Dictionary<string, int> page_views { get; set; }
}

public class Data
{
    public List<string> series { get; set; }
    public Values values { get; set; }
}

public class RootObject
{
    public string computed_at { get; set; }
    public int legend_size { get; set; }
    public Data data { get; set; }
}

The trick was to use a Dictionary<string, int> type for the date-values, and rename the property to a valid c# name. The JsonDeserializer still finds the data, because we told it to look for "page views" using the JsonProperty attribute. This way the class is also flexible for multiple entries in your "page views" field.

Hope this helps

Thanks so much @Luchspeter this is really useful. I'd like to go down the first route and create a C# class and deserialize it into a corresponding object. However, the conversion converts into lots of classes and I'm still not sure how to access the numbers from this? I have edited my original question showing this. — Jordan1993, Nov 20 '19 at 16:25
Ok I'll have a look at it. The reason is, that your json contains parameter names, that are not valid c# variable names. In c# a variable cannot start with a number. There are ways to work around this, and I'll go through them and edit the answer — Luchspeter, Nov 20 '19 at 17:52
thank you so much for your time and explanation with this - this really helped me. — Jordan1993, Nov 21 '19 at 08:52

koolahman · Answer 2 · 2019-11-20T16:21:23.580

You were close. In the line

var rawData = result["data"]["values"]["page views"][0]

you are attempting to access index 0 of an object. This doesn't really make sense because objects are basically dictionaries or hash tables, which means they are unordered and must be accessed by a key rather than an index (See Retrieving a property of a JSON object by index?).

What you want looks like

var rawData = result["data"]["values"]["page views"]['2019-11-12'] to access the number of page views (which is 111) for the date 2019-11-12.

You can then loop through the object and get each number of page views as follows:

var pageViews = [];
for (var key of Object.keys(result.data.values['page views'])) {
    pageViews.push(result.data.values['page views'][key]);
}

Retrieving Mixpanel data from JSON in C#

2 Answers2