No, GQL isn't Google Query Language; it's Glide Query Language, and it's exclusively for ServiceNow.

Having been a long-time SQL user who appreciates the simple, yet powerful construct of the language, I occasionally miss not being able to use it when I'm working with web services or data export in ServiceNow. Don't get me wrong; I like using JSONv2 Web Service. But how can I select only the columns I want? Or, how can I rename column headings? Can I show only the display values? Can I have calculated columns? Hmm, it's not trivial to do any of these with web services while they are trivial in SQL. So I dream. But I don't dream too big in that I really only want to be able to SELECT data (read only) for now and not UPDATE, DELETE, etc. Yes, there are ways to work with SQL today; although the infamous gs.sql() has now been retired (although I liked using it occasionally), the ODBC still works. But it's not a web service (technically it is SOAP under the cover, but we still have to deal with installing the ODBC driver, configure, etc.). And, yes, there are other ways to partially accomplish this, by using Export Sets, Database/Form Views, etc., but you have to configure them on the server, typically as an admin. Ideally, I'd like to have the convenience of both web services and SQL so data can be readily consumed with ease. So begins my quest.

I'd like to send a SQL-like query, similar to the example below, to ServiceNow using web service calls and receive the result set:

SELECT

  number [Number]

  , short_description [Title]

  , state [State]

  , caller_id.email [Caller Email]

  , caller_id.manager.name [Manager]

FROM incident

WHERE active=true

ORDERBY priority

Some use cases might include:

1. Retrieve Incident (or any table/view) data with specific columns in the order I want.
2. Build a dynamic report.
3. Create a CSV file with custom column headings.
4. Add a calculated column; for example, convert one currency to another.
5. Apply a custom format to a column.

Here are some high-level requirements:

1. Preserve the JSONv2 API as much as possible.
2. Utilize the GlideRecord syntax as much as possible.
3. Mimic the SQL syntax as much as possible.
4. Send SQL scripts using GET.
5. Focus on SELECT for now.
6. Don't need complex Subqueries or CTEs (Common Table Expressions) for now.
7. Select the raw and/or display value of a field.
8. Be able to rename column headings.
9. Support dot walking of reference fields.
10. Be able to filter the result set.
11. Be able to sort by one or more columns.

The above lists may not be exhaustive and I'll update them over time. If you have more ideas, please add comments below. If you like this concept and you might be able to use it, please hit Like. This will be a highly collaborative effort, so your support and feedback would be absolutely essential to get this moving forward.

Next time, we'll look at some design considerations.

Please feel free to connect, follow, post feedback / questions / comments, share, like, bookmark, endorse.

John Chun, PhD PMP