JSON files

JSON is an open-standard format that uses human-readable text to transmit data objects consisting of key-value pairs. It is the most common data format to build web APIs.

As JSON documents can have a lot of different forms, the platform can extract data from JSON files, JSON Lines files, and JSON dictionaries.

If the platform does not fully extract a document with a complex structure, use one of the JSON processors to complete the extraction.

When you provide it with a JSON file, the platform extracts a valid JSON document (array or object) into one dataset divided up into different records:

• If the document is a JSON array, a record will be created for each object inside the array (the keys of the first object will be used as column names).

• If the document is a JSON object, the "JSON root" parameter should contain a dot-separated path to the array inside your object. If not provided, the platform tries items.

For each item inside the array, the platform can follow another path before extracting the records with the parameter "JSON object".

JSON files have no header row that lists their columns. The platform creates your dataset's fields from the keys of the first object in the array.

Note that a key that is missing from the first object isn't created as a field, even if most other objects contain it. Its values aren't extracted for any record, and you can't add the field from the Schema tab.

If a key from your file is missing from your dataset's schema, make sure the first object contains every key you need:

• Reorder the file so that a complete object comes first, or ask the data producer to do so.

• If the producer also publishes the data as a CSV file, use that file instead. The header row of a CSV file lists all its columns.

• Regular fields (decimal, boolean, string)

• JSON object: used as-is

• Array:

  • If the array contains JSON objects, it is used as-is.

  • If the array contains strings, a multivalued field is created with all the strings separated by a semicolon (;).

For more information about adding a file source, see Retrieving a file.

[
 {
     "name": "Agra Express",
     "origin": "Agra Cantt",
     "destination": "New Delhi"
 },
 {
     "name": "Gour Express",
     "origin": "Balurghat",
     "destination": "Sealdah"
 }
]

In this JSON file, the JSON array is directly at the root. You can leave the JSON root box empty.

The resulting dataset will be:

The column order may vary.

{
 "filename": "trains.json",
 "content": {
     "trains": [
         {
             "id": 123,
             "info": {
                 "name": "Agra Express",
                 "origin": "Agra Cantt",
                 "destination": "New Delhi"
             }
         },
         {
             "id": 555,
             "info": {
                 "name": "Gour Express",
                 "origin": "Balurghat",
                 "destination": "Sealdah"
             }
         }
     ]
 }
}

For this complex JSON file, the correct JSON root is content.trains. If content.trains is set as the JSON root, the resulting dataset will be:

To only extract the info JSON objects, and skip the id number, the correct JSON object is info. If info is set as a JSON object, the resulting dataset will be:

You can use a JSON Lines file as a source. In JSON Lines files, each line is a one-line JSON document. The whole file is not a valid JSON document but each line is.

Two modes are supported:

• One JSON array on each line (or one array altogether)

• One JSON object on each line

• Regular fields (decimal, string)

• Array: used as-is

• JSON object: used as-is

For more information about adding a file source, see Retrieving a file.

You can use a JSON dictionary as a source. The platform extracts a valid JSON document (array or object) into one dataset of several records.

The platform expects a JSON object where each key contains a record. The platform then creates records with:

• one column to hold the key

• one column for each attribute of the corresponding object (or a single column named "value" if the value is a string instead of an object).

• Regular fields (decimal, bool, string)

• JSON object: will be used as-is

• Array:

  • If the array contains JSON objects, it is used as-is.

  • If the array contains strings, a multivalued field is created with all the strings separated by a semicolon (;).

For more information about adding a file source, see Retrieving a file.

{
  "2016": {
    "type1": {
      "price": 10,
      "color": "blue",
      "available": true
    },
    "type2": {
      "price": 9,
      "color": "red",
      "available": true
    }
  },
  "2015": {
    "type1": {
      "price": 10.5,
      "color": "teal",
      "available": true
    },
    "type2": {
      "price": 9.1,
      "color": "crimson",
      "available": true
    }
  }
}

If you leave the JSON root box empty, the resulting dataset will be:

{
  "results": {
    "datasets": [{
        "abc": {
          "title": "A B C",
          "description": "A description about ABC"
        }
      },
      {
        "xyz": {
          "title": "D E F",
          "description": "Another description"
        }
      }
    ]
  }
}

For this complex JSON file, the correct JSON root is results.datasets.item.

• results.datasets leads to the array

• item iterates inside the array and gets each object