Importing JSON files

These examples demonstrate how to import various types of JSON data and the necessary path for each row. JSON files that you import should follow these guidelines.

  • For JSON arrays, the path for each row must specify the array root element twice, such as /incidents/incidents.
  • JSON files should follow RFC-4627. For example, a single comma should separate a value from the following name. Names within an object should be unique.
  • Predicates such as @element, [index], or text(), as well as Axis such as children, siblings, or ancestors are not supported.

Simple array

  • Path for each row: /incidents/incidents
  • Result: 2 records
In this example, the path for each row includes the array root node /incidents twice, which is necessary when importing an array.
{  
   "source":"HI",
   "incidents":[  
      {  
         "number":"INC0000001",
         "short_description":"Can't read email"
      },
      {  
         "number":"INC0000002",
         "short_description":"Error loading XML file"
      }
   ]
}

Array in 2nd level

  • Path for each row: /problems/data/data
  • Result: 4 records
In this example, the path for each row includes the array root node /data twice.
{  
   "problems":{  
      "id":"0",
      "data":[  
         {  
            "number":"PRBTEST001",
            "short_description":"testsd1"
         },
         {  
            "number":"PRBTEST002",
            "short_description":"testsd2"
         },
         {  
            "number":"PRBTEST003",
            "short_description":"testsd3"
         }
      ]
   }
}

Nested array

  • Path for each row: /problems/problems/data/data
  • Result: 3 records
In this example, the path for each row includes the root nodes for both arrays twice, /problems and /data.
{  
   "problems": [  
         {
      "id":0,
      "data":[  
            {  
               "number":"PRBTEST001",
               "short_description":"testsd1"
            },
            {  
               "number":"PRBTEST002",
               "short_description":"testsd2"
            },
            {  
               "number":"PRBTEST003",
               "short_description":"testsd3"
            }
          ]
       }
    ]	
}

Limited support for child (nested) arrays

There are some limitations related to importing child arrays within an array.

When an array has a simple collection of values like [1,2,3], there is no way to import that data in that format, regardless of where it lies in the JSON hierarchy. Here are some examples of what is and is not supported.

{ 
  "response":{
    "docs":[ 
      { 
        "id":"id_val",
        "childrenArray":[1,2,3],
        "anotherArray":[{"key1":"value1"}, {"key1": "value2"}]
      }
    ]
  }
}
  • The /response/docs/docs path creates one record with only one column: id. Any arrays found in a record (like childrenArray or anotherArray) are not created as columns in the import table.
  • The /response/docs/docs/anotherArray/anotherArray path creates two records, each with one column: key1.
  • The /response/docs/docs/childrenArray/childrenArray path does not work and returns a Path should always refer JSON Objects error because the values in the array are not in a key-value structure.
There are two possible ways to adjust the format.
  1. Change the array of "childrenArray":[1,2,3] to be "childrenArray ":[{"val":1}, {"val":2},{"val":3}], which creates three records.

    The downside is that you need two separate data sources, one for /response/docs/docs and another for /response/docs/docs/childrenArray/childrenArray.

  2. Another option is to convert the array to a simple string: "childrenArray ":"1, 2, 3".

    This option drops the use of the array and works with the single data source. The downside is that any consumer of this data must split the string on commas.

Orphan array

Important: This format is not supported. A JSON array must always be inside an element. The JSON file must always start with { and end with }.
[  
   {  
      "number":"PRBTEST001",
      "short_description":"testsd1"
   },
   {  
      "number":"PRBTEST002",
      "short_description":"testsd2"
   }
]

Multiple elements instead of an array

  • Path for each row: /problems/problem
  • Result: 3 records
Important: This format is not recommended. JSON files should follow RFC-4627, which states that names within an object should be unique. Use JSON arrays instead.
{  
   "problems":{  
      "title":"2 problems",
      "problem":{  
         "number":"PRBTEST001",
         "short_description":"testsd1"
      },
      "problem":{  
         "number":"PRBTEST002",
         "short_description":"testsd2"
      }
   },
   "problems":{  
      "title":"1 problem",
      "problem":{  
         "number":"PRBTEST005",
         "short_description":"testsd5"
      }
   }
}