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

Question

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":"[email protected]"
      },
      {
         "sys_id":"55617907db9253045fbdf1fabf9619d2",
         "email":"[email protected]"
      },
      {
         "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":"[email protected],[email protected],",
         "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!

question from:https://stackoverflow.com/questions/65910404/servicenow-scripted-rest-apis-how-to-get-data-from-two-tables-and-join-them

Answer 1

I though this might be possible using a Database View (https://docs.servicenow.com/bundle/paris-platform-administration/page/use/reporting/concept/c_DatabaseViews.html), but it appears that you are using a Glide List to hold the list of users. Database views are rather limited, and there is no way to perform a join using a Glide List.

Your only option would be to create a Scripted REST API, which would be completely custom JavaScript. You can do anything you want in a Scripted REST API. You can return any data that you want and filter the data anyway you want.