Introduction

For a project I’m currently working on which should launch shortly, I needed to generate a JSON schema from a PostgreSQL recordset. There are a couple of projects out there which will automatically take a .NET type and generate a schema, however with just a recordset, it’s a bit more complicated.

I ended up choosing NJsonSchema, it seemed to be pretty active and fully featured. Also, having a permissive license makes it easy to bootstrap a project without worrying too much about upfront investment in licenses.

Schema Generation

To actually generate a schema manually however is not as straightforward as generating it from a type. Hence this post.
To accomplish our schema, we’ll retrieve the dataset, read the schema and then create a JSON schema and add our relevant elements to it, mapping a column per element with the closest type mapping we can accomplish given the limited types available in JSON.

Dataset

This is our dataset which we’ll be generating a schema (and JSON) for.

Output JSON

To output our dataset to a JSON object, we produce an array of objects, each object will be uniform in that they will all have the same number of elements.

[
  {
    "id": 1,
    "name": "Jane Doe",
    "address": "21 Jump St"
  },
  {
    "id": 2,
    "name": "John Doe",
    "address": "19 Mercury Dv"
  }
]

Desired Schema

If we take our JSON, we can plug it into the quicktype app which will then analyse our JSON and generate a schema for it.
This gives us the below schema, which is an array referencing an object with each object having 3 properties, all of which are required.

{
  "$schema": "http://json-schema.org/draft-06/schema#",
  "type": "array",
  "items": {
    "$ref": "#/definitions/DataRowElement"
  },
  "definitions": {
    "DataRowElement": {
      "type": "object",
      "additionalProperties": false,
      "properties": {
        "id": {
          "type": "integer"
        },
        "name": {
          "type": "string"
        },
        "address": {
          "type": "string"
        }
      },
      "required": ["address", "id", "name"],
      "title": "DataRowElement"
    }
  }
}

C# Schema Generation

To generate our schema, we read the schema DataTable, create a new JsonSchema, create a JsonSchemaGenerator with the default JsonSchemaGeneratorSettings and a JsonSchemaResolver.
We then iterate through our schema table, generate a new JsonSchemaProperty and then add it to our JSON schema object.
Once we’ve completed our items schema, we then create an outer array schema and set its type to JsonObjectType.Array, add our object schema to its Definitions and set the Item property to be a new JsonSchemaProperty with the reference set to our object schema.

We’ve now completed our schema generation and if we call aSchema.ToJson();, we’ll be able to see that we’ve generated the desired schema referenced earlier.

var schema = reader.GetSchemaTable();
var jSchema = new JsonSchema();
jSchema.Type = JsonObjectType.Object;
jSchema.Title = "DataRowElement";
jSchema.AllowAdditionalProperties = false;

var generator = new JsonSchemaGenerator(new JsonSchemaGeneratorSettings());
var resolver = new JsonSchemaResolver(jSchema, generator.Settings);

foreach (DataRow row in schema.Rows)
{
    var name = (string)row["ColumnName"];
    var type = (Type)row["DataType"];

    var prop = generator.Generate<JsonSchemaProperty>(type.ToContextualType(), resolver);
    prop.IsRequired = true;
    jSchema.Properties.Add(name, prop);
}

var aSchema = new JsonSchema();
aSchema.Type = JsonObjectType.Array;

aSchema.Definitions.Add("DataRowElement", jSchema);
aSchema.Item = new JsonSchemaProperty { Reference = jSchema };

Happy generating