Introduction to Vault Queries

When an application invokes a Query call, it passes in a Vault Query Language (VQL)) statement (a SQL-like statement) that specifies the object to query (in the FROM clause), the fields to retrieve (in the SELECT clause), and any optional filters to apply (in the WHERE filter and FIND operator) to narrow your results. Additional syntax may also be included to control how your results are displayed.

Detailed information about VQL syntax and how to structure queries is covered in the Query Syntax & Structure article, including information for retrieving fields from a single object (document, product, study, etc.). As of API V10.0, the Query API supports relationship queries (Joins), where more than one object is included in a single query. This is covered in the Relationship Queries (Joins) and Many-to-Many Relationship Queries articles.

VQL Standards & Specifications

The following standards and specifications apply to Vault queries.

Case Sensitivity

VQL Syntax
  • VQL grammar is not case sensitive. You may enter clauses such as SELECT, FROM, and WHERE in upper or lower-case.
  • In these articles, we write all VQL grammar in upper-case to highlight the information.
Field & Object Names
  • Field and object names in the query string are case sensitive.
  • You must enter field and object names such as name__v, documents, and product__v in lower-case.
  • This does not apply to the id field name on documents (only), which may be entered as id or ID.
  • Note that older API versions include some mixed-case field names. Retrieve field names to confirm correct formatting.
Field Values
  • 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.
Bypassing Field Value Case-Sensitivity
  • As of API v14.0, you can use the caseinsensitive({FIELD_NAME__V}) to bypass case-sensitivity. For example:
  • Using the filter caseinsensitive(name__v) = 'cholecap' returns results even if the value is Cholecap.
  • Learn more performing case-insensitive queries.

Search Term Tokenization

  • When performing keyword searches using API v8.0 or earlier, Vault automatically tokenizes CamelCase, alphanumeric, and delimited strings.
  • 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.
  • Learn more about search term tokenization.

Date & Time Formats

  • Date and DateTime field values are entered and returned in UTC (Coordinated Universal Time) and not the user's time zone.
  • Date formats are YYYY-MM-DD. For example: '2016-01-16'.
  • DateTime formats are YYYY-MM-DDTHH:MM:SS.SSSZ. For example: '2016-01-15T07:30:00.000Z'.
  • DateTimes must end with the .000Z UTC expression. The zeros may be any number.
  • Dates and DateTimes must be surrounded by single quotes in the query string.
  • Learn more about using dates and times.

Languages

  • Vault queries performed with field values in languages other than the user's language are matched against the label values in Vault's base language.
  • Field values without strings in Vault's base language return results in the system language.
  • This applies only to documents queries and document fields used in the SELECT and WHERE clauses.

Maximum Query String Length

  • The maximum length of a VQL statement is 10,000 characters.

Maximum Number of Records Returned

  • There is no maximum limit to the total number of records returned.
  • The default maximum number or records displayed per page is 200 for documents and 1000 for objects.

Limiting the Number of Records Returned

  • Vault supports the LIMIT and OFFSET parameters to limit the number of results displayed per page and paginate results.
  • As of API v10.0, you can use the next_page and previous_page URL endpoints to paginate results.
  • Learn more about limiting results.

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.
  • As of API v10.0, document and Vault Object queries include the next_page and previous_page URL endpoints when pagination is required.
  • Learn more about paginating results.

Sorting/Ordering Results

  • The order in which results is displayed is variable.
  • To enforce a specific order, use the ORDER BY operator.
  • The API supports primary and secondary sorting of fields in ascending/descending order. Learn more.
  • The API also supports sorting results by relevancy to a FIND operator search phrase. Learn more.

Response Formats

  • Vault queries support both JSON and XML response formats.
  • JSON (application/json) is the default response format.
  • To request an XML response, set the HTTP Request Header Accept to application/xml.

API Transaction Limits

  • Vault limits the number of API calls that can be made every 5 minutes and every 24 hours.
  • When either of these limits are reached, an API_LIMIT_EXCEEDED error message is returned and no further calls will be processed until the next 5 minute or 24 hour period begins.
  • The default limit every 5 minutes is 2000 calls.
  • The default limit every 24 hours is 100,000 calls.

User Permissions

  • To perform Vault Queries, you must be assigned the appropriate permissions by Admin.
  • In addition to API permissions, you must also have permissions to access the documents and objects of your queries.
  • Learn more about user permissions and access control.

Top

Submitting a Query

Submit a query by sending a POST or GET request to the /api/{version}/query endpoint. For example:

Using cURL Command-Line

Enter $ curl -k -X POST -H "Authorization: {SESSION_ID}" -d "q=SELECT id FROM documents" https://{customer}.veevavault.com/api/{version}/query

Using an HTTP Client

Send a GET request to https://{customer}.veevavault.com/api/{version}/query?q=SELECT id FROM documents

$ curl -X GET -H "Authorization: {SESSION_ID}" \ 
https://myvault.veevavault.com/api/v14.0/query?q=SELECT id FROM documents

Top

Vault Response

Vault's Query API supports JSON & XML response formats. JSON (application/json) is the default response format.

To request an XML response, set the HTTP Request Header Accept to application/xml. For example:

$ curl -X GET -H "Authorization: {SESSION_ID}" \ 
-H "Accept: application/xml" \ 
https://myvault.veevavault.com/api/v14.0/query?q=SELECT id FROM documents

On SUCCESS, the response includes the following information:

  • responseStatus - SUCCESS
  • responseDetails
    • limit - The number of records displayed per page. This can be modified. Learn more.
    • offset - The records displayed on the current page are offset by this number of records. This can be modified. Learn more.
    • size - The total number of records displayed on the current page.
    • total - The total number of records found.
    • previous_page - The Pagination URL to navigate to the previous page of results. This is not always available. Learn more.
    • next_page - The Pagination URL to navigate to the next page of results. This is not always available. Learn more.
  • data - All records found.

Top

About Vault Objects and Fields

In Vault, "objects" are divided into three main categories:

Standard Vault Objects

  • These include product__v, country__v, study__v, site__v, and others.
  • These vary by application (PromoMats, eTMF, Submissions, etc.).
  • The names of these objects always ends in __v, e.g., to query the products object, enter product__v.
  • All standard Vault objects and object fields are queryable.

Custom Vault Objects

  • Vault Admins can extend your organization's data by defining custom objects with information unique to your organization.
  • The names of these objects always ends in __c, e.g., to query a "custom object", enter custom_object__c.
  • All custom Vault objects and object fields are queryable.

Other Objects

  • These include documents, users, roles, groups, workflows, lifecycles, events, and relationships.
  • The names of these objects is simply their name, e.g., to query the documents object, enter documents.
  • Not all of these objects or their object fields are queryable.

Objects Available for Query

Queryable objects and the vaults in which they are available are listed below.

  • Note that this list is not all-inclusive and may vary, depending on your particular configuration.
  • As a best practice, always query your Vault to obtain the current collection of objects, object records, and object fields.

To retrieve the collection of Vault Objects, send a GET request to the /api/{VERSION}/metadata/vobjects endpoint. See the Retrieve Object Collection API

The following queryable objects are available in all Vault applications (PromoMats, eTMF, QualityDocs, MedComms, Submissions):

  • Documents: documents
  • Relationships: relationships
  • Events: events
  • Users: users
  • Workflows: workflows

The following objects are currently NOT queryable:

  • Binders binders
  • Groups groups
  • Roles roles
  • Lifecycles lifecycle__v
  • Picklists picklists
  • Security Policies securitypolicies

The following Vault objects are available in all Vault applications (PromoMats, eTMF, QualityDocs, MedComms, Submissions):

  • Product: product__v
  • Country: country__v

The following Vault objects are available in PromoMats vaults:

  • Directory: directory__v
  • Survey: survey__v
  • Key Message: key_message__v
  • Website: website__v
  • Applicant: 2253submissionapplicant__v

The following Vault objects are available in eTMF vaults:

  • Study: study__v
  • Study Country: study_country__v
  • Study Site: site__v
  • Location: location__v
  • Milestones: milestone__v

The following Vault objects are available in QualityDocs vaults:

  • Facility: facility__v
  • Department: department__v

Fields Available for Query

All objects listed below are queryable. However, not all fields on these objects are queryable.

To find queryable fields, send a GET request to the endpoints below and look for fields with the attribute: "queryable": true.

Documents & Binders: /api/{version}/metadata/objects/documents/properties endpoint. See the Retrieve All Document Fields API.

Document & Binder Relationships: /api/{version}/metadata/objects/documents/types/{document_type}/relationships endpoint. See the Retrieve Document Type Relationships API.

Document Events: /api/{version}/metadata/objects/documents/events/{event_type}/events/{event_subtype} endpoint. See the Retrieve Document Event SubType Metadata API.

Workflows: /api/{version}/metadata/objects/workflows endpoint. See the Retrieve Workflow Metadata API.

Users: /api/{version}/metadata/objects/users endpoint. See the Retrieve User Metadata.

About the LongText Field Type

As of 17.1, the LongText field type allows users to enter text strings up to 32,000 characters. While a FIND clause will always search the entire field, other queries will only return the first 250 characters of a longtext field by default. To return all of the characters, use the LONGTEXT() function. Note that VQL only supports SELECT clauses with LONGTEXT() function. For example:

SELECT id, LONGTEXT(long_text_field__c) FROM object__c 

Wildcards

LongText fields do not support wildcard (*) searching on spaces. For example, when trying to match on “Vault Query Language” in a LongText field, the following will find no results:

SELECT id FROM longtext_obj__c FIND('Vault*Language' SCOPE long_text_field__c)

But a the query below (using a normal text field):

SELECT id FROM longtext_obj__c FIND('Vault*Language' SCOPE text_field__c)

Will return the desired record.