How to create column and row wise json structure?

Question

I am trying to create json structure like below:

{
  "cols": [
    {
      label: "Types",
      type: "string"
    },
    {
      label: "values",
      type: "number"
    }
  ],
  "rows": [
    {
      c: [
        {
          v: "Mushrooms"
        },
        {
          v: 3
        },
        
      ]
    },
    {
      c: [
        {
          v: "Olives"
        },
        {
          v: 31
        }
      ]
    },
    {
      c: [
        {
          v: "Zucchini"
        },
        {
          v: 1
        },
        
      ]
    },
    {
      c: [
        {
          v: "Pepperoni"
        },
        {
          v: 2
        },
        
      ]
    }
  ]
}

This above values I am getting from here:

public class Type
{
    public int Mushrooms { get; set; }
    public int Olives { get; set; }
    public int Zucchini { get; set; }
    public int Pepperoni { get; set; }
  public int Statistics { get; set; } //Ignore properties in final output
    public int Count { get; set; } //Ignore properties in final output
    public int Average { get; set; } //Ignore properties in final output
}

var types = MyRepository<Type>.FirstorDefault();

The above query outputs:

Mushrooms:3

Olives:31 etc..

This is how I have designed a class:

public class Cols
{
    public List<Names> Names { get; set; }

}

public class Names
{
    public string label { get; set; }
    public string type { get; set; }
}

But I am getting an output like this:

"data": [
    {
      "Names": [
        {
          "label": "Types",
          "type": "string"
        },
        {
          "label": "values",
          "type": "number"
        }
      ]
    }
  ]

Update: Class design

 public class Rootobject
    {
        public List<Names> cols { get; set; }
        public List<Row> rows { get; set; }
    }

    public class Names
    {
        public string label { get; set; }
        public string type { get; set; }
    }

    public class Row
    {
        public List<C> c { get; set; }
    }

    public class C
    {
        public object v { get; set; } 
    }

Still above class design gives me below json structure:

{
  "Data": {
    "cols": [
      {
        "label": "Types",
        "type": "string"
      },
      {
        "label": "values",
        "type": "number"
      }
    ],
    "rows": [
      {
        "c": [
          {
            "v": "Mushrooms"
          },
          {
            "v": "3"
          },
          {
            "v": "Olives"
          },
          {
            "v": "31"
          },
          {
            "v": "Zucchini"
          },
          {
            "v": "1"
          },
          {
            "v": "Pepperoni"
          },
          {
            "v": "2"
          }
        ]
      }
    ]
  }
}

For Returning json data I am using this json class of asp.net mvc.

return Json(new {Data = rootObject }, JsonRequestBehavior.AllowGet);

teo van kot · Answer 1 · 2016-08-19T13:57:44.630

1

According to your first json, you classes structure should be like this if you want to serialize it that way:

public class Rootobject
{
    public List<Names> cols { get; set; }
    public List<Row> rows { get; set; }
}

public class Names
{
    public string label { get; set; }
    public string type { get; set; }
}

public class Row
{
    public List<C> c { get; set; }
}

public class C
{
    public string v { get; set; }
}

edited Aug 19 '16 at 13:57

answered Aug 19 '16 at 13:35

teo van kot

12,350
10
38
70

But here when generating rows array how i will add mushroom,Olives etc as because you have declared v as int – I Love Stackoverflow Aug 19 '16 at 13:56
@Learning sorry fixed – teo van kot Aug 19 '16 at 13:57
Sorry but with your class design i am not getting expected json architecture – I Love Stackoverflow Aug 19 '16 at 14:16

dbc · Accepted Answer · 2016-08-25T06:31:04.763

Your updated class design can represent the JSON you need. The question is, how to allocate and initialize the contents of your Rootobject? You can do so using reflection, specifically by looping through all the public properties of your Rootobject with Type.GetProperties() then querying the value with PropertyInfo.GetValue(), like so:

var query = types.GetType()
    .GetProperties(BindingFlags.Instance | BindingFlags.Public)
    .Where(p => p.GetIndexParameters().Length == 0 && p.GetGetMethod() != null && p.CanRead)
    .Select(p => new Row { c = new List<C> { new C { v = p.Name }, new C { v = p.GetValue(types, new object[0]) } } });

var root = new Rootobject
{
    cols = new List<Names> { new Names { label = "Types", type = "string" }, new Names { label = "values", type = "number" } },
    rows = query.ToList(),
};

return Json(root, JsonRequestBehavior.AllowGet);

Sample fiddle.

If you have a specific list of property names to ignore, you can add those names to a hash set and filter them out:

var ignoredPropertyNames = new HashSet<string> { "Statistics", "Average", "Count" };

var query = types.GetType()
    .GetProperties(BindingFlags.Instance | BindingFlags.Public)
    .Where(p => p.GetIndexParameters().Length == 0 && p.GetGetMethod() != null && p.CanRead)
    .Where(p => !ignoredPropertyNames.Contains(p.Name))
    .Select(p => new Row { c = new List<C> { new C { v = p.Name }, new C { v = p.GetValue(types, new object[0]) } } });

Alternatively, if you have a specific list of property names to include, you can use Type.GetProperty(string) to get the corresponding property and proceed as before:

var propertyNames = new[] { "Mushrooms", "Olives", "Zucchini", "Pepperoni" };

var query = propertyNames
    .Select(n => types.GetType().GetProperty(n))
    .Where(p => p != null)
    .Where(p => p.GetIndexParameters().Length == 0 && p.GetGetMethod() != null && p.CanRead)
    .Select(p => new Row { c = new List<C> { new C { v = p.Name }, new C { v = p.GetValue(types, new object[0]) } } });

In this type class i have 3 extra properties coming in output.Is it possible to avoid those properties.Like for eg:Those properties are Statistics,Count,Average.I want to avoid this 3 properties in my output.Is this possible?? — I Love Stackoverflow, Aug 25 '16 at 05:47
@Learning - yes, that could be done. But first please try to provide a [mcve] that reproduces your problem. Do you know in advance a hardcoded set of properties to serialize, or do you want to mark your class with [attributes](https://msdn.microsoft.com/en-us/library/mt653979.aspx) indicating which property to serialize or ignore? — dbc, Aug 25 '16 at 05:54
Yes i know in advance that for creating this json structure i want to ignore Statistics,Count and average but i am using this 3 properties elsewhere.So i just want to ignore this 3 properties for this json structure only.See my updated question which now include complete Type class — I Love Stackoverflow, Aug 25 '16 at 06:07

score 0 · Answer 3 · edited May 23 '17 at 12:08

0

As stated here you can deserialize against a dynamic object and use that object, this will save you of writing suplimentary code where it is not needed:

dynamic obj = JObject.Parse(yourComplexJson);

edited May 23 '17 at 12:08

Community

1
1

answered Aug 19 '16 at 13:56

meJustAndrew

6,011
8
50
76

How to create column and row wise json structure?

3 Answers3