Importing Nested JSONs

JSON (JavaScript Object Notation) is a lightweight data interchange format that’s easy for humans to read and write, and easy for machines to parse and generate. It’s commonly used for transmitting data in web applications between servers and clients.

• General syntax –

{
  "key1": "value1",
  "key2": "value2",
  "key3": "value3",
  "key4": value4,
  "key5": value5
}

• Keys: Strings enclosed in double quotes.
• Values: Strings, numbers, booleans, or null, depending on the data type.

JSONs are extensively used in ServiceNow for bidirectional data exchange, both within the platform and with external systems. In this article, we will explore how to convert an array of nested and complex JSONs into an Import Set using recursion.

Let’s assume that the below structure is being received into ServiceNow via a REST call.

{
  "employees": [
    {
      "name": "Jane Smith",
      "age": 28,
      "address": {
        "street": "123 Elm Street",
        "city": "New York",
        "state": "NY",
        "zipCode": "10001"
      },
      "jobDetails": {
        "title": "Software Engineer",
        "department": "IT",
        "salary": 75000
      },
      "skills": "JavaScript"
    },
    {
      "name": "John Doe",
      "age": 35,
      "address": {
        "street": "456 Oak Avenue",
        "city": "San Francisco",
        "state": "CA",
        "zipCode": "94107"
      },
      "jobDetails": {
        "title": "Project Manager",
        "department": "Operations",
        "salary": 90000
      },
      "skills": "Project Management"
    }
  ]
}

Certainly, one could loop through the ‘employees’ array and manually map each value to an Import Set column. However, if a new key-value pair is added to the structure, the code would need to be updated accordingly.

Our script will aim to re-structure each JSON from the ’employees’ array into a simple format like below.

{
  "name": "Jane Smith",
  "age": 28,
  "addressstreet": "123 Elm Street",
  "addresscity": "New York",
  "addressstate": "NY",
  "addresszipCode": "10001",
  "jobDetailstitle": "Software Engineer",
  "jobDetailsdepartment": "IT",
  "jobDetailssalary": 75000,
  "skills": "JavaScript"
}

The JSON can be inserted as an import set row like below.

Recursive script

Below is a versatile function that accepts a complex JSON as input and returns a simplified JSON, which can then be used to create an Import Set row.

• Copyable version can be found here.

The function can be used in anywhere, but primarily in data source scripts, example –

import_set_table.insert(convertJSONtoImportSet(employees[emp], '', {}));

Upon loading all records, the individual rows get inserted which can further be processed using transform maps.

This recursive JavaScript function was originally developed by Alan Eicker, which I adapted and enhanced to make it suitable for ServiceNow importing.

Next steps

Handling complex JSON imports has likely become much easier now. However, we still need to watch out for edge cases that may vary depending on the specific payload.