Query Syntax & Structure

If you're familiar with SQL queries, you shouldn't have much trouble structuring VQL queries since they share most of the same syntax: SELECT, FROM, WHERE, FIND, etc. With SQL, you typically query relational database tables with data records and fields in rows and columns. With VQL, the same general idea applies as does the way in which you structure queries.

The examples here use the following naming convention:

SELECT {fields} FROM {object}

Where {fields} is field name or comma-separated list of multiple field names for the specified object. Use the Metadata API endpoints to retrieve queryable fields.
Where {object} is documents, relationships, events, users, workflows, or Vault objects (product, country, etc.). These are the only queryable objects.
Note: The following objects are not queryable: binders, groups, roles, lifecycles, picklists, security policies.

Basic Queries

SELECT & FROM Statements

SELECT and FROM work together to form the basis of all queries and can be used entirely on their own.

Query Description API Version
SELECT {fields} FROM documents Retrieve fields from all documents. v1.0+
SELECT {fields} FROM relationships Retrieve fields from all document relationships. v3.0+
SELECT {fields} FROM events Retrieve fields from all document events. v6.0+
SELECT {fields} FROM users Retrieve fields from all users. v1.0+
SELECT {fields} FROM workflows Retrieve fields from all workflows. v4.0+
SELECT {fields} FROM {Vault object} Retrieve fields from all object records. v8.0+

In all Vault queries, you SELECT object fields FROM their corresponding objects. For example:

SELECT id, name__v, status__v FROM documents  |  Returns the ids, names, and status from all documents
SELECT user_name__v FROM users  | Returns the user names from all users
SELECT id FROM product__v  |  Returns the ids from all products
  • You may include any "queryable" field in the SELECT statement. Use the Metadata APIs to find fields where "queryable": true.
  • You may include multiple fields in the SELECT statement. Use commas to separate fields.
  • You may include any "queryable" object in the FROM statement.
  • You may include only one (1) object in the FROM statement.

WHERE Filter

While SELECT and FROM form a basic query, they can also produce a very large result set. WHERE allows you to apply search filters to narrow the resulting data.

Query Description API Version
SELECT {fields} FROM documents WHERE {field}{operator}{value} Retrieve fields from documents which meet the conditions of the filter. v1.0+
SELECT id, name__v, status__v FROM documents WHERE type__v = 'Promotional Piece'
SELECT user_name__v FROM users WHERE `last_login__v` > '2016-01-01'
SELECT id FROM product__v WHERE name__v = 'Nyaxa'
  • The WHERE filter can include a variety of "operators", allowing you to further refine query results. These are described in the sections below.
  • Fields vary depending on the document or object being queried.
  • Unless otherwise noted, all fields that can be used in SELECT can also be used in WHERE.

Using Comparison Operators

When querying documents or Vault objects, you can use the following operators in the WHERE filter and FIND operator.

Query Description API Version
SELECT {fields} FROM {object} WHERE {field} = {'value'} Field values equal to the specified value. v1.0+
SELECT {fields} FROM {object} WHERE {field} != {'value'} Field values not equal to the specified value. v1.0+
SELECT {fields} FROM {object} WHERE {field} < {'value'} Field values are less than the specified value. v1.0+
SELECT {fields} FROM {object} WHERE {field} > {'value'} Field values are greater than the specified value. v1.0+
SELECT {fields} FROM {object} WHERE {field} <= {'value'} Field values are less than or equal to the specified value. v1.0+
SELECT {fields} FROM {object} WHERE {field} >= {'value'} Field values are greater than or equal to the specified value. v1.0+

Equal to (=)

SELECT id, document_number__v FROM documents WHERE status__v = 'Approved'
SELECT id, name__v, generic_name__vs FROM product__v WHERE therapeutic_area__vs = 'hematology__vs'
SELECT id, user_name__v, security_profile__v FROM users WHERE user_locale__v = 'es_US'

Not Equal to (!=)

SELECT id, document_number__v FROM documents WHERE status__v != 'Obsolete'
SELECT workflow_id__v, workflow_name__v FROM workflows WHERE workflow_status__v != 'Cancelled'
SELECT user_first_name__v, user_last_name__v FROM users WHERE security_profile__v != 'vault_owner__v'

Less than (<)

SELECT id, document_number__v FROM documents WHERE document_creation_date__v < '2016-04-23'
SELECT workflow_id__v, task_id__v FROM workflows WHERE workflow_startDate__v < '2016-04-23'
SELECT id, study_name__vs FROM study__v WHERE study_start_date__vs < '2016-04-23'

Greater than (>)

SELECT id, document_number__v FROM documents WHERE version_creation_date__v > '2016-04-23'
SELECT id, site_status__v, location__v FROM site__v WHERE modified_date__v > '2016-04-23'
SELECT id, user_email__v FROM users WHERE last_login__v > '2016-04-23'

Less than or Equal to (<=)

SELECT id, document_number__v FROM documents WHERE version_modified_date__v <= '2016-04-23T07:30:00.000Z'

Greater than or Equal to (>=)

SELECT id, document_number__v FROM documents WHERE locked_date__v >= '2016-04-23T07:30:00.000Z'

Using Standard Operators

When querying documents or Vault objects, you can use the following operators in the WHERE filter and FIND operator.

Query Description API Version
SELECT {fields} FROM {object} WHERE {field_1} = {'value_1'} AND {field_2} = {'value_2'} Field values are equal to both specified values. v1.0+
SELECT {fields} FROM {object} WHERE {field_1} = {'value_1'} OR {field_2} = {'value_2'} Field values are equal to either specified value. v1.0+
SELECT {fields} FROM {object} WHERE {field} CONTAINS ({'value_1'},{'value_2'},{'value_3'}) Field values are equal to any of the specified values. v1.0+

AND

The AND operator returns results when both values meet the conditions. For example:

SELECT id, name__v FROM documents WHERE type__v = 'Reference Document' AND status__v = 'Approved'

OR

The OR operator returns results when any of the values meet the conditions. For example:

SELECT id, name__v FROM documents WHERE version_creation_date__v > '2016-04-23' OR version_modified_date__v > '2016-04-23'

CONTAINS

The CONTAINS operator is used to enclose multiple values in parentheses. This uses the OR operator logic. For example:

SELECT id, name__v FROM documents WHERE language__v CONTAINS ('English', 'Spanish', 'French')

The query above returns documents with English OR Spanish OR French set on the language field.

BETWEEN

The BETWEEN operator is used with AND to compare data between two different values. For example:

SELECT id, name__v FROM documents WHERE document_creation_date__v BETWEEN '2016-02-14' AND '2016-04-23'

LIKE

The LIKE operator is used with the wildcard character '%' to search for matching field values when you don't know the entire value. For example:

SELECT id, name__v FROM documents WHERE name__v LIKE 'Ny%25'

The query above returns documents where the name__v value starts with "Ny". In this example, '%25' is URL encoded '%' character.

IN

The IN operator is used to test whether or not a value (stated before the IN operator) is "in" the list of values provided after the IN operator. For example:

SELECT id FROM product__v WHERE id IN (SELECT id FROM document_product__vr)

The IN operator can only be used for inner join relationship queries on documents and objects.

Performing Keyword Searches on Documents & Vault Objects

Query Description API Version
SELECT {fields} FROM documents FIND ({'keywords'}) Search document fields. v8.0+
SELECT {fields} FROM documents FIND ({'keywords'} SCOPE CONTENT) Search document content. v8.0+
SELECT {fields} FROM documents FIND ({'keywords'} SCOPE ALL) Search document fields and content. v8.0+
SELECT {fields} FROM documents FIND ({'keywords'} SCOPE {field}) Search specific document field. v15.0+
SELECT {fields} FROM {Vault object} FIND ({'keywords'}) Search object fields. v14.0+
SELECT {fields} FROM {Vault object} FIND ({'keywords'} SCOPE {field}) Search specific object field. v15.0+

The following rules apply:

  • Document and object names and field names must be lower-case. The search phrase is not case-sensitive.
  • The wildcard character is supported. Learn more about wildcard searches below.
  • The search phrase is not tokenized. Learn more about tokenization below.

Note: When precise matching is absolutely critical, we recommend using the WHERE filter on fields instead of using the FIND operator. FIND is more appropriate for interactive applications.

SELECT {fields} FROM documents FIND ({'keywords'})

When using FIND on documents, all queryable document fields are searched. For example:

SELECT id, name__v FROM documents FIND ('insulin')

When using multiple keywords, Vault places an implicit OR operator between each. The following queries are equivalent:

SELECT id, name__v FROM documents FIND ('insulin diabetes')
SELECT id, name__v FROM documents FIND ('insulin OR diabetes')

Vault also places an implicit wildcard character '*' at the end of the last keyword. Therefore:

The query below returns the same results as the two above (or more, as all words starting with 'dia' will be found):

SELECT id, name__v FROM documents FIND ('insulin dia')

To search document fields containing all keywords, use the AND operator between each. For example:

SELECT id, name__v FROM documents FIND ('insulin AND diabetes')

To search for an exact match to all keywords, enclose the string in double-quotes within the single-quotes. For example:

SELECT id, name__v FROM documents FIND ('"blood sugar"')

SELECT {fields} FROM documents FIND ({'keywords'} SCOPE CONTENT)

To search within the document content, use SCOPE CONTENT. For example:

SELECT id, name__v FROM documents FIND ('insulin') SCOPE CONTENT

SELECT {fields} FROM documents FIND ({'keywords'} SCOPE ALL)

To search within the document content and all queryable document fields, use SCOPE ALL. For example:

SELECT id, name__v FROM documents FIND ('insulin') SCOPE ALL

SELECT {fields} FROM documents FIND ({'keywords'} SCOPE {field})

To search within a specific document field, use SCOPE {field}.

You can only include one text/string-type or exactmatchstring-type document field. For example:

SELECT id, name__v FROM documents FIND ('insulin' SCOPE name__v)
SELECT id, name__v FROM documents FIND ('insulin' SCOPE title__v)
SELECT id, name__v FROM documents FIND ('insulin' SCOPE description__v)

The following document fields are not supported here: type__v, subtype__v, classification__v, lifecycle__v, status__v, and filename__v.

When using SCOPE {field} on documents, you can combine it with SCOPE CONTENT. For example:

SELECT id, name__v FROM documents FIND ('cholecap' SCOPE product__v AND 'prescribing information' SCOPE CONTENT)

SELECT {fields} FROM {Vault object} FIND ({'keywords'})

When using FIND on Vault objects, all text/string-type object fields are searched. For example:

SELECT id, name__v FROM product__v FIND ('phosphate')
SELECT id, name__v FROM product__v FIND ('phosphate sulfate')

SELECT {fields} FROM {Vault object} FIND ({'keywords'} SCOPE {field})

To search within a specific object field, use SCOPE {field}.

You can only include one text/string-type object field. For example:

SELECT id, name__v FROM product__v FIND ('phosphate' SCOPE generic_name__vs)

You can look up object reference fields by using dot-notation on the object relationship name (__vr, __cr) and the referenced object field name. For example:

SELECT id, name__v, product__vr.name__v FROM study__v FIND ('phosphate' SCOPE product__vr.name__v)

SELECT {fields} FROM {Vault object} FIND ({'keywords'} SCOPE {field}) WHERE {field} = {value}

When using FIND and/or SCOPE, you can use the WHERE filter to narrow results. WHERE must be placed after FIND and SCOPE. For example:

SELECT id, name__v FROM product__v FIND ('phosphate') WHERE therapeutic_area__vs = 'cardiology__vs'
SELECT id, name__v FROM product__v FIND ('phosphate' SCOPE generic_name__vs) WHERE therapeutic_area__vs = 'cardiology__vs'

Using the Wildcard Character

When searching documents and objects using the FIND operator, use the wildcard character * to find partial matches. For example:

  • FIND 'ins* dia* glu*' returns documents containing words starting with 'ins' OR 'dia' OR 'glu'.

You can place the wildcard character in any part of the keyword. For example:

  • FIND '*ology' returns documents or objects with words ending with 'ology', e.g., psychology, urology, etc.
  • FIND 'o*ology' returns documents or objects with words starting with 'o' and ending with 'ology', e.g., oncology, ophthalmology, etc.

When the search string is not enclosed in parentheses or double-quotes, Vault places an implicit wildcard character * at the end of the last search term by default. Therefore:

  • FIND 'glu' is the same as FIND 'glu*' - All words starting with 'glu' will be found. In this case, the wildcard character is not needed.

You can use the wildcard character when searching document content: For example:

  • FIND '*ology' SCOPE CONTENT returns documents containing words ending in 'ology' in the document content.

About Search Term Tokenization

When performing keyword searches using API v8.0 or earlier, Vault automatically tokenizes CamelCase, alphanumeric, and delimited strings. For example:

  • FIND 'CamelCase' becomes FIND 'Camel OR Case'
  • FIND 'abc123' becomes FIND 'abc OR 123'
  • FIND 'abc-def' becomes FIND 'abc OR def'

To disable tokenization, set the tokenize request parameter to false. For example (cURL):

q=SELECT id, name__v FROM documents FIND 'CamelCase'" https://myvault.veevavault.com/api/v15.0/query?tokenize=false

You can also disable tokenization by surrounding the search phrase in double-quotes within single-quotes. For example:

SELECT id, name__v FROM documents FIND '"abc123"'

As of API v9.0, Vault only tokenizes alphanumeric strings. To enable tokenization of CamelCase and delimited strings, set the tokenize request parameter to true.

Using Date & DateTime Values

Values for Date and DateTime field types must be entered in UTC (Coordinated Universal Time) and not the user's time zone. For example:

  • Date formats are YYYY-MM-DD ('2016-04-23').
  • DateTime formats are YYYY-MM-DD'T'HH:MM:SS.SSS'Z' ('2016-04-23T15:30:00.000Z').

When using date formats, the time is assumed to be "00:00:00" (midnight of the specified date). For example:

SELECT id, name__v FROM documents WHERE document_creation_date__v > '2015-11-01'

The query above returns all documents completed after October 31st. The completion date of one minute after midnight on November 1st is considered greater than "2015-11-01".

Using True/False & Null Values

Boolean (True/False) field values are used to find documents and objects having these values. For example:

SELECT id, name__v FROM documents WHERE crosslink__v = TRUE
SELECT id, name__v FROM documents WHERE locked__v = FALSE

Null field values are used to find documents and objects having no value set on a particular field. For example:

SELECT id, name__v FROM documents WHERE external_id__v = NULL

Performing Case-Insensitive Queries

By default, all field values are case-sensitive. This applies to all field types. For example:

  • Using the filter name__v = 'cholecap' (where name__v is a String field type) returns no results if the field value is Cholecap.
  • Using the filter therapeutic_area__vs = 'Hematology__v' (where therapeutic_area__vs is a Picklist field type) returns no results if the field value is hematology__v.

As of API v14.0, Vault allows you to perform "case-insensitive" queries by using the caseinsensitive({FIELD_NAME}) parameter in the WHERE filter. For example:

  • SELECT id FROM product__v WHERE caseinsensitive(name__v) = 'cholecap' returns results even if the field value is "Cholecap".

Note the following scope and limitations:

  • This is supported only in the WHERE clause. It is not supported in the SELECT clause.
  • This is supported only on String field types of Vault Objects.
  • This is supported in the WHERE clause of sub-select statements (relationship queries).

Using Vault Document Functions

Query Description API Version
SELECT {fields} FROM documents WHERE status__v = STEADYSTATE() Retrieve fields from all documents in a Steady State (v1.0, v2.0, etc.). v8.0+
SELECT {fields} FROM documents WHERE status__v = OBSOLETESTATE() Retrieve fields from all documents in a Obsolete State. v8.0+
SELECT {fields} FROM documents WHERE status__v = SUPERSEDEDSTATE() Retrieve fields from all documents in a Superseded State. v8.0+
SELECT LATESTVERSION {fields} FROM ALLVERSIONS documents Retrieve fields from the latest version of all documents. v8.0+

Use one of the following document functions to filter on document lifecycle states:

SELECT id, name__v FROM documents SELECT id, name__v FROM documents WHERE status__v = STEADYSTATE()
SELECT id, name__v FROM documents SELECT id, name__v FROM documents WHERE status__v = OBSOLETESTATE()
SELECT id, name__v FROM documents SELECT id, name__v FROM documents WHERE status__v = SUPERSEDEDSTATE()

Note: Document functions are formatted differently in API v8.0 and earlier:

SELECT id, name__v FROM documents SELECT id, name__v FROM documents WHERE STEADYSTATE() = true
SELECT id, name__v FROM documents SELECT id, name__v FROM documents WHERE OBSOLETESTATE() = true
SELECT id, name__v FROM documents SELECT id, name__v FROM documents WHERE SUPERSEDEDSTATE() = true

Setting the Sort Order of Results

Query Description API Version
SELECT {fields} FROM documents ORDER BY {field} ASC Sort ascending order. v8.0+
SELECT {fields} FROM documents ORDER BY {field} DESC Sort descending order. v8.0+
SELECT {fields} FROM documents FIND ({'keywords'}) ORDER BY RANK Sort by keyword relevancy. v10.0+

Sorting Results in Ascending/Descending Order

The order in which results are displayed is variable. To enforce a specific order, use the ORDER BY operator. For example:

  • SELECT id FROM documents ORDER BY id DESC displays the IDs in descending order (99, 98, 97, etc.).
  • SELECT id FROM documents ORDER BY id ASC displays the IDs in ascending order (1, 2, 3, etc.).

You can order results using any document or object field. For example:

  • SELECT name__v FROM documents ORDER BY name__v DESC orders the results by name instead of ID and displays the names in descending order.
  • SELECT name__v FROM documents ORDER BY name__v ASC ascending order.

You can enforce both the primary and secondary order by using a comma-separated string of field names. The field sort priority is left to right. For example:

  • SELECT name__v, type__v FROM documents ORDER BY type__v DESC, name__v DESC sorts the results first by type and then by name, both in descending order.

Example response:

    {
      "type__v": "Resource",
      "name__v": "VeevaProm Resource Doc" 
    }
    {
      "type__v": "Resource",
      "name__v": "Nyaxa Resource Doc" 
    }
    {
      "type__v": "Resource",
      "name__v": "CholeCap Resource Doc" 
    }
    {
      "type__v": "Claim",
      "name__v": "VeevaProm Claim Doc" 
    }
    {
      "type__v": "Claim",
      "name__v": "Nyaxa Claim Doc" 
    }
    {
      "type__v": "Claim",
      "name__v": "CholeCap Claim Doc" 
    }     

Sorting Results by Relevancy to a Search Phrase

When performing searches with FIND, you can sort the results by relevancy to a search phrase using the ORDER BY rank operator. For example:

  • SELECT id, name__v FROM documents FIND 'ABC' ORDER BY rank sorts the results in descending order starting with those most closely matching the search phrase.

Example response:

    {
      "id": 99,
      "name__v": "Document ABC"     
    }
    {
      "id": 97,
      "name__v": "Document ABCD"    
    }
    {
      "id": 98,
      "name__v": "Document ABCDE"
    }

Limiting the Number of Results per Page

Query Description API Version
SELECT {fields} FROM documents LIMIT {number} Limit number of results per page. v8.0+
SELECT {fields} FROM {OBJECT} LIMIT {number} Limit number of results per page. v8.0+

By default, the maximum number of results displayed per page is 200 for documents and 1000 for objects. This can be lowered using the LIMIT operator. For example:

  • SELECT id FROM documents LIMIT 25 returns 25 documents per page.
  • SELECT id FROM product__v LIMIT 250 returns 250 object records per page.

Using LIMIT does not change the total number of results found, only the number displayed per page.

Note: When performing queries with unusually large numbers of fields in the SELECT clause, the API may scale down the number of results per page to reduce stress on the memory limit of the system. When this happens, you may experience an unexpected number of results in your response, e.g., you were expecting 1000 results per page but the system only returned 400 per page. In these cases, the system returns the same total number of results; they are simply distributed across more pages. This applies only to API v11.0 and later.

Paginating Results

When the number of results found exceeds the number displayed per page, use the OFFSET operator to display the next and previous pages of results.

For example: Assume a query returns 500 total results and the LIMIT is set to display 100 results per page.

  • The first page displays results 0-99. To see the second page, use LIMIT 100 OFFSET 100.
  • The second page displays results 100-199. To see the third page, use LIMIT 100 OFFSET 200.

As of API v10.0, document and Vault Object queries include the next_page and previous_page URL endpoints when pagination is required. For example:

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "limit": 100,
        "offset": 200,
        "size": 100,
        "total": 500,
        "previous_page": "/api/v11.0/query/c2b58293-1606-4c99-925d-b9b89e83670e?limit=100&offset=100",
        "next_page": "/api/v11.0/query/c2b58293-1606-4c99-925d-b9b89e83670e?limit=100&offset=300"

The URLs include information from the original query and provide a simple method of automatically paginating to the next and previous pages of results. Simply access the URL with a GET method to retrieve the data.

Note: The next_page and previous_page endpoints only remain active for 20 minutes following query submission. After that, the original query must be resubmitted.
Note: When the query result doesn’t generate the next_page and previous_page URLs, submit additional queries using OFFSET to paginate over the result set.