Importing JSON files

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

  • The XPath for JSON arrays must specify the array root element twice, such as /incidents/incidents.
    Important: When you create a new JSON data source, you cannot configure an XPath. Use the Form Designer and add Path for each row to the Data Source form. The Path for each row is mandatory for JSON file imports, not the XPath for each row.
  • 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

  • XPath: /incidents/incidents
  • Result: 2 records
In this example the XPath 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

  • XPath: /problems/data/data
  • Result: 4 records
In this example the XPath 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

  • XPath: /problems/problems/data/data
  • Result: 3 records
In this example the XPath 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":[259498,263022,489691],
        "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":[259498,263022,489691] to be "childrenArray ":[{"val":259498}, {"val":263022},{"val":489691}], 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. You cannot use and create a relation between the two data sources.

  2. Another option is to convert the array to a simple string: "childrenArray ":"259498, 263022, 489691".

    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. You must also run some code in the ServiceNow platform to handle the object/array string data, but it is possible.

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

  • XPath: /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"
      }
   }
}