JSON is a a great format for data exchange. It is simple, human readable and can be precise. On the down side it is untyped and it can be verbose. With formal data exchange this can be a problem.

I have been using a simple JSON data structure for a few years and so thought I would share it here. It is a robust way to exchange data between systems which don’t necessarily have a contract for the data format. It also scales better than the usual key value methos favoured by many systems.

The data exchange method improves on usual JSON data exchange approaches in two ways.

  • It supports data typing and extensible column meta data
  • The “key” in the key value pair is sent once rather than once for every value

The data follows this format:

{
  "columns": [
    {"name": "col_name", "data_type": "type"},
    ...
  ],
  "rows": [
    ["value1", "value2", ...],
    ...
  ]
}

There are two keys in the columns block which are mandatory, name and data_type. Any additional keys added are optional but just like regular JSON this format is extensible so max_length or display_colour are both valid keys which could be used. This is all meta data. The body of the data is stored in rows and each value appears exactly once. Each row is an array of values corresponding to each column. Each row’s array is the same length.

{
  "columns": [
    {"name": "col1", "data_type": "TEXT"},
    {"name": "col2", "data_type": "INT"}
  ],
  "rows": [
    ["val1", 42],
    ["val2", 43]
  ]
}

Because it is an extensible format with additional keys will not breaking existing integrations, at worst they are ignored, we can add integration specific attributes. Here is an example of an approach I have used for web presentation of data.

{
  "columns": [
    {"name": "col1", "data_type": "TEXT", "colour": "red", "width": "100px"},
    {"name": "col2", "data_type": "INT", "colour": "blue", "width": "20px"}
  ],
  "rows": [
    ["val1", 42],
    ["val2", 43]
  ]
}