Last time, we discussed the concept behind GQL as being a web service with SQL-like syntax and listed use cases along with high-level requirements. We want to use it similar to JSONv2 by sending a command using GET and retrieve a result set in JSON. This time, let's look at some design considerations.

DESIGN COMPONENTS

Here are some components we need to consider:

1. GQL syntax - define GQL syntax to be used
2. Processor - handle bidirectional HTTP traffic
3. Parser - parse GQL query for processing
4. Retriever - query and retrieve result set
5. Encoder - encode result set for output

Let's also add that this will be a scoped application. The design will evolve over time as we start simple and refactor as needed.

GQL SYNTAX

Under the hood, we know that all database transactions in ServiceNow are being done in SQL. However, we don't have direct access to it. Even if we did, it may not prove to be useful in all cases. For example, selecting a display value may require joins or subqueries and the SQL statement may quickly become unwieldy. Instead, we'll use GlideRecord or, more specifically, Scoped GlideRecord. This means GQL will be constrained by what we can do with Scoped GlideRecord.

In Part 1, our requirements called for

1. Focus on SELECT for now.
2. Don't need complex Subqueries or CTEs (Common Table Expressions) for now.
3. Select the raw and/or display value of a field.
4. Be able to rename column headings.
5. Support dot walking of reference fields.
6. Be able to filter the result set.
7. Be able to sort by one or more columns.

In its simplest form, we have

SELECT *

FROM table

This returns all columns from table; we'll define what "all" means later when we discuss raw values vs. display values. Since we'd also like to embed this in a query string and use GET, we may rewrite this as

SELECT * FROM table

Let's further stipulate that white spaces are optional and all keywords must be in caps. This also helps with readability since ServiceNow table and column names are in lowercase. So the above can be rewritten as

SELECT*FROMtable

In this article, for readability, we'll continue using white spaces, though. Now, let's add the optional WHERE clause. Instead of reinventing it, we can just use Encoded Query Strings, which I've always found to be compact and powerful:

SELECT *

FROM table

WHERE encoded_query_string

Encoded Query Strings may also contain one or more ORDERBY clauses for descending or ascending order, so we can sort the result set using them. We'll look at Encoded Query Strings and the syntax in more detail later.

Let's also add optional LIMIT, which we'll borrow from MySQL:

SELECT *

FROM table

WHERE encoded_query_string

LIMIT [offset,] row_count

Now, let's turn to the field list instead of the asterisk. One or more columns may be listed, using dot-walking, if needed. Dot-walking allows access to related tables through reference fields (although dot-walking can be nested multiple levels, recommended limit is three levels). We'll also optionally allow renaming column headings by enclosing a new name in square brackets (this is not the same as aliases in SQL since the renamed column headings don't act as new column names that can be used in queries):

SELECT column_1, column_2 [column_2_heading], column_3

FROM table

WHERE encoded_query_string

LIMIT [offset,] row_count

Lastly, let's allow for comments, using double dashes, as in SQL, in multi-line mode:

SELECT column_1, column_2 [column_2_heading], column_3   -- this is a comment

FROM table

WHERE encoded_query_string

LIMIT [offset,] row_count

Summing it all up, here's an example:

SELECT   -- sample GQL statement

  number [Number]

  , short_description [Title]

  , state [State]

  , caller_id.email [Caller Email]

  , caller_id.manager.email [Manager Email]

  , cmdb_ci

FROM incident

WHERE active=truepriority<2EQORDERBYpriorityORDERBYDESCnumber

LIMIT 5,10

Without white spaces, which may be useful for including in a GET query string, it is

SELECTnumber[Number],short_description[Title],state[State],caller_id.email[Caller Email],caller_id.manager.email[Manager Email],cmdb_ciFROMincidentWHEREactive=truepriority<2EQORDERBYpriorityORDERBYDESCnumberLIMIT5,10

Let's work with what we have so far for now and discuss other features later. If you'd like anything specific, please leave a comment below.

RAW VALUE VS. DISPLAY VALUE

Some fields have both raw and display values. The State field for Incident, for example, has numeric raw values while the display value might say "Resolved". Other field types, such as date, duration, etc., also have both raw and display values. Also, a reference field's raw value is the sys_id from the related table while the display value is from the display value field of the related table.

In our SELECT statement, we'd like to specify raw and/or display value of each field. JSONv2, by default, returns raw values unless the "displayvalue" option is set to "true" (display, not raw, values are returned) or "all" (both raw and display values are returned and the display column names are prefixed with "dv_").

In GQL, let's return raw values unless column names start with "dv_", keeping consistent with JSONv2 (the same convention also applies to ODBC SQL). Since what we want in most cases are display values, this will add a bit of extra typing, forcing us to rewrite the above GQL as (see "dv_" for state and cmdb_ci)

SELECT   -- sample GQL statement

  number [Number]

  , short_description [Title]

  , dv_state [State]

  , caller_id.email [Caller Email]

  , caller_id.manager.email [Manager Email]

  , dv_cmdb_ci

FROM incident

WHERE active=truepriority<2EQORDERBYpriorityORDERBYDESCnumber

LIMIT 5,10

For

SELECT *

FROM table

we'll return both raw and display values with the column names of the display values starting with "dv_", similar to JSONv2 with "displayvalue" set to "all".

Next time, we'll continue with the rest of the design. Please note that the design may change over time, especially with your input/feedback.

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

John Chun, PhD PMP