ServiceNow – Scripted REST APIs – How to GET data from two tables and JOIN them

I’m trying to produce a list of Projects (from a custom table called "pm_project") and the users who are working on those projects.

Using an API Explorer I can get a list of Projects (below is the example of my API and data).

API:
https://my-instance.service-now.com/api/now/table/pm_project?sysparm_fields=number%2Cu_resource_list&sysparm_limit=1

Data:

{
   "result":[
      {
         "assigned_users":"7961b907db9253045fbdf1fabf9619d4,55617907db9253045fbdf1fabf9619d2,c4c46419dba6d7045fbdf1fabf9619b5",
         "project_number":"11216"
      }
   ]
}

And using a separate API (see below example of my API and the data), I can get the list of users from "Sys_User" table.

API: https://my-instance.service-now.com/api/now/table/sys_user?sysparm_query=&sysparm_fields=sys_id%2Cemail

Data:

{
   "result":[
      {
         "sys_id":"7961b907db9253045fbdf1fabf9619d4",
         "email":"test_user1@my-site.com"
      },
      {
         "sys_id":"55617907db9253045fbdf1fabf9619d2",
         "email":"test_user2@my-site.com"
      },
      {
         "sys_id":"c4c46419dba6d7045fbdf1fabf9619b5",
         "email":""
      }
   ]
}

Then, I’m performing joins between data from "pm_project" and "sys_user" outside of ServiceNow to assign "emails" instead of "sys_id" to the projects and produce the final list like one below:

{
   "result":[
      {
         "assigned_users":"test_user1@my-site.com,test_user2@my-site.com,",
         "project_number":"11216"
      }
   ]
}

Is there a way (may be Scripted REST API) where I can do this join inside ServiceNow itself? And also, I don’t want to include users with empty "email" from "sys_user" in my final result. Can someone please advise!

8 thoughts on “ServiceNow – Scripted REST APIs – How to GET data from two tables and JOIN them”

Leave a Comment