We love working with Jason, (ahem), I mean JSON. If you have been using data for last decade or so, you have probably come to love JSON for many things and decreased your dependence on XML. XML still has it merits and there many reasons to use XML and learn about XPath queries. If you have not yet worked with JSON or you are curious about the standard for JSON, visit JSON dot org.
I was playing in my PDI a few months ago and I realized that JSON became an available option for a File Data Source. My job as a Software Development Manager sees me relying on JSON for all kinds of processes. For those of us that consume JSON regularly, ServiceNow having this option is a game-changer because not only are we able to attach a physical JSON file to our data source for those one-time imports but we can also import JSON from a web service that exposes a JSON endpoint.
In this post, I want to show you how to import a JSON file for a one-time import, how to import a JSON file from a web service and, finally, how to set up a Scheduled Data Import to import the the JSON file from a web service at a regular frequency.
Table of Contents
- JSON File Source (as an attachment)
- JSON File Source (from a web service)
- Scheduled Data Import for a JSON File (from a web service)
- Summary
- Sources cited
JSON File Source (as an attachment)
To follow along in a PDI, download the attachment in this post called "public_domain_books.json.zip" and extract the JSON file "public_domain_books.json."
Create the data source
1. From the All menu, type System Import Sets into Filter Navigator.
2. Click the Module Link entitled Data Sources (found under System Import Sets > Administration).
3. Click the New UI Action.
4. Configure your data source as follows:
a. Name should be set as Public Domain Books.
b. Import set table label we will set as Public Domain Book Import.
c. Type should be set as File.
d. Format should be set as JSON.
e. Path for each row should be set as /data/data.
This tells ServiceNow that we want to import all of the elements in the array field called "data." The duplication of "/data" indicates that the "data" field is a JSON array. Each field of the elements in the array will be mapped to a field in the "Public Domain Book Import" table.
6. Discard Arrays should be unchecked.
7. File retrieval method should be set to Attachment.
5. From the Form Header, click the Manage Attachments UI Action.
6. Navigate to where you have stored the public_domain_books.json file, click on this file and then click Open.
7. Once the file has been attached, close the modal dialog window.
8. Your record should look like this:
Record completed following the previous steps.
9. Click the Submit UI Action.
Test the data source
1. Open the record for Public Domain Books.
2. Click theTest Load 20 Records UI Action.
3. You should see that 10 rows were imported.
Test Load 20 Records successfully completed with 10 records imported.JSON File Source (from a web service)
We are going to have some fun and see what the buzz about ServiceNow is in ServiceNow's Subreddit.
Create the data source
1. From the All menu, type System Import Sets into Filter Navigator.
2. Click the Module Link entitled Data Sources (found under System Import Sets > Administration).
3. Click the New UI Action.
4. Configure your data source as follows:
a. Name should be set as ServiceNow Subreddit Posts.
b. Import set table label we will set as ServiceNow Subreddit Post Import.
c. Type should be set as File.
d. Format should be set as JSON.
e. Path for each row should be set as /data/children/children/data.
This tells ServiceNow that we want to import all of the elements from the "data" field within the elements in the array field called "data.children." The duplication of "/children/children" indicates that the "children" field is a JSON array. Each field of the nested "data" field on the elements in the array will be mapped to a field in the "ServiceNow Subreddit Post Import" table.
f. Discard Arrays should be unchecked.
g. File retrieval method should be set to HTTPS.
h. Server should be set to reddit.com.
i. File path should be set to /r/servicenow/new.json?limit=10.
We are using the "new" endpoint that Reddit offers which allows us to grab the newest posts. Then we append "json" to that, to ask Reddit to display the posts in JSON format. Last, the use of the query parameter (the question mark) of "limit=10" is a parameter that Reddit accepts to return only 10 posts in the JSON object. See the footnotes of this post for a link to the Reddit API documentation.
5. Your record should look like this:
Record completed by following the steps above.6. Click the Submit UI Action.
Test the data source
1. Open the record for ServiceNow Subreddit Posts.
2. Click the Test Load 20 Records UI Action.
3. You should see that 10 rows were imported.
Test Load 20 Records successfully completed with 10 records imported.Scheduled Data Import for a JSON File (from a web service)
Let's treat this like an RSS feed of sorts and request to get the ServiceNow Subreddit posts once a day at midnight.
Create the Scheduled Import
1. From the All menu, type System Import Sets into Filter Navigator.
2. Click the Module Link entitled Scheduled Imports (found under System Import Sets > Administration).
3. Click the New UI Action.
4. Configure your data source as follows:
a. Name should be set as ServiceNow Subreddit Posts Import.
b. Data source should be set to ServiceNow Subreddit Post Import.
The page will refresh once you select the "Data source" from the reference list.
c. Leave Active set be checked.
d. Leave Run set as Daily.
e. Leave Time set as 00:00:00.
5. Your record should look like this:
Test the data source
1. Click the Execute Now UI Action.
2. Open the Scheduled Data Import from the list view called ServiceNow Subreddit Posts Import.
3. In the Related List, click the Import Set field value of the latest Execution record.
4. Observe that there are 10 records in the Import Set Rows Related List.
Summary
Getting started with JSON as a File Data Source is really this simple! How are you using JSON Data Sources? What interesting use case do you have for importing JSON data into your instance? Tell us, the Community, what cool app you built and incorporated JSON into.
Sources cited
- ServiceNow Documentation for creating a File source: https://www.servicenow.com/docs/bundle/zurich-integrate-applications/page/administer/import-sets/tas...
- JSON.org for JSON standard: https://www.json.org/
- Reddit.com's Listing API for the "new" endpoint: https://www.reddit.com/dev/api/#GET_new
1 Comment
