Rest API and a "GET" function of a join of two tables.

[vanceturner]

I need to get at relationship data that requires a join of two or more tables. How can I do that in the REST API?

Here is the GET of a single table that works. I want to find a way to retrieve the XML payload from the link provided in the support_group.

I am trying not to have to unroll the XML payload, retrieve the support_group XML as an additional get, and then bundle everything back up.

GET("/api/now/table/cmdb_ci?sysparm_fields=u_ci_id%2Clocation%2Cinstall_status%2Cfqdn%2Cip_address%2Cdns_domain%2Csys_dom

ain%2Cu_server_type%2Csupport_group%2Cassigned_to%2Csupported_by&sysparm_limit=$record_limit&sysparm_offset=$offset",

                              {'Authorization' => "Basic $encoded_auth",

                                'Accept' => 'application/xml'});

[Chuck Tomasi]

Hi Vance,

You'll want to look at database views to do your join. Views behave like tables in the system in that you can do your REST call directly to the view using the view name for the table.

Database Views - ServiceNow Wiki

[cwilliams]

Hi Vance,

You've likely found a solution by now but in case others might want options I managed to achieve a join of 2 tables by passing an encoded join query via sysparm_query.

As part of a data transfer I wanted to verify if a list of attachments had been uploaded, the problem was that the some of the attachments had duplicate file names but I knew which RITM record they should be associated with.

The resulting query would look like this:

GET https://<instance_name>.service-now.com/api/now/attachment

table_name=sc_req_item^file_name=<duplicated_file_name>^!JOINsys_attachment.table_sys_id=sc_req_item.sys_id!number=<RITM_number>

However, when applying a filter on both the sys_attachment table and the sc_req_item table, the response body is empty, but the header attribute 'x-total-count' indicates the number of records returned by the call. In my case that's all I needed to confirm the existence of an attachment.

The response body is returned as expected if only one of the tables is filtered e.g:

table_name=sc_req_item^file_name=<duplicated_file_name>^!JOINsys_attachment.table_sys_id=sc_req_item.sys_id

or

JOINsys_attachment.table_sys_id=sc_req_item.sys_id!number=<RITM_number>

The same issue doesn't seem to occur when using the table API and I was able to successfully perform the following call:

GET https://<instance_name>.service-now.com/api/now/table/sc_item_option

sys_idSTARTSWITH4^!JOINsc_item_option.sys_id=sc_item_option_mtom.sc_item_option!^JOINsc_item_option_mtom.request_item=
sc_req_item.sys_id!sys_idSTARTWITH4

Which returns records from the table storing variable values where there sys_id starts with '4' where the sys_id of the RITM they are associated with (via the MTOM table) also begins with '4'. Why you would want to do this, I don't know, but it shows what is possible.

As Chuck mentions a database view would do the job, but for me I only needed to do a one-off verification of data and didn't need the overhead of a new view.

Hope this helps!

[Jhonatan P]

Hello cwilliams,

Your response was really helpful. I had to enable Debug for REST API and see how it translate it to a SQL query, and the ^!JOIN was translated to an "INNER JOIN". But what i need is a left join, do you know the operator for that?

And BTW this feature apparently is not documented, at least i couldn't find it. Where did you find it and where can i find more of this 🙂 ?

Thank you again.

Regards,

Jhonatan Passalacqua

[cwilliams]

Hi Jhonatan,

Glad the information could help someone 🙂

As you say there isn't any documentation, this is mostly true...the GlideRecord addJoinQuery method is documented, but how to write the statements as an encoded query isn't. Additionally, using the getEncodedQuery method doesn't seem to return an encoded join query that I had created using the addJoinQuery method. At least it didn't work for me when I tried.

Regardless, say I wanted to get all the catalog items that used a certain variable set:

var jq = gr.addJoinQuery('io_set_item', 'sys_id', 'sc_cat_item'); 
jq.addCondition('variable_set','<variable set sys_id>');

Would be the same as:

gr.addEncodedQuery('JOINsc_cat_item.sys_id=io_set_item.sc_cat_item!variable_set=<variable set sys_id>');

There's just a few script OOB script includes that use this functionality , 'TaskSLAController' and 'RepairTaskSLAController' for example.

I'm not sure exactly what you're trying to do, but encoded related list queries (partially documented) might be relevant and even encoded subqueries (no documentation, but used in at least one UI macro), but a database join could be the way to go.

Regards