You can use Vault Query Language (VQL) to access, retrieve, and interact with Vault data. This guide provides details on how to best utilize VQL, and outlines its syntax, structure, clauses, and operators. Although VQL queries share most of the same syntax as Structured Query Language (SQL), VQL statements allow you to perform queries specifically for Vault data.
When an application invokes a query call, it passes in a VQL statement that specifies the object to query such as Documents in the FROM clause, the fields to retrieve in the SELECT clause, and any optional filters to apply (in the WHERE and FIND clauses) to narrow your results:
SELECT one or comma-separated list of multiple field names
FROM an object
WHERE optional search filters to narrow resulting data
The following example query returns the id and name from all documents where the type is Promotional Piece:
SELECT id, name__v
FROM documents
WHERE type__v = 'Promotional Piece'
To send a VQL query, use the /api/{version}/query endpoint in the REST API Reference.
In the example below, we send a query to return the ID and name of all documents in the specified vault:
curl -X POST -H "Authorization: {session_id}" \
-H "Content-Type: application/x-www-form-urlencoded" \
-d 'q=select id, name__v from documents' \
"https://myvault.veevavault.com/api/v20.3/query"
{
"responseStatus": "SUCCESS",
"responseDetails": {
"pagesize": 1000,
"pageoffset": 0,
"size": 96,
"total": 96
},
"data": [
{
"id": 119,
"name__v": "Test File 1"
},
{
"id": 1,
"name__v": "Test File 2"
}
]
}
For detailed information about VQL syntax, how to structure queries, and how to retrieve fields from a single object (documents, product, etc.), see Query Syntax & Structure.
VQL supports relationship queries (joins) where more than one object is included in a single query. This is covered in Relationship Queries and Many-to-Many Relationship Queries.
This tutorial covers the basics of structuring and submitting a query and how to use VQL to search document fields and document content. VQL’s syntax is similar to SQL and provides a programmatic way of searching your vault’s data.
To query a specific field or to use it in a WHERE clause filter statement, the field must be queryable. The component’s metadata API can tell you which fields are queryable. All "queryable" metadata field is set to True for all queryable fields.
For example, you may want to create a query on the id field. To determine if this field is queryable, we need to call the metadata API as shown in the example below:
$ curl -X GET -H "Authorization: {SESSION_ID}" \
https://myvault.veevavault.com/api/20.3/metadata/objects/documents/properties
{
"responseStatus": "SUCCESS",
"properties": [
{
"name": "id",
"type": "id",
"required": true,
"maxLength": 20,
"minValue": 0,
"maxValue": 9223372036854775807,
"repeating": false,
"systemAttribute": true,
"editable": false,
"setOnCreateOnly": true,
"disabled": false,
"hidden": true,
"queryable": true
},
{
"name": "version_id",
"scope": "DocumentVersion",
"type": "id",
"required": true,
"maxLength": 20,
"minValue": 0,
"maxValue": 9223372036854775807,
"repeating": false,
"systemAttribute": true,
"editable": false,
"setOnCreateOnly": true,
"disabled": false,
"hidden": true,
"queryable": true
}
]
}
Let’s query the id and name__v fields of our documents to see how this works.
This request uses the name property of the document field (name__v), not the label (label__v). You can get the name property via the metadata API.
curl -X POST -H "Authorization: {session_id}" \
-H "Content-Type: application/x-www-form-urlencoded" \
-d 'q=select id, name__v from documents' \
"https://myvault.veevavault.com/api/20.3/query"
{
"responseStatus": "SUCCESS",
"responseDetails": {
"pagesize": 1000,
"pageoffset": 0,
"size": 96,
"total": 96
},
"data": [
{
"id": 1,
"name__v": "Cholecap Brochure"
},
{
"id": 2,
"name__v": "New VeevaRX Logo"
}
]
}
This section looks at some basic keyword searches on documents and explores the ALLVERSIONS and LATESTVERSION search options.
As a document moves through its lifecycle and versions, its source file and field values will change. By default, queries only return the latest version of a document. Searching previous versions requires additional syntax.
Using the ALLVERSIONS and LATESTVERSION syntax, you could find:
The examples in this section use a document with the following versions:
| Version | State | Keywords in Content | Version Description |
|---|---|---|---|
| 0.1 | Draft | Insulin | Added “Insulin” |
| 0.2 | Draft | Insulin, Contraindications | Added “Contraindications” |
| 1.0 | Approved | Insulin, Dosage | Removed “Contraindications”; Added “Dosage”; this is a past steady state version |
| 1.1 | Draft | Insulin, Dosage | No change |
| 2.0 | Approved | Insulin, Prescribing | Removed “Dosage”; Added “Prescribing”; this is the latest steady state version |
| 2.1 | Draft | Insulin, Prescribing | No change |
| 2.2 | Draft | Insulin, Formulary | Removed “Prescribing”; Added “Formulary”; this is the latest version |
First, we’ll query the WonderDrug Information document using the WHERE filter on the document name:
SELECT id, minor_version_number__v, major_version_number__v
FROM documents
WHERE name__v = 'WonderDrug Information'
{
"responseStatus": "SUCCESS",
"responseDetails": {
"pagesize": 1000,
"pageoffset": 0,
"size": 1,
"total": 1
},
"data": [
{
"id": 534,
"minor_version_number__v": 2,
"major_version_number__v": 2
}
]
}
This response returns the latest version of the document, version 2.2.
Next, we’ll use the FIND operator to search for documents with the keywords “Contraindications”, “Dosage”, or “Prescribing” in the document content. We have not specified a version option, so this search only includes the latest document versions. This query uses SCOPE, which indicates whether you want to search the full text of the document’s rendition (content), the document’s field values (properties), or both (all).
SELECT id, minor_version_number__v, major_version_number__v
FROM documents
FIND ('Contraindications OR Dosage OR Prescribing' SCOPE content)
{
"responseStatus": "SUCCESS",
"responseDetails": {
"find": "('Contraindications OR Dosage OR Prescribing' SCOPE content)",
"pagesize": 1000,
"pageoffset": 0,
"size": 0,
"total": 0
},
"data": []
}
The query returns no results because “Contraindications” was removed from the document content when it changed from version 0.2 to 1.0, “Dosage” was removed when it changed from version 1.1 to 2.0, and “Prescribing” was removed when it changed from version 2.1 to 2.2. For most document searches, only the latest version is relevant, so this result is what you’d want to see.
Occasionally, you may want to include past versions in your document search. To do this, we’ll add ALLVERSIONS to the FROM clause.
SELECT id, minor_version_number__v, major_version_number__v
FROM ALLVERSIONS documents
FIND ('Dosage' SCOPE content)
{
"responseStatus": "SUCCESS",
"responseDetails": {
"find": "('Dosage' SCOPE content)",
"pagesize": 1000,
"pageoffset": 0,
"size": 2,
"total": 2
},
"data": [
{
"id": 534,
"minor_version_number__v": 1,
"major_version_number__v": 1
},
{
"id": 534,
"minor_version_number__v": 0,
"major_version_number__v": 1
}
]
}
The query found our keyword in versions 1.1 and 1.0. If we queried “Insulin” (present in all seven versions), the response would look similar to the one above, but with a result for each document version.
Sometimes, you want to find only the latest version that meets your criteria. To do this, we place LATESTVERSION in the SELECT clause and (as above) ALLVERSIONS in the FROM clause:
SELECT LATESTVERSION id, minor_version_number__v, major_version_number__v
FROM ALLVERSIONS documents
FIND ('Dosage' SCOPE content)
{
"responseStatus": "SUCCESS",
"responseDetails": {
"find": "('Dosage' SCOPE content)",
"pagesize": 1000,
"pageoffset": 0,
"size": 1,
"total": 1
},
"data": [
{
"id": 534,
"minor_version_number__v": 1,
"major_version_number__v": 1
}
]
}
This query found the latest version (1.1) in which our keyword exists. Contrast this with the previous result, which found our keyword in multiple versions. Using LATESTVERSION is most useful when the keyword exists in many different versions and you only want the latest.
Searching document states is another way to search across document versions. The following examples use the same document as the previous examples.
As of v9.0, document state parameters use the following format:
SELECT fields FROM documents WHERE status__v = steadystate()
SELECT fields FROM documents WHERE status__v = supersededstate()
SELECT fields FROM documents WHERE status__v = obsoletestate()
This query searches all document versions for those with “Insulin” in the content, then filters to find the latest steady state version.
SELECT LATESTVERSION id, minor_version_number__v, major_version_number__v
FROM ALLVERSIONS documents
FIND ('Insulin' SCOPE content)
WHERE status__v = steadystate()
{
"responseStatus": "SUCCESS",
"responseDetails": {
"find": "('Insulin' SCOPE content)",
"pagesize": 1000,
"pageoffset": 0,
"size": 1,
"total": 1
},
"data": [
{
"id": 534,
"minor_version_number__v": 0,
"major_version_number__v": 2
}
]
}
The query found our keyword “Insulin” in version 2.0 of the document. The keyword exists in all versions of the document, including both steady state versions (1.0 and 2.0), but the query returned only 2.0 because of the LATESTVERSION syntax.
If we performed the same query as above, substituting “Insulin” for:
LATESTVERSION from the SELECT clause and keeping ALLVERSIONS in the FROM clause would find version 1.0.This query searches all document versions for those with “Insulin” in the content, then filters to find the latest superseded state version.
SELECT LATESTVERSION id, minor_version_number__v, major_version_number__v
FROM ALLVERSIONS documents
FIND ('Insulin' SCOPE content)
WHERE status__v = supersededstate()
{
"responseStatus": "SUCCESS",
"responseDetails": {
"find": "('Insulin' SCOPE content)",
"pagesize": 1000,
"pageoffset": 0,
"size": 1,
"total": 1
},
"data": [
{
"id": 534,
"minor_version_number__v": 0,
"major_version_number__v": 1
}
]
}
The query found our keyword “Insulin” in version 1.0 of the document. Version 1.0 was the latest steady state version until it was superseded by version 2.0. We’d get the same result for the keyword “Dosage”, but no result would be returned for any of the other keywords.
The examples above use a document that is actively progressing through Draft and Approved states. For the next example, the document has been retired from use and it has moved to Obsolete state. When this occurs, all versions of the document also become obsolete.
SELECT id, minor_version_number__v, major_version_number__v
FROM ALLVERSIONS documents
FIND ('Insulin' SCOPE content)
WHERE status__v = obsoletestate()
{
"responseStatus": "SUCCESS",
"responseDetails": {
"find": "('Insulin' SCOPE content)",
"pagesize": 1000,
"pageoffset": 0,
"size": 7,
"total": 7
},
"data": [
{
"id": 534,
"minor_version_number__v": 1,
"major_version_number__v": 2
},
{
"id": 534,
"minor_version_number__v": 0,
"major_version_number__v": 2
},
{
"id": 534,
"minor_version_number__v": 1,
"major_version_number__v": 1
},
{
"id": 534,
"minor_version_number__v": 2,
"major_version_number__v": 0
},
{
"id": 534,
"minor_version_number__v": 0,
"major_version_number__v": 1
},
{
"id": 534,
"minor_version_number__v": 1,
"major_version_number__v": 0
},
{
"id": 534,
"minor_version_number__v": 2,
"major_version_number__v": 2
}
]
}
Let’s query the same document to retrieve its current status:
SELECT id, name__v, status__v
FROM documents
WHERE id = 534
{
"responseStatus": "SUCCESS",
"responseDetails": {
"pagesize": 1000,
"pageoffset": 0,
"size": 1,
"total": 1
},
"data": [
{
"id": 534,
"name__v": "WonderDrug Information",
"status__v": "Obsolete"
}
]
}
Archiving is a way to hide old documents that are no longer relevant in day-to-day operations without removing them from your vault. Archiving a document archives all of its versions. Archived documents are excluded from search results by default, but you can choose to search your vault’s archive by adding archived_documents to the FROM clause.
Note: This functionality is available in v15.0 and later. Document archive is not available in all vaults. Learn more in Vault Help.
To find all archived documents:
SELECT id, name__v
FROM archived_documents
Like other document queries, searching archived documents defaults to LATESTVERSIONS.
Organizations frequently use Expected Document Lists (EDLs) to measure the completeness of projects like clinical studies by linking documents to EDL Item records based on matching field values. You can search Vault for matched documents by adding matched_documents to the FROM clause. Learn more about EDLs in Vault Help.
Note: This metadata is not retrievable via the standard metadata API.
The following fields are queryable for the matched_documents object:
| Name | Description |
|---|---|
id | The id of the matched_documents record. |
edl_item_id__v | The id of the EDL Item record linked to the document. |
matching_doc_id__v | The document id. |
major_version__v | The document’s major version number. |
minor_version__v | The document’s minor version number. |
created_date__v | The date the document was created. |
created_by__v | The id of the user who created the document. |
modified_date__v | The date the document was last modified. |
modified_by__v | The id of the user who last modified the document. |
include_in_total__v | When set to true, indicates that the document should be applied to the matching document count for the EDL Item. |
version_is_locked__v | When set to true, confirms that Vault has locked the document version to the EDL Item. |
source__v | Indicates if the document was matched by user or auto. |
To fetch all matched documents:
SELECT id, edl_item_id__v, matching_doc_id__v, major_version__v, minor_version__v, created_date__v, created_by__v, modified_date__v, modified_by__v, include_in_total__v, version_is_locked__v, source__v
FROM matched_documents
The following standards and specifications apply to Vault queries.
SELECT, FROM, and WHERE in upper or lowercase.name__v, documents, and product__v in lower-case.id field name on documents (only), which may be entered as id or ID.name__v = 'cholecap' (where name__v is a String field type) returns no results if the field value is Cholecap.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, you can use the caseinsensitive({FIELD_NAME__V}) to bypass case-sensitivity. For example: caseinsensitive(name__v) = 'cholecap' returns results even if the value is Cholecap. Learn more about performing case-insensitive queries.
SELECT and WHERE clauses.'2016-01-16'.'2016-01-15T07:30:00.000Z'..000Z UTC expression. The zeros may be any number.The maximum length of a VQL statement is 50,000 characters.
Vault supports the PAGESIZE and PAGEOFFSET parameters to limit the number of results displayed per page and paginate results.
GET request to retrieve the data.next_page and previous_page endpoints only remain active for 20 minutes following query submission. After that, the original query must be resubmitted.workflows(legacy), events, or users, the response does not include the next_page and previous_page URL endpoints and displays all results on one page.next_page and previous_page URLs, submit additional queries using PAGEOFFSET to paginate over the result set. Learn more about paginating results with PAGEOFFSET.ORDER BY operator.FIND operator search phrase using the ORDER BY rank operator. Learn more about Order By rank.application/json) is the default response format.Accept to application/xml.To retrieve all objects in your vault, use the Retrieve Object Collection REST API endpoint. To retrieve metadata for a specific object, including fields, use the Retrieve Object Metadata REST API endpoint.
Vault Objects are divided into the following categories:
product__v, country__v, study__v, site__v, and others.__v. For example, to query the Product object, use product__v.user__sys, rendition_type__sys, group__sys, and others.__sys. For example, to query Groups, use group__sys.Note: The following system objects are versioned:
user__sys: Available in v18.1+binders: Available in v18.2+group__sys: Available in v18.3+doc_role__sys: Available in v21.1+__c. For example, to query a “custom object”, enter custom_object__c.accountmessage__sys, signaturepage__sys, objecttype__sys, and others.documents, users, roles, groups, workflows(legacy), lifecycles, events, and relationships.documents.Note: When querying users across vaults, Vault uses the private key values (external, readOnly, and full) for license_type__v. For example, the following query uses the external private key value instead of external__v:
SELECT user_first_name__v, user_last_name__v, license_type__v
FROM users
WHERE license_type__v = 'external'
Some objects aren’t available for query. As a best practice, always query your vault to obtain the current collection of objects, object records, and object fields.
The following objects are currently not queryable:
roleslifecycle__vpicklistssecuritypoliciesNot all fields on object targets 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: /metadata/objects/documents/properties
See the Retrieve All Document Fields API.
Document & Binder Relationships: /metadata/objects/documents/types/{document_type}/relationships
See the Retrieve Document Type Relationships API.
Document Events: /metadata/objects/documents/events/{event_type}/events/{event_subtype}
See the Retrieve Document Event SubType Metadata API.
Users: /metadata/objects/users
See the Retrieve User Metadata API.
As of v17.1, the LongText field type allows users to enter text Strings up to 32,000 characters. While a FIND clause always searches 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.
SELECT id, LONGTEXT(long_text_field__c)
FROM object__c
As of v21.1, the RichText field type allows users to enter text Strings up to 32,000 plaintext characters, with an additional 32,000 characters reserved for HTML markup. For example, <b>Hello</b> is 5 plaintext characters and 7 HTML markup characters.
Vault does not support all HTML tags and attributes in Rich Text fields. Learn more about supported HTML for Rich Text in the API documentation.
Using FIND on a Rich Text field only searches the text, not the HTML markup. While a FIND clause does search the entire text without markup, other queries will only return the first 250 characters of a Rich Text field by default. To return all of the characters, use the RICHTEXT() function. Learn more about reading Rich Text values.
Note that VQL only supports SELECT clauses with RICHTEXT() function.
SELECT id, RICHTEXT(rich_text_field__c)
FROM object__c
You can read Rich Text values in the following ways:
LONGTEXT(): returns the full Rich Text value without HTML markup.RICHTEXT(): returns the full Rich Text value with HTML markup.Note that all retrieval methods include newline characters.
Using the following Rich Text field as an example:
The following VQL query will retrieve the value of this field in three different ways:
SELECT name__v,
rich_text_field__c,
RICHTEXT(rich_text_field__c) AS RichTextFunction,
LONGTEXT(rich_text_field__c) AS LongTextOnRich
FROM campaign__c
WHERE name__v='Veepharm Marketing Campaign'
This query returns the following:
{
"data": [
{
"name__v": "Veepharm Marketing Campaign",
"rich_text_field__c": "A two-hour reduction in sleep per night for one week is associated with a significant reduction in psychomotor performance.\n\nGet a good night's sleep with Veepharm, clinically proven to help you fall asleep faster and stay asleep longer. Ask your",
"RichTextFunction": "<blockquote><p style=\"text-align: left;\">A two-hour reduction in sleep per night for one week is associated with a significant reduction in psychomotor performance.</p></blockquote><p><br></p><p>Get a good night's sleep with <b>Veepharm</b>, clinically proven to help you fall asleep faster and stay asleep longer. Ask your doctor if <b>Veepharm </b>is right for you.</p>",
"LongTextOnRich": "A two-hour reduction in sleep per night for one week is associated with a significant reduction in psychomotor performance.\n\nGet a good night's sleep with Veepharm, clinically proven to help you fall asleep faster and stay asleep longer. Ask your doctor if Veepharm is right for you."
}
]
}
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 finds no results:
SELECT id
FROM longtext_obj__c FIND('Vault*Language' SCOPE long_text_field__c)
A formula field calculates the field-value based on a formula entered during field configuration. VQL allows you to query formula fields for custom and standard objects.
Note: Querying a non-object formula field returns an error.
Formula fields are not searchable and are not stored, and thus cannot be used with FIND, ORDER BY, and WHERE clauses. Since the formulas are evaluated during runtime, if there is an error calculating the formula, null is returned for the field value.
With the currency field type, users can configure currency fields on a Vault object. In addition to primary currency, Vault supports a corporate currency.
When querying currency fields, Vault always includes trailing decimal places. For example, if a USD currency field value was entered by a user as $10, VQL returns $10.00 as a String. The number of trailing decimal places depends on the currency.
To retrieve corporate currency fields, you must use <field name>_corp__sys to retrieve the corporate currency numeric value.
For example, the following query returns a result with the numeric value of the market_value_corp__sys field with for a list of active products.
SELECT name__v, market_value_corp__sys
FROM product__v
WHERE status__v = 'active__v'
In VQL versions v21.2+, VQL displays number fields based on the field’s configured Decimal places. If a user enters 10.00 and the number field is configured with Decimal places of 1, VQL returns 10.0. Likewise, if Decimal places is 0, VQL returns 10. If Decimal places is 9, VQL returns 10.000000000, and so on.
Decimal places are configured by your Vault Admin, and can be different for each unique field. For example, the same document can have multiple number fields each with a different configuration for Decimal places.
In previous versions of VQL, Number fields have slightly different behavior when displaying decimal places, depending both on your VQL version and the type of data you’re querying.
For document queries, VQL displays number fields exactly as the user entered them. If a user enters 10.00, VQL returns 10.00.
For object record queries, VQL displays number fields based on the field’s configured Decimal places. If a user enters 10.00 and the number field is configured with Decimal places of 1, VQL returns 10.0. Likewise, if Decimal places is 0, VQL returns 10. if Decimal places is 9, VQL returns 10.000000000, and so on.
VQL displays number fields exactly as the user entered them. If a user enters 10.00, VQL returns 10.00.
VQL truncates trailing zeros on number fields. If a user enters 10.00, VQL returns 10.
The basic structure of a VQL query always includes a SELECT statement followed by one or more optional clauses or filters including WHERE, ORDER BY and FIND.
VQL SELECT statements use the following syntax:
SELECT {field}
FROM {object}
WHERE {condition}
FIND ({keywords})
ORDER BY {field} ASC|DESC
PAGESIZE {number}
PAGEOFFSET {number}
AS {alias}
| Name | Description |
|---|---|
| SELECT | Select one or more fields to return from a specified object. |
| FROM | Specifies the object from which to return the fields in the SELECT statement. |
| WHERE | Use the WHERE clause to apply search filters and narrow results. |
| FIND | Use FIND to search document or vault object fields for specific keywords. |
| ORDER BY | Sets the sort order of query results. |
| PAGESIZE | Limits the number of query results per page. |
| PAGEOFFSET | When the query results found exceeds the number displayed per page, use PAGEOFFSET to display results in multiple pages. |
| AS | Define an optional alias when using a function on a field in the SELECT statement. Not all fields support aliases. |
Several escape sequences for special characters are supported on all document fields, object fields, and other VQL endpoints.
| Name | Description | Behavior When Unescaped |
|---|---|---|
Backslash (\) | \\ | Returns error unless used in escape sequence |
Single quote (‘) | \’ or ‘’ (two single quotes) | Creates query string |
Double quote (“) | \” | Creates exact match query in FIND clause and literal “ elsewhere |
Percent sign (%) | \% | Acts as wildcard in LIKE clause and literal % elsewhere |
Asterisk (*) | \* | Acts as wildcard in FIND clause and literal * elsewhere |
| Line feed | \n | Literal n |
| Tab | \t | Literal t |
| Carriage return | \r | Literal r |
SELECT and FROM work together to form the basis of all queries and can be used entirely on their own.
SELECT {fields} FROM {object}
The following query returns the IDs, names, and status from all documents.
SELECT id, name__v, status__v
FROM documents
{
"responseStatus": "SUCCESS",
"responseDetails": {
"pagesize": 1000,
"pageoffset": 0,
"size": 54,
"total": 54
},
"data": [
{
"id": 68,
"name__v": "Cholecap Akathisia Temporally associated with Adult Major Depressive Disorder",
"status__v": "Draft"
},
{
"id": 65,
"name__v": "Gludacta Package Brochure",
"status__v": "Approved"
},
{
"id": 64,
"name__v": "Gludacta Logo Light",
"status__v": "Approved"
}
]
}
Use the WHERE clause in VQL as a search filter to narrow and retrieve results that meet a specified condition.
WHERE filter can include a variety of “operators”, allowing you to further refine query results. Learn more about operators below.SELECT can also be used in WHERE.SELECT {fields} WHERE {field}{operator}{value}
The following query returns a list of documents of the Commercial Content document type.
SELECT id, name__v, status__v
FROM documents
WHERE type__v = 'Commercial Content'
{
"responseStatus": "SUCCESS",
"responseDetails": {
"pagesize": 1000,
"pageoffset": 0,
"size": 6,
"total": 6
},
"data": [
{
"id": 68,
"name__v": "Cholecap Akathisia Temporally associated with Adult Major Depressive Disorder",
"status__v": "Draft"
},
{
"id": 65,
"name__v": "Gludacta Package Brochure",
"status__v": "Approved"
},
{
"id": 64,
"name__v": "Gludacta Logo Light",
"status__v": "Approved"
},
{
"id": 63,
"name__v": "Gludacta Logo Dark",
"status__v": "Approved"
}
]
}
All comparison operators are available in API v1.0+. You can use the following operators in the WHERE clause:
| Operator | Syntax | Description |
|---|---|---|
= | SELECT {fields} FROM {object} WHERE {field} = {'value'} | Field values equal to the specified value. |
!= | SELECT {fields} FROM {object} WHERE {field} != {'value'} | Field values not equal to the specified value. |
< | SELECT {fields} FROM {object} WHERE {field} < {'value'} | Field values are less than the specified value. |
> | SELECT {fields} FROM {object} WHERE {field} > {'value'} | Field values are greater than the specified value. |
<= | SELECT {fields} FROM {object} WHERE {field} <= {'value'} | Field values are less than or equal to the specified value. Not supported when querying workflows(legacy). |
>= | SELECT {fields} FROM {object} WHERE {field} >= {'value'} | Field values are greater than or equal to the specified value. Not supported when querying workflows(legacy). |
SELECT id, user_name__v, security_profile__v
FROM users
WHERE user_locale__v = 'es_US'
SELECT label__sys, due_date__sys
FROM active_workflow_task__sys
WHERE status__sys != 'available__sys'
SELECT id, document_number__v
FROM documents
WHERE document_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, document_number__v
FROM documents
WHERE version_modified_date__v <= '2016-04-23T07:30:00.000Z'
When querying documents or Vault objects, you can use the following operators in the WHERE filter and FIND operator.
| Operator | Syntax | Description |
|---|---|---|
AND | SELECT {fields} FROM {object} WHERE {field_1} = {'value_1'} AND {field_2} = {'value_2'} | Field values are equal to both specified values. |
OR | SELECT {fields} FROM {object} WHERE {field_1} = {'value_1'} OR {field_2} = {'value_2'} | Field values are equal to either specified value. VQL does not support using the OR operator between different query objects in a WHERE clause, or with Static Reference Constraints for documents. |
CONTAINS | SELECT {fields} FROM {object} WHERE {field} CONTAINS ({'value_1'},{'value_2'},{'value_3'}) | Field values are equal to any of the specified values. |
BETWEEN | SELECT {fields} FROM {object} BETWEEN {'value_1'} AND {'value_2'} | Used with AND to compare data between two different values. |
LIKE | SELECT {fields} FROM {object} WHERE {field_1} LIKE {'%wildcard'} | Used with the wildcard character % to search for matching field values when you don’t know the entire value. |
IN | SELECT {fields} FROM {object} WHERE {field} IN (SELECT {'field'} FROM {object} | The IN operator can only be used for inner join relationship queries on documents and objects. |
The AND operator returns results when both values meet the conditions. The following query returns Approved documents of the Reference Document type.
SELECT id, name__v
FROM documents
WHERE type__v = 'Reference Document' AND status__v = 'Approved'
The OR operator returns results when any of the values meet the conditions. Note that VQL does not support using the OR operator between different query objects in a WHERE clause, or with Static Reference Constraints for documents.
The following query includes documents with a version creation OR modified date after the date of 04-23-2018.
SELECT id, name__v
FROM documents
WHERE version_creation_date__v > '2018-04-23' OR version_modified_date__v > '2018-04-23'
Use the CONTAINS operator to enclose multiple values in parentheses. This uses the OR operator logic. The following query returns documents with English OR Spanish OR French set on the language field.
SELECT id, name__v FROM documents
WHERE language__v
CONTAINS ('English', 'Spanish', 'French')
Note: The CONTAINS operator does not support workflows(legacy) queries.
Use BETWEEN operator with AND to compare data between two different values. The following query returns the documents created between the dates of ‘2018-11-01’ AND '2018-12-01’.
SELECT id, name__v
FROM documents
WHERE document_creation_date__v BETWEEN '2018-11-01' AND '2018-12-01'
The LIKE operator is used with the wildcard character % to search for matching field values when you don’t know the entire value.
The following query returns documents where the name__v value starts with “N”. In this example, ’%25’ is URL encoded ’%’ character.
SELECT id, name__v
FROM documents
WHERE name__v LIKE 'N%25'
VQL does not support LIKE that starts with a wildcard (%). For example, name__v LIKE '%_DOC' will not work. If the wildcard is in the middle or the end of the LIKE, it works as expected. For example, name__v LIKE 'DOC%'. All other clauses besides LIKE work as expected with leading wildcards.
Use the IN operator to test whether or not a value (stated before the IN operator) is “in” the list of values provided after the IN operator. The following query returns the id for all products referenced by a document.
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.
Values for Date and DateTime field types must be entered in UTC (Coordinated Universal Time) and not the user’s time zone. For example:
'2016-04-23').'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, the following query 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”.
SELECT id, name__v
FROM documents
WHERE document_creation_date__v > '2015-11-01'
By default, all field values are case-sensitive. This applies to all field types. For example:
name__v = 'cholecap' (where name__v is a String field type) returns no results if the field value is Cholecap.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. The following example returns results even if the field value is “Cholecap”.
SELECT id
FROM product__v
WHERE caseinsensitive(name__v) = 'cholecap'
Note the following scope and limitations:
WHERE clause. It is not supported in the SELECT clause.WHERE clause of sub-select statements (relationship queries).You can filter object records by state type by using the STATETYPE() function with the WHERE clause. Learn more about Object Record State Types in Vault Help.
The STATETYPE() function is available in v19.3+.
| Name | Syntax | Description |
|---|---|---|
STATETYPE() | SELECT {fields} FROM {object} WHERE state__v = STATETYPE('{state type name}') | Retrieve fields from all object records for a specific object in a specific State Type. STATETYPE() can only be used in filters (the WHERE clause) and can only be applied to the state__v field. |
SELECT id
FROM product__v
WHERE state__v = STATETYPE('complete_state__sys')
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 (blank) 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
Note that Vault does not consider fields with NULL values in an inequality. For example, the following query does not return any documents where the country__v field is null:
SELECT id, name__v
FROM documents
WHERE country__v != Canada
Use FIND to perform keyword searches on documents & Vault objects. When using FIND on documents, Vault searches all queryable document fields. All FIND statements must be enclosed in parenthesis.
FIND for documents is available in API version v8.0+FIND for standard volume Vault objects is available in API v14.0+FIND is not supported for high volume Vault objectsNote: 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.
A simple search is any FIND string that does not contain operators. Beginning in v20.2, simple searches use Strict Matching by default to return the same results as a basic search in the Vault UI. However, when Strict Matching is disabled, Vault places an implicit OR operator between each keyword, so results can be different than expected. Learn more about Strict Matching in Vault Help.
In most cases, Strict Matching is disabled when there is at least one operator in the search string. See Operator Exceptions for details.
A multi-scope query can have one scope that is simple and another that is not. For example:
FIND ('diabetes AND insulin OR Nyaxa' SCOPE Properties OR 'cholesterol Cholecap' SCOPE CONTENT)
In this case, Vault will apply Strict Matching to the CONTENT scope, but not to the PROPERTIES scope.
If all terms in a search string are joined by the AND operator, Vault requires all of the terms to match. For example, the following query would return results containing both ‘diabetes’ and ‘insulin,’ but would not include results containing only one of the terms.
FIND ('diabetes AND insulin')
Using NOT outside of the search string negates the entire expression with Strict Matching applied. For example, the following query returns results containing ‘pain’ and ‘medication’, but not ‘pain medication.’
FIND (NOT 'pain medication')
SELECT {fields} FROM documents FIND ({'keywords'}) SELECT {fields} FROM {Vault object} FIND ({'keywords'})
Document and object names and field names must be lower-case. The search phrase is not case-sensitive.
When using FIND on documents, all queryable document fields are searched.
SELECT id, name__v
FROM documents
FIND ('insulin')
{
"responseStatus": "SUCCESS",
"responseDetails": {
"find": "('insulin')",
"pagesize": 1000,
"pageoffset": 0,
"size": 3,
"total": 3
},
"data": [
{
"id": 200,
"name__v": "Test Doc Nyaxa Insulin"
},
{
"id": 198,
"name__v": "Test Doc Diabetes Insulin"
},
{
"id": 197,
"name__v": "Test Doc Nyaxa Diabetes Insulin"
}
]
}
SELECT id, name__v, status__v
FROM product__v
FIND ('phosphate')
{
"responseStatus": "SUCCESS",
"responseDetails": {
"find": "('phosphate')",
"pagesize": 1000,
"pageoffset": 0,
"size": 1,
"total": 1
},
"data": [
{
"id": "00P000000000205",
"name__v": "Nyaxa",
"status__v": [
"active__v"
]
}
]
}
To search document fields containing any keywords, use the OR operator between each. For example:
SELECT id, name__v
FROM documents
FIND ('insulin OR diabetes')
This is equivalent to typing ‘insulin diabetes’ into the Any of these words field in Advanced Search in the Vault UI.
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 AND Nyaxa')
This is equivalent to typing ‘diabetes insulin Nyaxa’ into the All of these words field in Advanced Search in the Vault UI.
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"')
Use SCOPE CONTENT with FIND to search within document content. SCOPE CONTENT is available in v8.0+.
SELECT {fields} FROM documents FIND ({'keywords'} SCOPE CONTENT)
The following statement would return documents with the keyword “insulin” within the content.
SELECT id, name__v
FROM documents
FIND ('insulin' SCOPE CONTENT)
Use SCOPE ALL with FIND to search document fields and within document content. SCOPE ALL is available in v8.0+.
SELECT {fields} FROM documents FIND ({'keywords'} SCOPE ALL)
The example query below searches document content and all queryable fields.
SELECT id, name__v
FROM documents
FIND ('insulin' SCOPE ALL)
Use SCOPE to search specific document or Vault object fields. SCOPE is available in v15.0+.
Vault does not support the following document fields for SCOPE:
type__vsubtype__vclassification__vlifecycle__vstatus__vfilename__vSELECT {fields} FROM documents FIND ({'keywords'} SCOPE {field})
The following statement searches within a specific document field. Note that you can only include one document field.
SELECT id, name__v
FROM documents
FIND ('insulin' SCOPE name__v)
The following statement searches within a specific object field:
SELECT id, name__v
FROM product__v
FIND ('phosphate' SCOPE generic_name__vs)
The following statement returns all documents where the product field is set to “cholecap” and the document content also contains “prescribing” or “information”.
SELECT id, name__v
FROM documents
FIND ('cholecap' SCOPE product__v AND 'prescribing information' SCOPE CONTENT)
When using FIND and/or SCOPE, you can use the WHERE filter to narrow results. WHERE must be placed after FIND and SCOPE.
SELECT id, name__v
FROM product__v
FIND ('phosphate' SCOPE generic_name__vs)
WHERE therapeutic_area__vs = 'cardiology__vs'
Use NOT with FIND to exclude results from a query. The following query searches for all documents that do not contain the word 'insulin’:
SELECT id, name__v
FROM documents
FIND (NOT 'insulin')
Vault uses stemming to return results for metadata (but not content) searches that include inflections of the search term:
To return only the exact search term, for example, “large” but not “larger” or “largest”, use an exact match query.
Stemming is available for English, French, and German fields.
When searching documents and objects using the FIND operator, use the wildcard character * to find partial matches.
This query searches documents containing words starting with 'ins’, 'dia’, 'glu’:
SELECT id, name__v
FROM documents
FIND ('ins* dia* glu*')
You can place the wildcard character in any part of the keyword, however, we strongly discourage adding wildcards at the beginning of search terms due to negative effects on performance and relevance.
FIND ('bio*') returns documents or objects with words beginning with 'bio’, e.g., biology, biodiversity, etc.FIND ('o*ology') returns documents or objects with words starting with 'o’ and ending with 'ology’, e.g., oncology, ophthalmology, etc.Vault automatically adds a wildcard to the end of single search terms that do not match the ID pattern, as well as to the end of the last search term for SCOPE CONTENT queries.
Vault applies special handling to single search terms that either have punctuation in the middle of the term or are a combination of characters and digits. ID pattern describes the purpose of the special handling but does not comprehensively describe all the searches that qualify. For example, a search for ‘10mg’ is clearly not an ID but matches the ID pattern and receives the ID pattern handling.
For searches that fit the ID pattern, all tokens of the ID must match to be included in search results. Vault applies additional handling to document numbers to ensure the desired document is the first search result. For example, a search for VV-123-456 would return VV-123-456 and VV-456-123, but special handling ensures that VV-123-456 appears first in the search results. Additionally, documents that only include VV are not included.
When searching documents and objects using the FIND operator, Vault also returns documents or objects containing synonyms if a thesaurus exists in your vault. Vault does not search for synonyms if a query contains:
At least one NOT operator. For example, FIND (NOT ‘doctor’ scope content) will exclude documents or objects that contain the word doctor, but not documents or objects that contain synonyms for doctor.
At least one AND operator. For example, FIND (‘doctor AND physician’ scope content) will find documents or objects containing the words doctor and physician, but not their synonyms.
An exact keyword search. For example, FIND (‘“doctor”’ scope content) will match documents or objects containing the word doctor, but not its synonyms.
A wildcard. For example, FIND (‘doctor*’ scope content) will match documents or objects containing the word doctors, but not its synonyms.
Beginning in v20.2, Vault automatically tokenizes CamelCase, alphanumeric, and delimited strings when performing keyword searches on document and object fields. When performing keyword searches on content, Vault only tokenizes delimited strings.
To enable tokenization in API versions 9.0 - 20.1, set the tokenize request parameter to true. As of API v20.2, Vault ignores the tokenize parameter. Learn more about search term tokenization in Vault Help.
SELECT id, name__v
FROM documents
FIND ('CamelCase') "https://myvault.veevavault.com/api/v15.0/query?tokenize=true"
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"')
Use ORDER BY to control the order of query results. You can specify to sort results in either an ascending (ASC) order or descending order (DESC). ORDER BY is available in v8.0+.
SELECT {fields} FROM documents ORDER BY {field} ASC|DESC
This following query returns document IDs in ascending order (1, 2, 3, etc.)
SELECT id, name__v
FROM documents
ORDER BY id ASC
{
"responseStatus": "SUCCESS",
"responseDetails": {
"pagesize": 1000,
"pageoffset": 0,
"size": 54,
"total": 54
},
"data": [
{
"id": 1,
"name__v": "Binders v10 Video"
},
{
"id": 2,
"name__v": "PowerPoints 20R3"
},
{
"id": 3,
"name__v": "Video Script Creating Tabular Reports"
}
]
}
This query returns document names in descending order (Z, Y, X, etc.)
SELECT id, name__v
FROM documents
ORDER BY name__v DESC
{
"responseStatus": "SUCCESS",
"responseDetails": {
"pagesize": 1000,
"pageoffset": 0,
"size": 54,
"total": 54
},
"data": [
{
"id": 26,
"name__v": "Ways to Get Help"
},
{
"id": 44,
"name__v": "WonderDrug Research"
},
{
"id": 4,
"name__v": "VeevaProm Information"
},
{
"id": 7,
"name__v": "Time-Release Medication"
},
]
}
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
The response includes results sorted first by type and then by name, both in descending order.
{
"responseStatus": "SUCCESS",
"responseDetails": {
"pagesize": 1000,
"pageoffset": 0,
"size": 54,
"total": 54
},
"data": [
{
"name__v": "VeevaProm Resource Doc",
"type__v": "Resource"
},
{
"name__v": "Nyaxa Resource Doc",
"type__v": "Resource"
},
{
"name__v": "CholeCap Resource Doc",
"type__v": "Resource"
}
]
}
When performing searches with FIND, you can sort the results by relevance to a search phrase using the ORDER BY rank operator. Doing so matches the default result ordering for the same search in the Vault UI.
Note ORDER BY rank is only supported for documents queries. Since the default behavior for Vault object queries is to sort by rank, including ORDER BY rank in Vault object queries results in an error.
The following query sorts the results in descending order starting with those most closely matching the search phrase.
SELECT id, name__v
FROM documents FIND ('ABC')
ORDER BY rank
{
"responseStatus": "SUCCESS",
"responseDetails": {
"pagesize": 1000,
"pageoffset": 0,
"size": 54,
"total": 54
},
"data": [
{
"id": 26,
"name__v": "Document ABC"
},
{
"id": 44,
"name__v": "Document ABCD"
},
{
"id": 4,
"name__v": "Document ABCDE"
}
]
}
Use the MAXROWS clause to fetch the maximum of N rows, applied after any filters.
SELECT {fields} FROM documents MAXROWS {number}
The following query returns 500 documents maximum.
SELECT
id
FROM documents MAXROWS 500
Use the SKIP clause to skip first N results. The results are fetched starting from the N+1’th row.
SELECT {fields} FROM documents SKIP {number}
The following query skips the first 25 results, and begins by returning result 26.
SELECT
id
FROM documents SKIP 25
Use the PAGESIZE clause to limit the number of results returned per page.
PAGESIZE does not change the total number of results found, only the number displayed per page.SELECT {fields} FROM documents PAGESIZE {number}
The following query returns 25 documents per page.
SELECT
id
FROM documents PAGESIZE 25
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. For example, 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.
When querying certain object types, the response may not include the next_page and previous_page URL endpoints and instead displays all results on one page. Learn more about paginating results.
If you’re using VQL version v20.2 or lower, you must use LIMIT instead of PAGESIZE. If both LIMIT and PAGESIZE are used in a query, PAGESIZE will overwrite LIMIT.
Deprecated as of v20.3: Instead, use PAGESIZE.
Use the LIMIT clause to limit the number of results returned per page.
LIMIT does not change the total number of results found, only the number displayed per page.SELECT {fields} FROM documents LIMIT {number}
The following query returns 25 documents per page.
SELECT
id
FROM documents LIMIT 25
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. For example, 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.
When querying certain object types, the response may not include the next_page and previous_page URL endpoints and instead displays all results on one page. Learn more about paginating results.
When the number of results found exceeds the number displayed per page, use the PAGEOFFSET clause to display the next and previous pages of results.
For example, if a query returns 500 total results and the PAGESIZE is set to display 100 results per page:
PAGESIZE 100 PAGEOFFSET 100.PAGESIZE 100 PAGEOFFSET 200.{
"responseStatus": "SUCCESS",
"responseDetails": {
"pagesize": 100,
"pageoffset": 200,
"size": 100,
"total": 500,
"previous_page": "/api/v20.3/query/c2b58293-1606-4c99-925d-b9b89e83670e?pagesize=100&pageoffset=100",
"next_page": "/api/v20.3/query/c2b58293-1606-4c99-925d-b9b89e83670e?pagesize=100&pageoffset=300"
Responses include the next_page and previous_page URL endpoints when pagination is required for most queries. Learn more about paginating results.
Note that if you’re using VQL version v20.2 or lower, you must use OFFSET instead of PAGEOFFSET. If both OFFSET and PAGEOFFSET are used in a query, PAGEOFFSET will overwrite OFFSET.
Deprecated as of v20.3: Instead, use PAGEOFFSET.
When the number of results found exceeds the number displayed per page, use the OFFSET clause to display the next and previous pages of results.
For example, if a query returns 500 total results and the LIMIT is set to display 100 results per page:
LIMIT 100 OFFSET 100.LIMIT 100 OFFSET 200.{
"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"
Responses include the next_page and previous_page URL endpoints when pagination is required for most queries. Learn more about paginating results.
In v20.3 and later (documents) and v21.1 and later (objects), use the AS clause to define an optional alias when using a function on a field in the SELECT clause. Note that aliases:
SELECT. Click below for a complete list of reserved keywords.
Aliases are scoped to the query target. Therefore, you can use the same alias name in the main query and the sub-select. Note that queries which include a field both by itself and modified by a function must use the AS clause to define an alias for the function.
SELECT id, lifecycle__v, TONAME(lifecycle__v) AS lifecycle_name
FROM documents
WHERE type__v ='Promotional'
{
"responseStatus": "SUCCESS",
"responseDetails": {
"pagesize": 1000,
"pageoffset": 0,
"size": 1,
"total": 1
},
"data": [
{
"id": 36,
"lifecycle__v": "General Lifecycle",
"lifecycle_name": "general_lifecycle__c"
}
]
}
SELECT id, packaging_text__c, LONGTEXT(packaging_text__c) AS package_fulltext
FROM product__v
WHERE name__v = 'Cholecap'
{
"responseStatus": "SUCCESS",
"responseDetails": {
"pagesize": 1000,
"pageoffset": 0,
"size": 1,
"total": 1
},
"data": [
{
"id": "cholecap",
"packaging_text__c": "Your blood cholesterol level has a lot to do with\nyour chances of getting heart disease. High blood cholesterol is one of the major risk factors for heart disease. A risk factor is a condition that increases your chance of getting a disease. In fact,",
"package_fulltext": "Your blood cholesterol level has a lot to do with\nyour chances of getting heart disease. High blood cholesterol is one of the major risk factors for heart disease. A risk factor is a condition that increases your chance of getting a disease. In fact, the higher your blood cholesterol level, the greater your risk for developing heart disease or having a heart attack. Heart disease is the number one killer of women and men in the United States. Each year, more than a million Americans have heart attacks, and about a half million people die from heart disease."
}
]
}
You can use special document functions to retrieve specific versions of documents or documents in a specific state.
DELETEDSTATE() function is available in v19.2+.| Name | Syntax | Description |
|---|---|---|
STEADYSTATE() | SELECT {fields} FROM documents WHERE status__v = STEADYSTATE() | Retrieve fields from all documents in a Steady State. |
OBSOLETESTATE() | SELECT {fields} FROM documents WHERE status__v = OBSOLETESTATE() | Retrieve fields from all documents in a Obsolete State. |
SUPERSEDEDSTATE() | SELECT {fields} FROM documents WHERE status__v = SUPERSEDEDSTATE() | Retrieve fields from all documents in a Superseded State. |
LATESTVERSION | SELECT LATESTVERSION {fields} FROM ALLVERSIONS documents | Retrieve fields from the latest version of all documents. |
DELETEDSTATE() | SELECT {fields} FROM documents WHERE status__v = DELETEDSTATE() | Retrieve fields from all documents in a Deleted State. |
SELECT id, name__v
FROM documents
WHERE status__v = STEADYSTATE()
SELECT id, name__v
FROM documents
WHERE status__v = OBSOLETESTATE()
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
WHERE STEADYSTATE() = true
SELECT LATESTVERSION id, minor_version_number__v
FROM ALLVERSIONS documents
SELECT id, name__v
FROM documents
WHERE status__v = DELETEDSTATE()
As of v20.3, the toName() function allows SELECT and WHERE clauses to use names instead of labels in queries on the following document fields:
lifecycle__v)status__v)type__v)subtype__v)classification__v)Picklist type fieldsSELECT toName(status__v)
FROM documents
WHERE toName(subtype__v) = 'resume__c'
{
"responseStatus": "SUCCESS",
"responseDetails": {
"pagesize": 1000,
"pageoffset": 0,
"size": 1,
"total": 1
},
"data": [
{
"status__v": "draft__c"
}
]
}
Note that if a field is included twice, both by itself and modified by a function, you must define an alias for the function. Learn more about using aliases.
You can use the documents and doc_role__sys objects to query document roles. This allows you to see which users and groups are assigned to certain roles on a document, as well as filter documents by the users and groups assigned to roles. Document roles are available for query in v21.1+ only.
The documents object exposes the doc_roles__sysr relationship. This is a one-to-many relationship which points to doc_role__sys child objects.
The doc_role__sys object exposes the following relationships:
| Name | Description |
|---|---|
user__sysr | This is a child relationship allowing a join with the user__sys object. |
group__sysr | This is a child relationship allowing a join with the group__sys object. |
document__sysr | This is a parent relationship allowing a join with documents. |
Note that this metadata is not retrievable via the standard metadata API.
The following fields are queryable for the doc_roles__sys object:
| Name | Description |
|---|---|
role_name__sys | The name of the role, for example reviewer__v. |
document_id | The document ID. |
user__sys | The ID of the user in the role. |
group__sys | The ID of the group in the role. |
The following are examples of standard document roles queries:
Find all roles and their assigned users and groups on a document with the document_id 627:
SELECT role_name__sys, user__sys, group__sys
FROM doc_role__sys
WHERE document_id = 627
Find documents where user 123 is in any role:
SELECT document_id, user__sys, user__sysr.username__sys, role_name__sys
FROM doc_role__sys
WHERE user__sys = '123'
Find documents with the legal reviewers group assigned the reviewer role:
SELECT document_id, role_name__sys
FROM doc_role__sys
WHERE role_name__sys = 'reviewer__v' AND group__sysr.label__v = 'Legal Reviewers'
Find the ID and name for documents where users 123 or 456 and groups 9876 or 5432 are assigned the approver role:
SELECT id, name__v
FROM documents
WHERE id IN (SELECT document_id FROM doc_roles__sysr WHERE user__sys CONTAINS (123, 456) OR group__sys CONTAINS (9876, 5432) AND role_name__sys = 'approver__v')
Find the ID, name, and owner role for documents with document_id 123 or 456:
SELECT id, name__v, (SELECT id, user__sysr.email__sys FROM doc_roles__sysr WHERE role_name__sys = 'owner__c')
FROM documents
WHERE id CONTAINS (123, 456)
You can use the binders and binder_node__sys objects to query binders for the following:
Since the binders object is an extension of the documents object, it supports the same VQL functions. Binders are available for query in v18.2+ only
The binders object exposes the binder_nodes__sysr relationship. This relationship is a “down” relationship and points to binder_nodes__sys child objects.
The binder_node__sys object exposes the following relationships:
| Name | Description |
|---|---|
binder__sysr | This relationship is a parent lookup relationship and points to the binders object. |
document__sysr | This relationship is a lookup relationship to a document at the node. This is applicable only if the node is a document (or a binder, which is a type of a document). |
child_nodes__sysr | This is a self-referencing “down” relationship pointing to binder_node__sys child objects. This is applicable only if the node is a section. |
parent_node__sysr | This is a self-referencing parent lookup relationship pointing to a binder_node__sys object at the parent node. The parent node could either be a node of type section or a null for root node. |
Note that this metadata is not retrievable via the standard metadata API.
The following fields are queryable for the binder_node__sys object:
| Name | Description |
|---|---|
id | The node ID |
name__v | The section name. This field only has value for nodes of type “section”. You should get a null back in this field for document or binder nodes. The top level node will have a name__v value because it is considered a section. |
parent_binder_id__sys | Reference to the parent binder where this node lives. |
parent_binder_major_version__sys | Reference to the parent binder where this node lives. |
parent_binder_minor_version__sys | The minor version of the parent binder |
parent_node_id__sys | Reference to parent binder_node__sys object. |
order__sys | The ordinal position of the node within its parent. |
content_id__sys | Reference to the Document or Binder. |
content_major_version__sys | The major version of the content. |
content_minor_version__sys | The minor version of the content. |
type__sys | Points to the new standard picklist binder_node_types__sys. |
created_date__v | Timestamp when the node was created. |
created_by__v | ID of a user who created the node. |
modified_date__v | Timestamp when the node was updated. |
modified_by__v | ID of a user who updated the node. |
The following are examples of standard binder queries:
Find latest steady-state versions of binders, the documents they contain, and where within the binder structure the document is contained:
SELECT LATESTVERSION id,
(SELECT parent_node_id__sys, parent_node__sysr.type__sys, parent_node__sysr.name__v, document__sysr.id, document__sysr.name__v
FROM binder_nodes__sysr)
FROM ALLVERSIONS binders
WHERE status__v = steadystate()
Find binders containing specific documents:
SELECT binder__sysr.id, binder__sysr.name__v
FROM binder_node__sys
WHERE type__sys = 'document' AND document__sysr.name__v = 'Test'
Find documents within sections named “Test Section”.
SELECT binder__sysr.id, document__sysr.id
FROM binder_node__sys
WHERE type__sys = 'document' AND parent_node__sysr.type__sys = 'section' AND parent_node__sysr.name__v = 'Test Section'
Find binders and section names containing specific documents.
SELECT binder__sysr.id, binder__sysr.name__v, parent_node__sysr.name__v, parent_node__sysr.type__sys
FROM binder_node__sys
WHERE type__sys = 'document' AND document__sysr.name__v 'Test'
You can use the renditions object to query rendition properties for a document and document versions.
The following fields are queryable for the renditions object:
| Name | Description |
|---|---|
rendition_type__sys | Public name of Renditiontype, for example, viewable_rendition__v. There is no lookup to Renditiontype metadata. |
document_id | The parent document id. |
major_version_number__sys | parent document major version |
minor_version_number__sys | parent document minor version |
size__sys | Size of unencrypted rendition file |
md5checksum__sys | MD5 checksum of unencrypted file |
filename__sys | Name of the file. |
pending__sys | Indicates if the rendition file is being processed (true) or complete (false). |
format__sys | File format of the rendition file, for example: application/vnd.openxmlformats-officedocument.wordprocessingml.document |
upload_date__sys | The upload date for the rendition. |
document_version_id | Compound document version id field. |
The following are examples of queries for document and rendition properties.
Get document and rendition properties for a collection of document versions. Note that you will need to respect VQL query size limits and break up your queries:
SELECT id, name__v,
(SELECT rendition_type__sys, md5checksum__sys, size__sys, filename__sys
FROM renditions__sysr)
FROM ALLVERSIONS documents
WHERE version_id CONTAINS ('102_0_3', '106_1_2', '107_1_0')
Get document and rendition properties for steady state version of a set of documents.
SELECT id, name__v,
(SELECT rendition_type__sys, md5checksum__sys, size__sys, filename__sys
FROM renditions__sysr)
FROM documents
WHERE status__v = steadystate() AND id CONTAINS (101,102,103)
Get document and custom rendition properties for steady state version of a set of documents.
SELECT id, name__v,
(SELECT md5checksum__sys, size__sys, filename__sys
FROM renditions__sysr
WHERE rendition_type__sys = 'my_rendition_type__c' )
FROM documents
WHERE status__v = steadystate() AND id CONTAINS (101,102,103)
Query for Renditions of a particular type.
SELECT document_id, size_v, upload_date__sys
FROM renditions
WHERE rendition_type__sys = 'imported_rendition__c'
You can use the group__sys and group_membership__sys query targets to query Vault group and user membership information. This allows you to retrieve, filter, and paginate over a large number of groups in your vault. Groups are available for query in v18.3+ only
For relationships between users and groups, both user__sys and group__sys objects have a group_membership_sysr “down” relationship that joins the user__sys and group__sys objects.
The group_membership_sys exposes the following parent relationships:
user__sysr relationship to the user__sys objectgroup__sysr relationship to the group__sys objectNote that this metadata is not retrievable via the standard metadata API.
The following fields are queryable for the group__sys object:
| Name | Description |
|---|---|
name__v | The group name. This field must be unique. |
label__v | UI label for the group. This field must be unique. |
status__v | The current state of the group (Active or Inactive). |
description__sys | The description of the group. |
system_group__sys | Specifies if the group is editable. User-managed groups will have a value of false, while system-managed groups will have a value of true. |
type__sys | Points to group_types__sys standard picklist. |
created_date__v | Timestamp when the group was created. |
created_by__v | ID of a user who created the group. |
modified_date__v | Timestamp when the group was updated. |
modified_by__v | ID of a user who updated the group. |
Note that this metadata is not retrievable via the standard metadata API.
The following fields are queryable for the group_membership__sys object:
| Name | Description |
|---|---|
id | The group membership ID. |
user_id__sys | ID of the user__sys object. |
group_id__sys | ID of the group__sys object. |
The following are examples of standard group queries:
Find all user-managed groups:
SELECT id, name__v, label__v, type__sys
FROM group__sys
WHERE type__sys = 'user_managed__v'
Find all group IDs where user with ID 123 is a member:
SELECT group__sysr.id
FROM group_membership__sys
WHERE user__sysr.id = 123
Find all active users, find user managed groups the user is a member of:
SELECT id,
(SELECT group__sysr.name__v, group__sysr.label__v
FROM group_membership__sysr
WHERE group__sysr.type__sys = 'user_managed__v')
FROM user__sys
WHERE status__v = 'active__v'
For each user managed 'Approvers’ group, find active members
SELECT id,
(SELECT user__sysr.id
FROM group_membership__sysr
WHERE user__sysr.status__v = 'active__v')
FROM group__sys
WHERE name__v = 'approvers__c' AND type__sys = 'user_managed__sys'
You can query document workflow instances (formerly multi-document workflows) in the following ways:
activeinactiveWorkflows definitions are not queryable.
The following workflow objects are available for query in VQL v21.2+ only. You must have the Application: Workflow: Query permission to query these objects.
| Name | Description |
|---|---|
active_workflow__sys | Each record represents a single in-progress workflow instance. This workflow is running. |
inactive_workflow__sys | Each record represents a single completed or cancelled workflow instance. This workflow is no longer running. |
active_workflow_task__sys | Each record represents a user task instance of an in-progress workflow. There is one task instance per task-user-iteration of a workflow process. For example, a workflow task assigned to three users will have three active_workflow_task__sys records. If a user completes their task, it remains an active_workflow_task__sys until the entire workflow completes or is cancelled. |
inactive_workflow_task__sys | Each record represents a user task instance of a cancelled or completed workflow. There is one task instance per task-user-iteration of a workflow process. For example, a workflow task assigned to three users will have three inactive_workflow_task__sys records. |
active_workflow_task_item__sys | Each record represents an item of a workflow task of an in-progress workflow instance. There is one item for each document in the MDW document set, per task instance. For example, a workflow on two documents with a task sent to three users will have three active_workflow_task__sys, each with two active_workflow_task_item__sys. If a user completes a task item on one document, it remains as active_workflow_task_item__sys until the entire workflow completes or is cancelled. |
inactive_workflow_task_item__sys | Each record represents an item of a workflow task of an in-progress workflow instance. There is one item for each document in the workflow document set, per task instance. For example, a workflow on two documents with a task sent to three users will have three inactive_workflow_task__sys, each with two inactive_workflow_task_item__sys. |
The active_workflow__sys and inactive_workflow__sys objects contain workflow-level information about each workflow instance. A single workflow can have multiple instances, so each record for these objects represents a unique instance of a workflow. For example, an in-progress Read & Understood workflow instance can be queried with active_workflow__sys, and instances of the same Read & Understood workflow which have already completed can be queried with inactive_workflow__sys.
The active_workflow__sys and inactive_workflow__sys objects expose the following relationships:
| Name | Description |
|---|---|
owner__sysr | An outbound reference to this workflow instance owner user (user__sys). |
{in}active_workflow_tasks__sysr | An inbound reference to this workflow’s task instances. |
{in}active_workflow_task_items__sysr | An inbound reference to this workflow’s task item instances. |
Note that this metadata is not retrievable via the standard metadata API.
The active_workflow__sys and inactive_workflow__sys allow queries on the following fields:
| Name | Description |
|---|---|
id | The workflow instance ID. |
label__sys | The workflow label visible to Admins in the Vault UI. |
name__sys | The name of this workflow. |
owner__sys | An object reference to the user__sys record in the workflow owner role. |
cardinality__sys | Indicates how many contents can be included in this workflow; either one__sys or one_or_many__sys. |
type__sys | The workflow type, which is document__sys. In future releases, this may also include object__sys. |
status__sys | The workflow status, which is either active__v, cancelled__v, or completed__v. |
cancelled_date__sys | The date this workflow was cancelled. If the workflow was not cancelled, this value will be null. |
completed_date__sys | The date this workflow was completed. If the workflow has not completed, this value will be null. |
created_by__sys | An object reference to the user__sys record which created this workflow instance. |
created_date__sys | The date this workflow was created. |
modified_by__sys | An object reference to the user__sys record which last modified this workflow instance. |
modified_date__sys | The date this workflow was last modified. |
class__sys | If this is a Read & Understood workflow, this value is read_and_understood__sys. Otherwise, this value is null. |
The active_workflow_task__sys and inactive_workflow_task__sys objects contain task-level information about each user task associated with a workflow. A single task can be assigned to multiple users, so each record for these objects represents a unique instance of a task-user-iteration. For example, assigning the same Approval task to three unique users would create three unique task instances: one for each user. If the workflow later needed to assign new Approval tasks, it would create new task instances.
Workflow tasks remain active until their parent workflow completes. For example, when a user completes a task it remains an active_workflow_task__sys until the entire parent workflow completes or is cancelled. Once the parent workflow completes or is cancelled, the task is available from inctive_workflow_task__sys.
These objects only contain Document Task steps, which are steps assigned to users which require action. Other step types such as Decision or Content Action are not included in these objects. Learn more about document workflow step types in Vault Help.
The active_workflow_task__sys and inactive_workflow_task__sys objects expose the following relationships:
| Name | Description |
|---|---|
{in}active_workflow__sysr | An outbound reference to the parent workflow instance. |
owner__sysr | An outbound reference to the workflow task instance owner user (user__sys). |
{in}active_workflow_task_items__sysr | An inbound reference to the workflow task’s item instances. |
Note that this metadata is not retrievable via the standard metadata API.
The active_workflow_task__sys and inactive_workflow_task__sys allow querying the following fields:
| Name | Description |
|---|---|
id | The workflow task instance ID. |
workflow__sys | An object reference to the parent workflow__sys. |
label__sys | The workflow task label visible to users in the Vault UI. |
name__sys | The name of this workflow task. |
owner__sys | An object reference to the user__sys record assigned to this task. If unassigned, this value is null. |
status__sys | The status of this workflow task, either available__sys, assigned__sys, completed__sys, or cancelled__sys. |
assigned_date__sys | The date this workflow task was assigned. |
cancelled_date__sys | The date this workflow task was cancelled. If the task was not cancelled, this value will be null. |
completed_date__sys | The date this workflow task was completed. If the task was not completed, this value will be null. |
created_date__sys | The date this workflow task instance was created. |
due_date__sys | The date by which this task must be completed. If the task has no due date, this value will be null. |
modified_date__sys | The date this workflow task was last modified. |
iteration__sys | The number of times this task instance has iterated. For example, a user completed an Approval task, then something changed on the document, requiring the user to complete the Approval task a second time. In this case, iteration__sys would be 2. |
instructions__sys | The written instructions for this workflow task. This is custom text created by your Vault Admin when configuring this workflow task. |
group_instructions__sys | The written instructions for this workflow task, sent when a task is made available to multiple participants. This is custom text created by the workflow owner. This field will only have a value if the workflow is configured to Allow workflow initiator to select participants and to Allow task instructions for these participants. |
The active_workflow_task_item__sys and inactive_workflow_task_item__sys objects contain item-level information about each user task associated with a workflow. A single task can be assigned on multiple documents, so each record in these objects represents a unique instance of a task-user-document-iteration. For example, assigning an Approval task on three documents would create three unique task item instances: one for each document. Assigning this Approval task on three documents to two users would create two task instances and six item instances: one task instance for each user, and three task item instances for each task instance.
The active_workflow_task_item__sys and inactive_workflow_task_item__sys objects expose the following relationships:
| Name | Description |
|---|---|
{in}active_workflow__sysr | An outbound reference to the parent workflow instance. |
{in}active_workflow_tasks__sysr | An outbound reference to the parent task instance. |
document_sysr | An unbound reference to the document associated with this task item instance. This always points to the latest version of the document which the authenticated user has permission to view. |
The active_workflow_task_item__sys and inactive_workflow_task_item__sys allow queries on the following fields:
| Name | Description |
|---|---|
id | The workflow task item instance ID. |
task__sys | An object reference to the parent task record. |
workflow__sys | An object reference to the parent workflow record. |
status__sys | The status of this workflow task item, either completed__sys, cancelled__sys, excluded__sys, or pending__sys. |
capacity__sys | If configured, task verdicts may require a capacity. This is a user-inputted value providing more context for the verdict. If this task item is still in progress or capacity is not required, this value may be null. |
verdict__sys | If configured, task items may require a verdict. This is a user-inputted value providing context for task completion. If this task item is still in progress or verdict is not required, this value may be null. |
verdict_reason__sys | If configured, task verdicts may require a reason. This is a user-inputted value providing more context for the verdict. If this task item is still in progress or a verdict reason is not required, this value may be null. |
verdict_comment__sys | If configured, task verdicts may require a comment. This is a user-inputted value providing more context for the verdict. If this task item is still in progress or a comment is not required, this value may be null. |
type__sys | The type of workflow task item, either document__sys or document_version__sys. In future releases, this will also include object__sys. |
document_id__sys | The document ID for this task item. |
verdict_document_major_version_number__sys | If this task item has a verdict__sys, this field value is the major version of the document associated with the verdict. |
verdict_document_minor_version_number__sys | If this task item has a verdict__sys, this field value is the major version of the document associated with the verdict. |
verdict_document_version_id__sys | If this task item has a verdict__sys, this field value contains the ID, major version, and minor version of the document associated with the verdict. The format is id_major_minor. |
The following query retrieves the due dates for all currently unassigned tasks:
SELECT label__sys, due_date__sys
FROM active_workflow_task__sys
WHERE status__sys = 'available__sys’
The following query retrieves the open tasks for a given user:
SELECT label__sys, workflow__sys, owner__sys, due_date__sys, active_workflow__sysr.owner__sys
FROM active_workflow_task__sys
WHERE owner__sysr.username__sys = 'olivia@veepharm.com'
AND status__sys = 'active__v'
The following query retrieves the task details of a specified completed workflow:
SELECT id, owner__sys, name__sys, iteration__sys, inactive_workflow__sysr.owner__sys, inactive_workflow__sysr.name__sys, inactive_workflow__sysr.type__sys,
(SELECT verdict__sys, verdict_reason__sys, capacity__sys
FROM inactive_workflow_task_items__sysr)
FROM inactive_workflow_task__sys
WHERE inactive_workflow__sysr.id = 123
The workflows object allows queries on legacy workflows. Learn more about legacy workflows in Vault Help.
Note that the escape sequences available for special characters are not standardized for legacy workflows and may behave differently.
The Query API is used to construct simple but powerful queries to retrieve data from Veeva Vault. This article covers advanced query use cases using Join relationships. For general information about the Query API, refer to Query Syntax & Structure or Vault Query API Overview. You may also find additional information and help with Vault queries in the Vault Developer Community.
Note: Relationship queries are supported as of API v10.0
VQL is the object query language for querying data in Vault. You can think of it as an object oriented cousin of SQL, where the data model and the way to use joins differs from SQL. Vault’s data model is based on objects that are related to each other by relationships. Navigating these relationships is a fundamental building block in constructing queries that allow you to use data from different objects, thus using implicit joins. In other words, the relationship syntax in VQL allows you to join data using path traversal.
Programmatic access to a result set of a VQL query naturally reflects the object oriented nature of the underlying data model. VQL uses the navigation and relationships and is anchored to a particular object from where the result set is obtained by using the FROM clause. Consequently, the returned result set is a list of objects that reflect the object that constitutes the domain of the VQL query. The result set also contains the list of fields that are included in the SELECT clause, including those that are traversed via the relationships. The SELECT clause can include nested SELECT-FROM statements using relationships and the result set can include nested objects within a result set of objects. This is illustrated in the examples below.
In this tutorial, we’ll look at some of the capabilities and patterns in VQL that allow developers to conceptualize familiar concepts like joins in SQL and puts them in perspective with respect to VQL’s syntax and capabilities.
When performing relationship queries, you must include at least one field in each SELECT statement.
You can use a maximum of 10 relationships in a single query. The way in which Vault counts the number of relationships in a query is based on the way VQL constructs the joins in order to process the query. The join is only needed for the following two conditions:
In the examples below, we’ll use a simplified subset of baseball team rosters.
Object A: Teams team__v
Team ID id | Team Name name__v | Team City city__v |
|---|---|---|
| 101 | Giants | San Francisco |
| 102 | Royals | Kansas City |
| 103 | Yankees | New York |
| 104 | Cubs | Chicago |
Object B: Players player__v
Player ID id | Player Name name__v | Player Position position__v | Team team__v |
|---|---|---|---|
| 44 | Doe | Pitcher | 101 |
| 55 | Post | Catcher | 101 |
| 66 | Daniels | First Base | 102 |
| 77 | Perez | Catcher | 102 |
| 88 | Beltran | Right Field | 103 |
| 99 | Ryan | Short Stop | 103 |
We’ll represent the Teams object as team__v and the Players object as player__v. Each team has a one-to-many (1:M) relationship with its players (one team has many players and one player can be assigned to only one team). These are Parent-Child relationships, where team__v is the parent of player__v. The objects are connected by an inbound and outbound relationship, always looked at from the perspective of the child object. In other words, the child object has a relationship coming “inbound” from the parent object and another relationship going “outbound” to the parent object. The team__v relationship field on the player__v object establishes its relationship with the team__v object.
Relationship naming conventions always use the plural form of the child object for inbound relationships and the singular form of the parent object for outbound relationships, Relationship names end in __vr here and in Vault for standard __v objects. For example, The inbound relationship name is players__vr for the team__v and player__v objects and the outbound relationship name is team__vr. The relationship names allow us to traverse from parent to child or child to parent. The examples below illustrate their usage in relationship queries (Joins). These are completely analogous to Vault Object queries.
NOTE: The team and player objects share some of the same field names: id and name__v. This is merely a naming convention. The field values are different on each object. The id or name retrieved (team or player) depends on the object being queried.
Problem
Retrieve the id, name, and city from all teams and the id, name, and position of the players assigned to each team.
Query
SELECT id, name__v, city__v, (SELECT id, name__v, position__v FROM players__vr) FROM team__v
Result
Team ID id | Team Name name__v | Team City city__v | Player ID id | Player Name name__v | Player Position position__v |
|---|---|---|---|---|---|
| 101 | Giants | San Francisco | 44 | Doe | Pitcher |
| 101 | Giants | San Francisco | 55 | Post | Catcher |
| 102 | Royals | Kansas City | 66 | Daniels | First Base |
| 102 | Royals | Kansas City | 77 | Perez | Catcher |
| 103 | Yankees | New York | 88 | Beltran | Right Field |
| 103 | Yankees | New York | 99 | Ryan | Short Stop |
| 104 | Cubs | Chicago | null | null | null |
| 104 | Cubs | Chicago | null | null | null |
Discussion
The object of the query is team__v, from which we’re directly retrieving the team id, name, and city. Since the team__v object is the parent of the player__v object, we used the inbound relationship name players__vr in a nested SELECT-FROM statement to retrieve the player id, name, and position from the player__v object.
Nested SELECT-FROM statements within the SELECT clause are very useful for obtaining related records by traversing from the “1” side of a 1:M relationship (from parent to child). We know from SQL that the result of a left outer join for objects A and B always contains all records of the “left” object (A), even if the join-condition does not find any matching record in the “right” object (B). Notice in this query that the “Cubs” team returned null results from the player object.
Problem
Retrieve the id, name, and city from all teams. Restrict the results to teams with assigned players.
Query
SELECT id, name__v, city__v FROM team__v WHERE id IN (SELECT name__v FROM players__vr)
Result
Team ID id | Team Name name__v | Team City city__v |
|---|---|---|
| 101 | Giants | San Francisco |
| 101 | Giants | San Francisco |
| 102 | Royals | Kansas City |
| 102 | Royals | Kansas City |
| 103 | Yankees | New York |
| 103 | Yankees | New York |
Discussion
This is nearly identical to the previous query but we’ve removed the nested SELECT-FROM statement from the SELECT clause and placed another in the WHERE clause. We’re also using the team id (which exists in both objects) and the logical operator IN. The object of the query is team__v. The nested SELECT-FROM statement is using the inbound relationship name players__vr to look at the player names in the player__v object and return results only when the team id exists in a player’s row. We’re not asking to return the player names (they are not included in the first SELECT clause) but only to filter on them.
Combining a nested SELECT-FROM statement with the IN operator in the WHERE clause allows us to test whether the team id exists and return results only when it does. We know from SQL that the result of an inner join for objects A and B requires that each record in the two joined objects have matching records. This query compared each row of object A with each row of object B and found all pairs of rows which satisfied the join-predicate (non-null values).
Problem
Retrieve the id and name of all players in the Catcher position and the id, name, and city of the team to which each player is assigned.
Query
SELECT id, name__v, team__vr.id, team__vr.name__v, team__vr.city__v FROM player__v WHERE position__v = 'Catcher'
Result
Player ID id | Player Name name__v | Team ID team__vr.id | Team Name team__vr.name__v | Team City team__vr.city__v |
|---|---|---|---|---|
| 55 | Post | 101 | Giants | San Francisco |
| 77 | Perez | 102 | Royals | Kansas City |
Discussion
Unlike the previous two queries in which the object of the query was team__v (parent), we’re now querying the object player__v (child). Child to parent queries are many-to-one (M:1) relationships and a nested SELECT-FROM statement can’t be used. To retrieve the fields from the parent object, we must combine its field names with the outbound relationship name team__vr using dot-notation.
Look at the basic part of this query: SELECT the player id and name from the player object WHERE the position is “Catcher”. Using dot-notation, add team__vr.id, team__vr.name__v, and team__vr.city__v to the SELECT clause. The outbound relationship name team__vr allows us to traverse “outbound” from the child player__v object to the parent team__v object to retrieve the team id, name, and city. The response contains the selected fields from each child object and the related parent object fields.
The last type of query we’ll discuss is a “lookup”.
Object A: Teams team__v
Team ID id | Team Name name__v | Mascot mascot__v |
|---|---|---|
| 101 | Giants | 5 |
| 102 | Royals | 6 |
| 103 | Yankees | 7 |
| 104 | Cubs | 8 |
Object C: Mascots mascot__v
Mascot ID id | Mascot Name name__v | Animal animal__v |
|---|---|---|
| 5 | Lou | Seal |
| 6 | Slugger | Lion |
| 7 | Dandy | Bird |
| 8 | Clark | Bear |
For this example, we’ll modify the Teams object team__v and create a new Mascots object mascot__v. Each team has a 1:1 relationship with its mascot (each team has only one mascot and each mascot has only one team). There is no true parent to child or child to parent relationship between these objects. There is what’s referred to as a “reference relationship”, which is why a “lookup” query must be used. To traverse the relationship, we’ll use the reference relationship name mascots__vr.
NOTE: The team and mascot objects share some of the same field names: id and name__v. This is merely a naming convention. The field values are different on each object. The id or name retrieved (team or mascot) depends on the object being queried.
Problem
Retrieve the team id and name which has a “Bear” as a mascot.
Query
SELECT id, name__v FROM team__v WHERE mascots__vr.animal__v = 'Bear'
Result
Team ID id | Team Name name__v |
|---|---|
| 104 | Cubs |
Discussion
The object of the query is team__v, which includes the mascot__v object field for the mascot but not its name or animal. This type of query is called a “lookup” because we’re using the animal__v field record from the mascot__v object and using dot-notation to combine it with the mascots__vr relationship in the WHERE clause, thereby looking up the team id and name from the team__v object.
In the examples below, we’ll use a simplified subset of Vault documents and products.
Object A: Documents documents
Document ID id | Document Name name__v | Document Type type__v |
|---|---|---|
| 1 | CholeCap Study | Study |
| 2 | Nyaxa Brochure | Promotional Piece |
| 3 | VeevaProm Information | Reference Document |
Object B: Products product__v
Product ID id | Product Name name__v | Generic Name generic__v |
|---|---|---|
| 01010 | CholeCap | cholepridol phosphate |
| 02020 | Nyaxa | nitroprinaline oxalate |
| 03030 | VeevaProm | veniladrine |
| 04040 | VeevaProm XR | veniladrine extended |
In Vault, documents documents and products product__v have many-to-many (M:M) relationships (a document may be associated with many products and a product may be associated with many documents). The same applies to document relationships with other Vault objects such as country__v, study__v, site__v, etc. These document-object relationships are referred to as reference relationships. Their reference relationship names (to use in relationship queries) are exposed in the document metadata relationship fields. We’ll discuss finding relationships in the sections below.
To traverse the document-product relationship, we’ll use the reference relationship name document_product__vr. The two queries shown below illustrate the bidirectional nature of document-product relationships. The direction of the query and structure of results depends on which object is being queried, i.e., the object in the FROM clause.
NOTE: The document and product objects share some of the same field names: id and name__v. This is merely a naming convention. The field values are different on each. The id or name retrieved (document or product) depends on the which is queried.
Problem
Query the Documents object documents to retrieve document fields. Use the document_product__vr relationship to retrieve product fields associated with each document.
Query
SELECT id, name__v, type__v, (SELECT id, name__v, generic__v FROM document_product__vr) FROM documents
Result
Document ID id | Document Name name__v | Document Type type__v | Product ID id | Product Name name__v | Generic Name generic__v |
|---|---|---|---|---|---|
| 1 | CholeCap Study | Study | 01010 | CholeCap | cholepridol phosphate |
| 2 | Nyaxa Brochure | Promotional Piece | 02020 | Nyaxa | nitroprinaline oxalate |
| 3 | VeevaProm Information | Reference Document | 03030 | VeevaProm | veniladrine |
| 3 | VeevaProm Information | Reference Document | 04040 | VeevaProm XR | veniladrine extended |
Problem
Query the Products object product__v to retrieve the product fields. Use the document_product__vr relationship to retrieve document fields associated with each product.
Query
SELECT id, name__v, generic__v, (SELECT id, name__v, type__v FROM document_product__vr) FROM product__v
Result
Product ID id | Product Name name__v | Generic Name generic__v | Document ID id | Document Name name__v | Document Type type__v |
|---|---|---|---|---|---|
| 01010 | CholeCap | cholepridol phosphate | 1 | CholeCap Study | Study |
| 02020 | Nyaxa | nitroprinaline oxalate | 2 | Nyaxa Brochure | Promotional Piece |
| 03030 | VeevaProm | veniladrine | 3 | VeevaProm Information | Reference Document |
| 04040 | VeevaProm XR | veniladrine extended | 3 | VeevaProm Information | Reference Document |
Results in List Format (Document to Product / Product to Document)
Discussion
As shown in the two examples above, using the document_product__vr relationship in a nested SELECT-FROM statement allows you to retrieve fields from both objects in a single query. The bidirectional nature of this M:M relationship allows you to place either object in the FROM clause, thereby obtaining nested results in two different ways.
Relationships (and the relationship names to use in queries) are exposed in the Document and Object Metadata APIs for fields of the type ObjectReference where the referenced object objectType is a Vault Object.
Here are the endpoints:
/api/{version}/metadata/objects/documents/properties/api/{version}/metadata/vobjects/{object name}To find relationships on a document or object, search the metadata response for fields with the following attributes (an example is provided in the JSON response below):
type : ObjectReference
objectType : vault_object__v
object__v for standard objects or object__c for custom objects, e.g., product__v, study__v, custom_object__c, etc. relationshipType : reference, reference_inbound, reference_outbound, parent, or child.
relationshipName : relationship_name__vr for standard objects or relationship_name__cr for custom objects.
study_number__vr, sites__vr, document_product__vr, etc.All document-object relationships are defined on the documents object and always take the form document_{field_name}__vr for standard objects or document_{field_name}__cr for custom objects.
For example:
product__v relationship field has the relationship name: document_product__vrcountry__v relationship field has the relationship name: document_country__vrcustom_object__c relationship has the relationship name: document_custom_object__crYou can retrieve the metadata of all document properties by sending a GET request to the /api/{version}/metadata/objects/documents/properties endpoint.
For example:
$ curl -X GET -H "Authorization: {SESSION_ID}" \
https://{CUSTOMER}.veevavault.com/api/{version}/metadata/objects/documents/properties
JSON Response (abridged)
The response contains the list of all document fields (id, name__v, type__v, etc.) configured across entire document type hierarchy. The response shown below lists the details of the product__v relationship field on the documents object. Note that product__v is both a Vault Object and a relationship field on other objects.
{
"name": "product__v",
"scope": "DocumentVersion",
"type": "ObjectReference",
"required": false,
"repeating": true,
"systemAttribute": true,
"editable": true,
"setOnCreateOnly": false,
"disabled": false,
"objectType": "product__v",
"label": "Product",
"section": "productInformation",
"sectionPosition": 1,
"hidden": false,
"queryable": true,
"shared": false,
"definedInType": "type",
"definedIn": "base_document__v",
"relationshipType": "reference",
"relationshipName": "document_product__vr"
}
Discussion
The product__v field is just one of many document fields. By searching the complete list of metadata returned for the Document Metadata API, you can find all relationships configured on the documents object which link documents to other objects in Vault. You can also create custom objects with relationships to documents or other objects. Once you have the relationship names, structure queries by using the relationship names in nested SELECT-FROM statements in either the SELECT clause or WHERE clause. You can also use multiple nested statements in a single query.
With Vault Objects, you can configure various relationships, including parent-child (hierarchical) and reference (non-hierarchical) relationships. Reference relationships can point to another object or back to the same object (self-referencing). Parent-child relationships are typically one-to-one (1:1) or one-to-many (1:M), but you can create a many-to-many (M:M) relationship by using a third (join) object. Learn more about Object Relationships in Vault Help.
This article describes how to set up many-to-many object and document relationships and then using the relationships to construct VQL queries such that a set of records from one parent can be retrieved based on another parent directly. These types of queries are possible by utilizing a third (join) object, which is a child object with two parents.
By using the inbound relationship to the join object from one of the parent objects, you can traverse (navigate) “down” from one of the parent objects to the join object. From there, you can use the outbound relationship from the join object to traverse back “up” to the second parent object. This is all done in a single query using subselect statements.
approved_country__c). This must be done in the Admin UI. Learn how.product_field__c) on the new object. Set the “Field Type” to “Parent Object” referencing the standard object product__v.country_field__c) on the new object. Set the “Field Type” to “Parent Object” referencing the standard object country__v.The new approved_country__c object is referred to as a “Join Object” (a child object of both the product__v and country__v objects).
Note: For the purposes of this article, we’ll refer to product__v as “Parent 1” and country__v as “Parent 2”. These are arbitrary names as both are simply “parent” objects of approved_country__c.
To retrieve the relationship metadata on the approved_country__c (Child) object, send a GET request to the /api/{VERSION}/metadata/vobjects/approved_country__c endpoint.
{
"responseStatus": "SUCCESS",
"object": {
"available_lifecycles": [],
"label_plural": "Approved Countries",
"help_content": null,
"prefix": "A09",
"in_menu": true,
"description": "Child join object with parents product and country.",
"label": "Approved Country",
"source": "custom",
"modified_date": "2015-10-22T16:39:55.000Z",
"created_by": 46916,
"allow_attachments": false,
"dynamic_security": false,
"relationships": [
{
"field": "country_field__c",
"relationship_name": "country_field__cr",
"relationship_label": "Country Field",
"relationship_type": "parent",
"relationship_deletion": "block",
"object": {
"url": "/api/v13.0/metadata/vobjects/country__v",
"label": "Country",
"name": "country__v",
"label_plural": "Countries",
"prefix": "00C"
}
},
{
"field": "product_field__c",
"relationship_name": "product_field__cr",
"relationship_label": "Product Field",
"relationship_type": "parent",
"relationship_deletion": "block",
"object": {
"url": "/api/v13.0/metadata/vobjects/product__v",
"label": "Product",
"name": "product__v",
"label_plural": "Products",
"prefix": "00P"
}
}
],
"urls": {
"field": "/api/v13.0/metadata/vobjects/approved_country__c/fields/{name}",
"record": "/api/v13.0/vobjects/approved_country__c/{id}",
"list": "/api/v13.0/vobjects/approved_country__c",
"metadata": "/api/v13.0/metadata/vobjects/approved_country__c"
},
"fields": [
{
"help_content": "Field on the Approved Country object which references the Product parent object.",
"editable": true,
"relationship_deletion": "block",
"label": "Product Field",
"source": "custom",
"type": "Object",
"relationship_outbound_name": "product_field__cr",
"modified_date": "2015-10-22T16:44:01.000Z",
"created_by": 46916,
"required": true,
"relationship_inbound_label": "Approved Countries",
"relationship_type": "parent",
"name": "product_field__c",
"list_column": true,
"modified_by": 46916,
"relationship_inbound_name": "approved_countries__cr",
"created_date": "2015-10-22T16:44:01.000Z",
"status": [
"active__v"
],
"object": {
"url": "/api/v13.0/metadata/vobjects/product__v",
"label": "Product",
"name": "product__v",
"label_plural": "Products",
"prefix": "00P"
},
"order": 10
},
{
"help_content": "Field on the Approved Country object which references the Country parent object.",
"editable": true,
"relationship_deletion": "block",
"label": "Country Field",
"source": "custom",
"type": "Object",
"relationship_outbound_name": "country_field__cr",
"modified_date": "2015-10-22T16:44:25.000Z",
"created_by": 46916,
"required": true,
"relationship_inbound_label": "Approved Countries",
"relationship_type": "parent",
"name": "country_field__c",
"list_column": true,
"modified_by": 46916,
"relationship_inbound_name": "approved_countries__cr",
"created_date": "2015-10-22T16:44:25.000Z",
"status": [
"active__v"
],
"object": {
"url": "/api/v13.0/metadata/vobjects/country__v",
"label": "Country",
"name": "country__v",
"label_plural": "Countries",
"prefix": "00C"
},
"order": 11
},
To retrieve the relationship metadata on the product__v (Parent 1) object, send a GET request to the /api/{VERSION}/metadata/vobjects/product__v endpoint.
"relationships": [
{
"field": "product_field__c",
"relationship_name": "approved_countries__cr",
"relationship_label": "Approved Countries",
"relationship_type": "child",
"relationship_deletion": "block",
"object": {
"url": "/api/v13.0/metadata/vobjects/approved_country__c",
"label": "Approved Country",
"name": "approved_country__c",
"label_plural": "Approved Countries",
"prefix": "A09"
}
},
To retrieve the relationship metadata on the country__v (Parent 2) object, send a GET request to the /api/{VERSION}/metadata/vobjects/country__v endpoint.
"relationships": [
{
"field": "country_field__c",
"relationship_name": "approved_countries__cr",
"relationship_label": "Approved Countries",
"relationship_type": "child",
"relationship_deletion": "block",
"object": {
"url": "/api/v13.0/metadata/vobjects/approved_country__c",
"label": "Approved Country",
"name": "approved_country__c",
"label_plural": "Approved Countries",
"prefix": "A09"
}
},
Once you’ve set up relationships between the join object and two parent objects (described in Part 1 above), you can start building queries.
In this query, we’re starting from the product__v (Parent 1) object, traversing “down” through the approved_country__c (Child) object, and then back “up” to the country__v (Parent 2) object.
This allows us to retrieve fields and values from all three objects in a single query.
SELECT id, name__v, (SELECT name__v, local_name__c, approval_status__c, country_field__cr.name__v, country_field__cr.external_id__v FROM approved_countries__cr) FROM product__v
This query can be broken down into the following steps:
id and name__v from all products. The product__v (Parent 1) object is the “driver object” of this query.approved_countries__cr relationship in a subselect to retrieve the name__v, local_name__c, and approval_status__v from the approved_country__c (Child) object.approved_countries__cr relationship in a subselect to retrieve the name__v and external_id__v (using dot-notation on the country_field__cr relationship) from the country__v (Parent 2) object.{
"responseStatus": "SUCCESS",
"responseDetails": {
"limit": 1000,
"offset": 0,
"size": 12,
"total": 12
},
"data": [
{
"id": "00P000000000101",
"name__v": "Nyaxa",
"approved_countries__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 2,
"total": 2
},
"data": [
{
"name__v": "Spain",
"local_name__c": "Nyasená",
"approval_status__c": "Pending",
"country_field__cr.name__v": "Spain",
"country_field__cr.external_id__v": "SPA-014"
},
{
"name__v": "Italy",
"local_name__c": "Nyza",
"approval_status__c": "Approved",
"country_field__cr.name__v": "Italy",
"country_field__cr.external_id__v": "ITA-015"
}
]
}
},
{
"id": "00P000000000102",
"name__v": "Gludacta",
"approved_countries__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 2,
"total": 2
},
"data": [
{
"name__v": "United States",
"local_name__c": "Gludacta",
"approval_status__c": "Approved",
"country_field__cr.name__v": "United States",
"country_field__cr.external_id__v": "USA-012"
},
{
"name__v": "Italy",
"local_name__c": "Gludasom",
"approval_status__c": "Approved",
"country_field__cr.name__v": "Italy",
"country_field__cr.external_id__v": "ITA-015"
}
]
}
},
In this query, we’re starting from the country__v (Parent 2) object, traversing “down” through the approved_country__c (Child) object, and then back “up” to the product__v (Parent 1) object.
SELECT id, name__v, (SELECT name__v, local_name__c, approval_status__c, product_field__cr.name__v, product_field__cr.external_id__v FROM approved_countries__cr) FROM country__v
This query can be broken down into the following steps:
id and name__v from all countries. The country__v (Parent 2) object is the “driver object” of this query.approved_countries__cr relationship in a subselect to retrieve the name__v, local_name__c, and approval_status__v from the approved_country__c (Child) object.approved_countries__cr relationship in a subselect to retrieve the name__v and external_id__v (using dot-notation on the product_field__cr relationship) from the product__v (Parent 1) object.{
"responseStatus": "SUCCESS",
"responseDetails": {
"limit": 1000,
"offset": 0,
"size": 17,
"total": 17
},
"data": [
{
"id": "1357662840400",
"name__v": "United States",
"approved_countries__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 1,
"total": 1
},
"data": [
{
"name__v": "United States",
"local_name__c": "Gludacta",
"approval_status__c": "Approved",
"product_field__cr.name__v": "Gludacta",
"product_field__cr.external_id__v": "GLU-00577"
}
]
}
},
{
"id": "1357662840582",
"name__v": "Italy",
"approved_countries__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 2,
"total": 2
},
"data": [
{
"name__v": "Italy",
"local_name__c": "Nyza",
"approval_status__c": "Approved",
"product_field__cr.name__v": "Nyaxa",
"product_field__cr.external_id__v": "NYA-00278"
},
{
"name__v": "Italy",
"local_name__c": "Gludasom",
"approval_status__c": "Approved",
"product_field__cr.name__v": "Gludacta",
"product_field__cr.external_id__v": "GLU-00577"
}
]
}
},
{
"id": "1357662840631",
"name__v": "Spain",
"approved_countries__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 1,
"total": 1
},
"data": [
{
"name__v": "Spain",
"local_name__c": "Nyasená",
"approval_status__c": "Pending",
"product_field__cr.name__v": "Nyaxa",
"product_field__cr.external_id__v": "NYA-00278"
}
]
}
},
To create a many-to-many relationship between documents and a join object:
approved_country__c). This must be done in the Admin UI. Learn how.approved_country__c, which is a child of both the product__v and country__v fields.To see the relationship metadata on the documents object, send a GET request to the /api/{VERSION}/metadata/objects/documents/properties endpoint.
{
"responseStatus": "SUCCESS",
"properties": [
{
"name": "approved_country__c",
"scope": "DocumentVersion",
"type": "ObjectReference",
"required": false,
"repeating": false,
"systemAttribute": false,
"editable": true,
"setOnCreateOnly": false,
"disabled": false,
"objectType": "approved_country__c",
"label": "Approved Country",
"section": "generalProperties",
"sectionPosition": 1000,
"hidden": false,
"queryable": true,
"shared": false,
"helpContent": "Document field which references the child join object Approved Country.",
"definedInType": "type",
"definedIn": "base_document__v",
"relationshipType": "reference",
"relationshipName": "document_approved_country__cr",
"controllingField": "product__v"
},
Once you’ve set up relationships between documents and the join object (described in Part 3 above), you can start building queries.
In this query, we’re starting from the documents object, traversing “across” through the approved_country__c (Child) object, and then back “up” to the product__v (Parent 1) and country__v (Parent 2) objects.
SELECT id, name__v, type__v, (SELECT name__v, local_name__c, approval_status__c, product_field__cr.name__v, country_field__cr.name__v FROM document_approved_country__cr) FROM documents
This query can be broken down into the following steps:
id, name__v, and type__v from all documents. The documents object is the “driver object” of this query.document_approved_country__cr relationship in a subselect to retrieve the name__v, local_name__c, and approval_status__v from the approved_country__c (Child) object.document_approved_country__cr relationship in a subselect to retrieve the name__v (using dot-notation on the product_field__cr relationship) from the product__v (Parent 1) object.document_approved_country__cr relationship in a subselect to retrieve the name__v (using dot-notation on the country_field__cr relationship) from the country__v (Parent 2) object.{
"responseStatus": "SUCCESS",
"responseDetails": {
"limit": 1000,
"offset": 0,
"size": 201,
"total": 201
},
"data": [
{
"id": 381,
"name__v": "Nyaxa and Your Health",
"type__v": "Promotional Piece",
"document_approved_country__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 1,
"total": 1
},
"data": [
{
"name__v": "Italy",
"local_name__c": "Nyza",
"approval_status__c": "Approved",
"product_field__cr.name__v": "Nyaxa",
"country_field__cr.name__v": "Italy"
}
]
}
},
{
"id": 382,
"name__v": "Nyaxa Information Packet",
"type__v": "Claim",
"document_approved_country__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 1,
"total": 1
},
"data": [
{
"name__v": "Spain",
"local_name__c": "Nyasená",
"approval_status__c": "Pending",
"product_field__cr.name__v": "Nyaxa",
"country_field__cr.name__v": "Spain"
}
]
}
},
{
"id": 383,
"name__v": "Nyaxa Prescribing Information",
"type__v": "Reference Document",
"document_approved_country__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 1,
"total": 1
},
"data": [
{
"name__v": "Italy",
"local_name__c": "Nyza",
"approval_status__c": "Approved",
"product_field__cr.name__v": "Nyaxa",
"country_field__cr.name__v": "Italy"
}
]
}
},
{
"id": 384,
"name__v": "Gludacta Brochure",
"type__v": "Promotional Piece",
"document_approved_country__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 1,
"total": 1
},
"data": [
{
"name__v": "United States",
"local_name__c": "Gludacta",
"approval_status__c": "Approved",
"product_field__cr.name__v": "Gludacta",
"country_field__cr.name__v": "United States"
}
]
}
},
{
"id": 385,
"name__v": "Gludacta Information",
"type__v": "Reference Document",
"document_approved_country__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 1,
"total": 1
},
"data": [
{
"name__v": "Italy",
"local_name__c": "Gludasom",
"approval_status__c": "Approved",
"product_field__cr.name__v": "Gludacta",
"country_field__cr.name__v": "Italy"
}
]
}
},
{
"id": 386,
"name__v": "Gludacta Prescribing Information",
"type__v": "Reference Document",
"document_approved_country__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 1,
"total": 1
},
"data": [
{
"name__v": "United States",
"local_name__c": "Gludacta",
"approval_status__c": "Approved",
"product_field__cr.name__v": "Gludacta",
"country_field__cr.name__v": "United States"
}
]
}
},
In this query, we’re starting from the approved_country__c (Child) object, traversing “across” to the documents object, and also “up” to the product__v (Parent 1) and country__v (Parent 2) objects.
SELECT name__v, local_name__c, approval_status__c, product_field__cr.name__v, country_field__cr.name__v, (SELECT id, name__v, type__v FROM document_approved_country__cr) FROM approved_country__c
This query can be broken down into the following steps:
name__v, local_name__c, approval_status__c from the approved_country__c object. The approved_country__c object is the “driver object” of this query.product_field__cr relationship to retrieve the product name__v field value from the approved_country__c object. This field maps to the product__v object.country_field__cr relationship to retrieve the country name__v field value from the approved_country__c object. This field maps to the country__v object.document_approved_country__cr relationship in a subselect to retrieve the id, name__v, and type__v from the documents object.{
"responseStatus": "SUCCESS",
"responseDetails": {
"limit": 1000,
"offset": 0,
"size": 4,
"total": 4
},
"data": [
{
"name__v": "Italy",
"local_name__c": "Gludasom",
"approval_status__c": "Approved",
"product_field__cr.name__v": "Gludacta",
"country_field__cr.name__v": "Italy",
"document_approved_country__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 1,
"total": 1
},
"data": [
{
"id": 385,
"name__v": "Gludacta Information",
"type__v": "Reference Document"
}
]
}
},
{
"name__v": "Italy",
"local_name__c": "Nyza",
"approval_status__c": "Approved",
"product_field__cr.name__v": "Nyaxa",
"country_field__cr.name__v": "Italy",
"document_approved_country__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 2,
"total": 2
},
"data": [
{
"id": 381,
"name__v": "Nyaxa and Your Health",
"type__v": "Promotional Piece"
},
{
"id": 383,
"name__v": "Nyaxa Prescribing Information",
"type__v": "Reference Document"
}
]
}
},
{
"name__v": "United States",
"local_name__c": "Gludacta",
"approval_status__c": "Approved",
"product_field__cr.name__v": "Gludacta",
"country_field__cr.name__v": "United States",
"document_approved_country__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 2,
"total": 2
},
"data": [
{
"id": 384,
"name__v": "Gludacta Brochure",
"type__v": "Promotional Piece"
},
{
"id": 386,
"name__v": "Gludacta Prescribing Information",
"type__v": "Reference Document"
}
]
}
},
{
"name__v": "Spain",
"local_name__c": "Nyasená",
"approval_status__c": "Pending",
"product_field__cr.name__v": "Nyaxa",
"country_field__cr.name__v": "Spain",
"document_approved_country__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 1,
"total": 1
},
"data": [
{
"id": 382,
"name__v": "Nyaxa Information Packet",
"type__v": "Claim"
}
]
}
}
]
}
Vault Query Language (VQL) is a SQL-like language which allows you to query information in Vault. Criteria VQL is a slimmed-down version of VQL, entered through the Vault UI. This article provides detailed information for Vault Admins entering Criteria VQL when configuring:
The image below shows the Dynamic Security Rule Criteria in the Vault UI.
Use the Token Helper button to the right of the Criteria VQL text box to search for available tokens on an object. String field values are case-sensitive.
Use a backslash (\) to escape special characters in VQL.
The following applies to static constraints; Dynamic Reference Constraints use tokens.
Rules for DAC and Static Reference Constraints use the same criteria. The sections below explain the available fields.
Note that nested expressions are not allowed, i.e., Join relationships. Additionally, OR is not supported for constraints on documents.
Object record IDs are system-managed fields used in the API and not visible in the Vault UI. If you know the object record ID, you can use it to identify the object record. The object record name, however, serves the same purpose.
| Object | Object Record | Field Name | Field Value (example) | Rule Criteria Entry |
|---|---|---|---|---|
| Product | CholeCap | id | 1357663087386 | id = 1357663087386 |
| Study | VVT485-301 | id | 1357752641846 | id = 1357752641846 |
We recommend using object record name fields and lookup fields to identify your object records. Examples are provided below.
Enter text field value labels as shown in the object record details (capitals, spaces, special characters, etc.) and enclose all values in single-quotes. These are case-sensitive (“Cholecap” does not equal “CholeCap”).
Here are some examples of commonly used criteria:
| Object Label | Field Label | Field Name | Field Value (example) | Rule Criteria Entry |
|---|---|---|---|---|
| Product | Product Name | name__v | CholeCap | name__v = 'CholeCap' |
| Country | Country Name | name__v | United States | name__v = 'United States' |
| Study | Study Number | name__v | VVT485-301 | name__v = 'VVT485-301' |
| Study | Study Name | study_name__vs | Cholecap Efficacy Trial | study_name__vs = 'Cholecap Efficacy Trial' |
Picklist behavior varies slightly between Documents, Objects, and Workflows.
To query workflow picklists, use the picklist value label enclosed in single-quotes. For example, the “Therapeutic Area” picklist field has the picklist value label “Hematology”.
If you supply a value that is not a valid label, VQL will treat the result as undefined. This means inequalities will return nothing. For example, workflow_type_v {=,>,<} 'Invalid Label' would all return nothing because 'Invalid Label' is not a valid value of the picklist.
To query document picklists, use the picklist value label enclosed in single-quotes. For example, the “Therapeutic Area” picklist field has the picklist value label “Hematology”.
If you supply an invalid value for label, VQL will treat the label as a string. This means inequalities will still operate and return results for invalid picklist values. For example, if a picklist named p contains values {'k', 'g', 'a'}, then p < 'h' would evaluate to true for values 'a' and 'g'.
To query object picklists, do not enter picklist value labels as shown in the object record details. Instead, you must use the picklist value name enclosed in single-quotes. For example, the “Therapeutic Area” picklist field has the picklist value label “Hematology” and the picklist value name “hematology__vs”. To find picklist value names, go to Business Admin > Picklists.
If you supply an invalid value for label, VQL will treat the label as a string. This means inequalities will still operate and return results for invalid picklist values. For example, if a Picklist named p contains values {'k', 'g', 'a'}, then p < 'h' would evaluate to true for values 'a' and 'g'.
Here are some examples of commonly used criteria:
| Object Label | Field Label | Field Name | Field Value (example) | Rule Criteria Entry |
|---|---|---|---|---|
| Product | Therapeutic Area | therapeutic_area__vs | hematology__vs | therapeutic_area__vs = 'hematology__vs' |
| Product | Product Family | product_family__vs | wonderdrug_family__c | product_family__vs = 'wonderdrug_family__c' |
| Study | Study Type | study_type__v | safety__vs | study_type__v = 'safety__vs' |
| Study | Study Phase | study_phase__v | phase3__vs | study_phase__v = 'phase3__vs' |
Many object records have relationships with other object records. For example, the object record details for study number “VVT485-301” shows that it is associated with the product “CholeCap”. When looking at fields configured on a particular object, these have the data type “Object” with the object type in parentheses. For example, on the Study object includes the field name “product__v”.
Assume you’re configuring rule criteria on the Study object and want to filter on the product named “CholeCap”. You cannot enter name__v = 'CholeCap' because the name__v field applies to the Study. If you knew the product ID, you could enter id = '1357663087386'. However, this is most easily achieved by using an “object lookup field” in the form product__vr.name__v = 'Cholecap'. By adding __vr to the product name and using dot-notation to combine it with a product object field, Vault allows you to traverse the relationship between the two objects. You can apply this method to any Vault object.
vault_object__v), the lookup name is vault_object__vr.name__v.custom_object__c), the lookup name is custom_object__cr.name__v.Here are some examples of commonly used criteria:
| Object Label | Field Label | Field Lookup Name | Field Lookup Value (example) | Rule Criteria Entry |
|---|---|---|---|---|
| Study | Product | product__v | WonderDrug | product__vr.name__v = 'WonderDrug' |
| Study Site | Study | study_number__v | VVT485-301 | study_number__vr.name__v = 'VVT485-301' |
| Study Site | Study Location | location__v | UCSF Medical Center | location__vr.name__v = 'UCSF Medical Center' |
| Study Site | Study Country | study_country__v | United States | study_country__vr.name__v = 'United States' |
| Location | Country | country__v | United States | country__vr.name__v = 'United States' |
| Study Country | Study Number | study_number__v | VVT485-301 | study_number__vr.name__v = 'VVT485-301' |
Note: As a best practice, we recommend using the “token” button to the right of the Criteria VQL text box to search for available object lookup fields.
All Dates and DateTimes are entered and returned in UTC (Coordinated Universal Time) and not the user’s time zone.
Here are some examples of commonly used criteria:
| Object Label | Field Label | Field Name | Field Value (example) | Rule Criteria Entry |
|---|---|---|---|---|
| Product | Created Date | created_date__v | 2014-12-20T15:30:00.000Z | created_date__v != '2014-12-20T15:30:00.000Z' |
| Study | Start Date | study_start_date__vs | 2014-12-20 | study_start_date__vs >= '2014-12-20' |
Numeric fields are always used with comparison operators (=, !=, <, >, etc.). You do not need to enclose the field value in single- or double-quotes.
Here are some examples using numeric fields as rule criteria:
| Object Label | Field Label | Field Name | Field Value (example) | Rule Criteria Entry |
|---|---|---|---|---|
| Study | Enrollment | enrollment__vs | 5000 | enrollment__vs < 5000 |
| Publication | Distribution | distribution__c | 200 | distribution__v >= 200 |
Boolean fields have only two possible values: true or false. In Vault Admin, these are referred to as “Yes/No” fields. You do not need to enclose the field value in single- or double-quotes.
Here are some examples using Boolean fields as rule criteria:
| Object Label | Field Label | Field Name | Field Value | Rule Criteria Entry |
|---|---|---|---|---|
| Publication | Approved | approved__c | true | approved__c = true |
| Publication | Approved | approved__c | false | approved__c = false |
Criteria VQL and Filter Expressions for Dynamic Reference Constraints must contain a valid field value token instead of a static field value. Tokens are in the format {{this.field__name}}.
The following applies to dynamic constraints for objects. For static reference constraints, see above. See the examples below.
| Description | Controlling Field Location | Field to Constrain (Controlled Field) | Relationship between Controlling and Controlled | Criteria VQL |
|---|---|---|---|---|
| Only show countries relevant for the selected region | Region, on the referring Object | Country, on the referring Object | Country has a reference field to region, that indicates the region in which a country belongs. Country and Region has a M:1 relationship. | region__v = {{this.region__v}} |
| Only show applications relevant for the region of the selected country | Country, on the referring object | Application, on the referring Object | Country has a reference field to region, that indicates the region in which a country belongs. Application has a reference to the region, indicating the region of the application. | region__v = {{this.country__vr.region__v}} |
| Only show applications relevant for the selected product | Product, on the referring object | Application, on the referring object | Product and Application objects have a M:M relationship and are related by the join Object product_application__v. | id IN (SELECT id FROM product_applications__rimr WHERE product__v = {{this.product__v}}) |
The following applies to dynamic constraints for documents. For static reference constraints, see above.
| Description | Controlling Field Location | Field to Constrain (Controlled Field) | Filter Expression |
|---|---|---|---|
| Only show indications relevant for the selected region | On the referring document | A document object reference field, indication | region__v CONTAINS {{this.region__v}} |
| Only show applications relevant for the region of the selected country | On the referenced object | A document object reference field, application | region__v CONTAINS {{this.document_country__vr.countries__vr.region__v}} |
| Only show applications relevant for the selected product | On an object related to the referenced object | A document object reference field, application | id IN (SELECT id FROM product_applications__rimr WHERE product__rim CONTAINS {{this.product__v}}) |
You can set up reference constraints using a VQL-type expression. However, reference constraints on document fields do not support full VQL functionality.
You can use the following standard VQL operators when defining static reference constraints: =, !=, >, <, >=, <=. Note that you cannot enter a space between characters in the following operators: !=, >=, <=.
To use the AND clause in your static reference constraint on a document field, you must use a comma (,).
For example:
id IN (SELECT id FROM countryproduct__cr WHERE country__c CONTAINS {{this.country__v}}, state__v = ‘approved_state__c’
Learn more about Criteria VQL Operators below.
When working with integration rules, Vault Admins can define Query Object Rules which are additional operations to perform against field rules. While field rules provide the SELECT portion of a query, query object rules provide the WHERE. To do this, a Vault Admin enters Criteria VQL as the Filter Clause for the query object rule. For example, a field rule can select the Country field on Product object records. By defining your query object rule’s Filter Clause as WHERE status__v = 'active__v', you can filter for object records where the Status is Active.
Filter clauses support the following operators in addition to the standard Criteria VQL Operators:
| Name | Description |
|---|---|
IN | Determines whether or not a value is “in” the list of values provided after the IN operator. Can only be used for inner join relationship queries on documents and objects. |
LIKE | Used with the wildcard character % to search for matching field values when you don’t know the entire value. Note that VQL does not support fields which begin with a wildcard. |
OR | Returns results when any of the values are true. |
To check your syntax, use the Validate link in the Vault UI. This link only validates the following:
number_field__c ='custom String text' is valid VQL syntax, but this would fail at runtime if number_field__c is a Number type field, as Number fields cannot accept String values.The following are examples of Filter Clauses in object query rules:
| Description | Filter Clause |
|---|---|
| Only query for objects (or documents) which are in the Complete lifecycle stage and have an Active Status. | stage__v ='complete__c' AND status__v = 'active__v' |
If your object’s page layout is configured to display related sections, you can filter the items that users can select in these sections with Criteria VQL. Learn more about page layouts for related sections in Vault Help.
Related sections also support dynamic tokens, such as {{this.field__c}}. Note that dynamic tokens only support the = and != comparison operators. For example, max_dosage__c = {{this.dosage__c}} is supported, while a comparison such as max_dosage__c > {{this.dosage__c}} is not supported.
In addition to the standard Criteria VQL operators, related sections also support the following operators:
| Name | Description |
|---|---|
LIKE | Used with the wildcard character % to search for matching field values when you don’t know the entire value. Note that VQL does not support fields which begin with a wildcard. |
Criteria VQL for related object sections also support the following unique functions:
| Name | Description |
|---|---|
{{IN_LAST(numberOfDays)}} | Used to specify a date field that falls between the current date and a number of days beforehand. Dates are inclusive. For example, created_date__v {{IN_LAST(7)}} queries for object records created in the last seven days. |
{{IN_NEXT(numberOfDays)}} | Used to specify a date field that falls between the current date and a number of days afterwards. Dates are inclusive. For example, expiration_date__v {{IN_NEXT(7)}} queries for object records expiring in the next seven days. |
Learn more about token selection in Vault Help.
Note that these functions cannot be included in a Criteria VQL statement which also contains dynamic tokens. For example, the following Criteria VQL is valid:
name__v = {{this.name__v}}created_date__v {{IN_LAST(7)}}While the following Criteria VQL is not valid:
name__v = {{this.name__v}} AND created_date__v {{IN_LAST(7)}}You can check your criteria VQL syntax with the Validate link.
Criteria VQL supports the following comparison operators:
| Operator | Description |
|---|---|
= | Equal to |
!= | Not equal to |
< | Less than |
> | Greater than |
<= | Less than or equal to |
>= | Greater than or equal to |
status__v = 'active__v'
study_status__v != 'Not Started'
created_date__v > '2014-12-20'
The AND operator returns results if the first and second expression are both true.
therapeutic_area__vs = 'cardiology__vs' AND therapeutic_area__vs = 'hematology__vs'
Parentheses can be used to enclose searches.
therapeutic_area__vs = 'neurology__vs' AND (therapeutic_area__vs = 'cardiology__vs' OR therapeutic_area__vs = 'hematology__vs')
The CONTAINS operator is used with parentheses to enclose multiple values.
therapeutic_area__vs CONTAINS ('hematology__vs','cardiology__vs')
The BETWEEN operator is used with AND to compare data between two values.
created_date__v BETWEEN '2014-10-15' AND '2014-04-20'
The following logical operators are not supported in Criteria VQL: NOT, AND NOT, OR NOT, FIND.