How to modify the JSON obtained from serializing a DataSet using Json.Net for purposes of ESRI geocoding

Question

How to introduce the "attributes" level into JSON text below? I'm using a C# dataset populated from SQL server with SerializeObject from Newtonsoft.json.

This is for submitting data to ESRI batch geocoder, as described here.

The format their REST service expects looks like this

{
  "records": [
    {
      "attributes": {
        "OBJECTID": 1,
        "Address": "4550 Cobb Parkway North NW",
        "City": "Acworth",
        "Region": "GA"
      }
    },
    {
      "attributes": {
        "OBJECTID": 2,
        "Address": "2450 Old Milton Parkway",
        "City": "Alpharetta",
        "Region": "GA"
      }
    }
  ]
}

The format my C# script creates looks like this (missing the "attributes" level.)

{
  "records": [
    {
      "OBJECTID": 1,
      "address": "4550 Cobb Parkway North NW",
      "city": "Acworth",
      "state": "GA",
      "zip": 30101.0
    },
    {
      "OBJECTID": 2,
      "address": "2450 Old Milton Parkway",
      "city": "Alpharetta",
      "state": "GA",
      "zip": 30009.0
    }
  ]
}

I've read thru json.net documentation and wonder if the JsonConverter class could be helpful. Candidly, I'm at loss for how to resolve this. First time user of Json.net, relative newbie with C#

Here is the C# code used to this point:

SQLStatement = "select OBJECTID, Address, City, Region, Postal from MyAddresses";
SqlDataAdapter geoA = new SqlDataAdapter(SQLStatement, GEOconn);
DataSet GeoDS = new DataSet();
geoA.Fill(GeoDS, "records");
string geoAJSON = JsonConvert.SerializeObject(GeoDS);
Console.WriteLine("{0}", geoAJSON);

Answer 1

You can wrap your rows in another object with an "attributes" property using Json.Net's LINQ-to-JSON API.

In your code, replace this line:

string geoAJSON = JsonConvert.SerializeObject(GeoDS);

with this:

var obj = JObject.FromObject(GeoDS);
obj["records"] = new JArray(
    obj["records"].Select(jo => new JObject(new JProperty("attributes", jo)))
);
string geoAJSON = obj.ToString();

Working demo here: https://dotnetfiddle.net/nryw27

Aside: based on your JSON it looks like you are storing postal codes in your database as decimals. Don't do that. They may look like numbers, but you should store them as strings. Postal codes in the US can have leading zeros, which will get dropped when you treat them as numbers. Some international postal codes can contain letters, so a numeric type won't even work in that case.