Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

LINQ and JSON.NET when the property names vary

I am attempting to parse some JSON content in to C#. For the simpler cases I am having great success with JSON.NET and really appreciate the clean approach offered by the LINQ provider. Here's an example where I'm downloading information about a layer in a map and filling in some properties on a class called (suprisingly!) Layer:

        using (var client = new WebClient())
        {
            _content = client.DownloadString(_url.AbsoluteUri + OutputFormats.Json);
        }

        JObject json = JObject.Parse(_content);
        IEnumerable<Field> fields = from f in json["fields"].Children()
                                    select new Field(
                                        (string)f["name"],
                                        (string)f["alias"],
                                        (EsriFieldType)Enum.Parse(typeof(EsriFieldType), (string)f["type"])
                                        );
        _fields = fields.ToList();
        _displayFieldName = (string)json["displayField"];

You can look at this url for details of the JSON for that method: http://sampleserver1.arcgisonline.com/ArcGIS/rest/services/WaterTemplate/WaterDistributionNetwork/MapServer/1?f=json&pretty=true. But the issue comes when I need to turn the individual data fields associate with map layers in to a DataTable or even just a dictionary structure. The problem is that, unlike RSS feeds or other consistent formats, the field names and number of fields changes from map layer to map layer. Here's an example of me running a query:

    [Test]
    [Category(Online)]
    public void Can_query_a_single_feature_by_id()
    {
        var layer = _map.LayersWithName(ObjectMother.LayerWithoutOID)[0];
        layer.FindFeatureById("13141");
        Assert.IsNotNull(layer.QueryResults);
    }

The code that's run in layer.FindFeatureById is this and includes the part where I get stuck:

        public void FindFeatureById(string id)
    {
        var queryThis = ObjectIdField() ?? DisplayField();
        var queryUrl = string.Format("/query{0}&outFields=*&where=", OutputFormats.Json);
        var whereClause = queryThis.DataType == typeof(string)
                                 ? string.Format("{0}='{1}'", queryThis.Name, id)
                                 : string.Format("{0}={1}", queryThis.Name, id);
        var where = queryUrl + HttpUtility.UrlEncode(whereClause);
        var url = new Uri(_url.AbsoluteUri + where);
        Debug.WriteLine(url.AbsoluteUri);
        string result;

        using (var client = new WebClient())
        {
            result = client.DownloadString(url);
        }

        JObject json = JObject.Parse(result);
        IEnumerable<string> fields = from r in json["fieldAliases"].Children()
                                     select ((JProperty)r).Name;
        // Erm...not sure how to get this done.
        // Basically need to populate class instances/rows with the 
        // values for each field where the list of fields is not
        // known beforehand.

    }

You can see the JSON spit out by visiting this URL (note the encoding when you cut'n'paste): href="http://sampleserver1.arcgisonline.com/ArcGIS/rest/services/WaterTemplate/WaterDistributionNetwork/MapServer/1/query?f=json&outFields=*&where=FACILITYID%3d'13141'

So my question (at last!) is this. How do I cycle through the collection of "attributes" in the "features" to get the actual field values. You can see that I've figured out how to get the field names from the fieldAliases, but after that I'm stumped. I've been tinkering with the JsonReader on a file that looks like this, but still no joy:

{
  "displayFieldName" : "FACILITYID", 
  "fieldAliases" : {
  "FACILITYID" : "Facility Identifier", 
  "ACCOUNTID" : "Account Identifier", 
  "LOCATIONID" : "Location Identifier", 
  "CRITICAL" : "Critical Customer", 
  "ENABLED" : "Enabled", 
  "ACTIVEFLAG" : "Active Flag", 
  "OWNEDBY" : "Owned By", 
  "MAINTBY" : "Managed By"
}, 
"features" : [
  {
    "attributes" : {
      "FACILITYID" : "3689", 
      "ACCOUNTID" : "12425", 
      "LOCATIONID" : "12425", 
      "CRITICAL" : 1, 
      "ENABLED" : 1, 
      "ACTIVEFLAG" : 1, 
      "OWNEDBY" : 1, 
      "MAINTBY" : 1
    }
  }, 
  {
    "attributes" : {
      "FACILITYID" : "4222", 
      "ACCOUNTID" : "12958", 
      "LOCATIONID" : "12958", 
      "CRITICAL" : 1, 
      "ENABLED" : 1, 
      "ACTIVEFLAG" : 1, 
      "OWNEDBY" : 1, 
      "MAINTBY" : 1
    }
  }
]
}
like image 898
Dylan Avatar asked Sep 10 '09 03:09

Dylan


2 Answers

For a quick and dirty (non-LINQ) way to get at the attributes and values, try the following:

JObject jo = JObject.Parse(json);

foreach (JObject j in jo["features"])
{
  foreach (JProperty k in j["attributes"])
  {
    Console.WriteLine(k.Name + " = " + k.Value);
  }
}

It's not ideal, but it works when you don't know the field names that will be coming back. If I find a better way to do this, I'll update it.

like image 98
Michael Todd Avatar answered Nov 07 '22 08:11

Michael Todd


Well it turned out the the best approach was to use a JsonTextReader and just rip through the data rather than trying to use LINQ. It's got lots on indentation which makes me unhappy but I suppose that's a direct effect of using a hierarchical data structure in the first place. Here's how to print the list of rows ("attributes") and their name/value collections:

        using (var file = File.OpenText(_fileWithGeom))
        {
            JsonReader reader = new JsonTextReader(file);

            while (reader.Read())
            {
                while (Convert.ToString(reader.Value) != "features")
                {
                    reader.Read();
                }

                Console.WriteLine("Found feature collections");

                // ignore stuff until we get to attribute array

                while (reader.Read())
                {
                    switch (Convert.ToString(reader.Value))
                    {
                        case "attributes":
                            Console.WriteLine("Found feature");
                            reader.Read(); // get pass attributes property

                            do
                            {
                                // As long as we're still in the attribute list...
                                if (reader.TokenType == JsonToken.PropertyName)
                                {
                                    var fieldName = Convert.ToString(reader.Value);
                                    reader.Read();
                                    Console.WriteLine("Name: {0}  Value: {1}", fieldName, reader.Value);
                                }

                                reader.Read();

                            } while (reader.TokenType != JsonToken.EndObject
                                     && Convert.ToString(reader.Value) != "attributes");
                            break;

                        case "geometry":
                            Console.WriteLine("Found geometry");
                            reader.Read();
                            break;
                    }
                }
            }
        }

And this time I'm also having to handle geometry, so check out this URL for the JSON that the above code is parsing:

http://sampleserver1.arcgisonline.com/ArcGIS/rest/services/WaterTemplate/WaterDistributionNetwork/MapServer/7/query?where=OBJECTID%3C10&returnGeometry=true&outSR=&outFields=*&f=pjson

like image 43
Dylan Avatar answered Nov 07 '22 07:11

Dylan