Inbound REST web service and JSON

[Henri Muldre]

Hi,

I have a 3rd party system that sends us data in JSON format using Import Set API -> POST.

For that, I have created an Inbound web service with certain web service fields and a Transform Map etc.

Example endpoint (POST): https://{instance}.service-now.com/api/now/import/u_example_integration_table

Body JSON:

{
    "u_sys_id": "5f82c76f1b50e1106c1ab9118b4bcb55",
    "u_state": "Ready",
    "u_comments": "Sample comments",
    "u_priority": "High",
    "u_correlation_id": "TICKET123456789",
    "u_field9": {
        "name 1": "value 1",
        "name 2": "value 2",
        "name 3": "value 3",
        "name 4": "value 4",
        "name 5": "value 5"
    }
}

In the web service fields, we have a field (let's call it Field 9 with value u_field9) that should accept JSON or name and values into 1 field.

Since from each of the request the 3rd party sends us, a new record is created into the Import staging table (in this example, it's "u_example_integration_table"). We have had a bit of a challenge in getting the data from Field 9 (u_field9) to be added into the import table record.

First I created a Name-Value Pair type of field, which looks really nice in the form view of the import table.

However, when I send JSON data to that field via REST API, it will not work and will stay empty, as it appears it's expecting a string instead.

If I open Postman and wrap the whole key and value between single quotes, it works well, however this is not a solution as the 3rd party system is sending the data most definitely without those single quotes:

{
    "u_field9": '{"name 1": "value 1","name 2": "value 2","name 3": "value 3","name 4": "value 4","name 5": "value 5"}'
}

*Notice the ' at the beginning and end of the key and value array.

The output was that the Name-Value Pair field got filled without issues, as I assume ServiceNow was expecting a string and not a JSON object. Otherwise, it would be easy to stringify() the JSON, but in this situation, I'm receiving an inbound Rest message that is inserted into Import set table.

Another idea was to just have a normal string field with max length as 4000.

That did also not work, because as soon as JSON is added to the string field, it removes all colons and quotes from the JSON and adds it to the text field as this (removes double quotes and replaces colon with equals:

Does anyone have any good ideas how to get this solved?

The ultimate goal is to get the data from JSON under name u_field9, add it to the import set table field u_field9.

What are my options and what kind of field type should I use?

[Henri Muldre · Accepted solution]

UPDATE:

As of now, I ended up creating a Scripted REST API in between and point the 3rd party to send all requests instead not though the Web Service I created (/api/now/import/u_example_integration_table), but the Scripted one (/api/123456/example_scripted_api/create), so that I can manipulate the JSON array before it is inserted into the Import Set staging table:

• Method: POST
• Full path: /api/123456/example_scripted_api/create
• Script:

(function process( /*RESTAPIRequest*/ request, /*RESTAPIResponse*/ response) {

    var body = request.body.dataString;
    var payload = JSON.parse(body);

    //Get data from payload
    var sysID = payload.u_sys_id;
    var state = payload.u_state;
    var comments = payload.u_comments;
    var priority = payload.u_priority;
    var correlationID = payload.u_correlation_id;

    //handle the JSON array into string
    var field9Raw = payload.u_field_9;
    var field9 = JSON.stringify(field9Raw);

    var importTable = new GlideRecord('u_example_integration_table');
    importTable.initialize();

    //Set data to staging table record
    importTable.u_sys_id = sysID;
    importTable.u_state = state;
    importTable.u_comments = comments;
    importTable.u_priority = priority;
    importTable.u_correlation_id = correlationID;
    importTable.u_field_9 = field9;

    //Insert record to staging table
    importTable.insert();

    //Set response
    //..some other stuff omitted for demo purposes

})(request, response);

After this, a new record is inserted into the stating table u_example_integration_table, which triggers the Tranform Map and other scripts, and then turns into a ticket.

Feel free to suggest any other solutions which are better.

[Jacob23]

@Henri Muldre Not related to your question, but I am working on a scripted REST API and I believe I am being sent a JSON. Where do you go to see what has been sent in the JSON body? 

[Henri Muldre]

Hi Jacob23,

You can easily log it, if you believe you are being sent JSON payload using the Scripted REST API (and correct resource) you created and provided to the sender.

In your scripted API resource(s):

(function process( /*RESTAPIRequest*/ request, /*RESTAPIResponse*/ response) {

    var body = request.body.dataString;
    var data = JSON.parse(body);

    gs.info('Scripted REST API JSON payload:\n' + JSON.stringify(data));

})(request, response);

After this you would go to syslog table and search for your log.

If you are not getting anything by that method, you can also enable system property glide.rest.debug to true. If it does not exist, create one and set is as true. It is recommended to keep it as true only while debugging to avoid performance issues, otherwise at false.

More info from Product documentation: Debug REST queries (servicenow.com) 

[Jacob23]

Thank you, this helps a lot!