JSON format

<< Click to Display Table of Contents >>

Navigation:  Reference > File formats >

JSON format

Easy Data Transform can input from and output to JSON format files. Default file extension ".json".

 

JSON (JavaScript Object Notation) format is commonly used for exchanging data between programs.  JSON data is generally expected to be in UTF8 encoding without a Byte Order Mark (BOM).

 

For example:

 

convert to json

 

Is equivalent to:

 

[

  {

    "CategoryID": 1,

    "CategoryName": "Beverages",

    "Description": "Soft drinks, coffees & teas",

    "In stock": true

  },

  {

    "CategoryID": 2,

    "CategoryName": "Condiments",

    "Description": "Sweet and savory sauces",

    "In stock": false

  },

  {

    "CategoryID": 3,

    "CategoryName": "Confections",

    "Description": "Candies and sweet breads",

    "In stock": true

  }

]

 

The dot ('.') character is used in the column header to show nesting. For example:

unflatten-table

Is equivalent to:

 

[

  {

    "name": "Avocado Dip",

    "carb": 2,

    "cholesterol": 5,

    "fiber": 0,

    "minerals": {

      "ca": 0,

      "fe": 0

    },

    "protein": 1,

    "sodium": 210,

    "vitamins": {

      "a": 0,

      "c": 0

    }

  }

]

 

Any dots in JSON names are converted to hyphens ('-') on input.

 

JSON arrays can be input in either long or wide Format. For example:

 

[

   {

      "name": "1",

      "values": [ "a", "b" ]

   },

   {

      "name": "2",

      "values": [ "c", "d" ]

   }

]

 

Input as Long (more rows):

JSON input in long format

Input as Wide (more columns):

 

wide-format-json-example

 

If children of the top level object are named, then they are flattened slightly different to avoid creating thousands or millions of columns. For example:

 

{

 "record 1":{

         "value": 1

 },

 "record 2":{

         "value": 2

 },

 "record 3":{

         "value": 3

 }

}

 

Is flattened into:

 

flatten json into table example

 

An input JSON format file is expected to have either:

a single top-level array or object; or

a single JSON object on each line (see jsonlines.org).

a record separator character followed by a single JSON object on each line (see RFC7464).

 

You can specify the JSON type for each columns when outputting to JSON using the Value types option. Any values that do not match the type selected are output as a JSON null value.