Introduction to Vault Queries

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'

Sending Queries

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:

Query

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/v14.0/query"

Response

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "limit": 1000,
        "offset": 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.

Getting Started

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.

Searching Documents

Get Queryable Fields

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:

Request

$ curl -X GET -H "Authorization: {SESSION_ID}" \
https://myvault.veevavault.com/api/18.3/metadata/objects/documents/properties

Response

{
    "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
        }
    ]
}

Query Documents

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.

Request

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/18.3/query"

Response

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "limit": 1000,
        "offset": 0,
        "size": 96,
        "total": 96
    },
    "data": [
        {
            "id": 1,
            "name__v": "Cholecap Brochure"
        },
        {
            "id": 2,
            "name__v": "New VeevaRX Logo"
        }
    ]
}

Searching Document Versions

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:

• All versions of a document
• The latest version that matches a specific search criteria, (even if it’s not the absolute latest version)
• All steady state versions (even if the latest version is not in a steady state)

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

Query the Document by Name

First, we’ll query the WonderDrug Information document using the WHERE filter on the document name:

Query

SELECT id, minor_version_number__v, major_version_number__v 
FROM documents 
WHERE name__v = 'WonderDrug Information'

Response

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "limit": 1000,
        "offset": 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.

Find Keywords in Content

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).

Query

SELECT id, minor_version_number__v, major_version_number__v 
FROM documents 
FIND 'Contraindications OR Dosage OR Prescribing' SCOPE content

Response

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "find": "Contraindications OR Dosage",
        "limit": 1000,
        "offset": 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.

Find Keywords Across Versions

Occasionally, you may want to include past versions in your document search. To do this, we’ll add ALLVERSIONS to the FROM clause.

Query

SELECT id, minor_version_number__v, major_version_number__v 
FROM ALLVERSIONS documents 
FIND 'Dosage' SCOPE content

Response

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "find": "Dosage",
        "limit": 1000,
        "offset": 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.

Find the Latest Matching 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:

Query

SELECT LATESTVERSION id, minor_version_number__v, major_version_number__v 
FROM ALLVERSIONS documents 
FIND 'Dosage' SCOPE content

Response

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "find": "Dosage",
        "limit": 1000,
        "offset": 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

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()

Search for Steady State Documents

This query searches all document versions for those with “Insulin” in the content, then filters to find the latest steady state version.

Query

SELECT LATESTVERSION id, minor_version_number__v, major_version_number__v 
FROM ALLVERSIONS documents 
FIND 'Insulin' SCOPE content 
WHERE status__v = steadystate()

Response

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "find": "Insulin",
        "limit": 1000,
        "offset": 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.

Other Keywords

If we performed the same query as above, substituting “Insulin” for:

• Contraindication: No results. This keyword doesn’t exist in any steady state versions.
• Dosage: No results. This keyword doesn’t exist in the latest steady state version. However, removing LATESTVERSION from the SELECT clause and keeping ALLVERSIONS in the FROM clause would find version 1.0.
• Prescribing: Same results as “Insulin”. This keyword exists in the latest steady state version (2.0).
• Formulary: No results. This keyword doesn’t exist in any steady state versions.

Search for Superseded Documents

This query searches all document versions for those with “Insulin” in the content, then filters to find the latest superseded state version.

Query

SELECT LATESTVERSION id, minor_version_number__v, major_version_number__v 
FROM ALLVERSIONS documents  
FIND 'Insulin' SCOPE content 
WHERE status__v = supersededstate()

Response

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "find": "Insulin",
        "limit": 1000,
        "offset": 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.

Search for Obsolete Documents

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.

Query

SELECT id, minor_version_number__v, major_version_number__v 
FROM ALLVERSIONS documents  
FIND 'Insulin' SCOPE content 
WHERE status__v = obsoletestate()

Response

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "limit": 1000,
        "offset": 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
        }
    ]
}

Find Document’s Current Status

Let’s query the same document to retrieve its current status:

Query

SELECT id, name__v, status__v 
FROM documents 
WHERE id = 534

Response

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "limit": 1000,
        "offset": 0,
        "size": 1,
        "total": 1
    },
    "data": [
        {
            "id": 534,
            "name__v": "WonderDrug Information",
            "status__v": "Obsolete"
        }
    ]
}

Searching Archived Documents

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.

Find All Archived Documents

To find all archived documents:

Query

SELECT id, name__v 
FROM archived_documents

Like other document queries, searching archived documents defaults to LATESTVERSIONS.

Standards & Specifications

The following standards and specifications apply to Vault queries.

Case Sensitivity

VQL Syntax

• VQL grammar is not case sensitive. You may enter clauses such as SELECT, FROM, and WHERE in upper or lowercase.
• This guide uses uppercase for all VQL grammar.

Field & Object Names

• Field and object names in the query string are case sensitive.
• You must enter field and object names such as name__v, documents, and product__v in lower-case.
• This does not apply to the id field name on documents (only), which may be entered as id or ID.
• Older API versions include some mixed-case field names. Retrieve field names to confirm correct formatting.

Field Values

• By default, all field values are case-sensitive. This applies to all field types. For example: using the filter name__v = 'cholecap' (where name__v is a String field type) returns no results if the field value is Cholecap.
• Using the filter therapeutic_area__vs = 'Hematology__v' (where therapeutic_area__vs is a Picklist field type) returns no results if the field value is hematology__v.

Bypassing Field Value Case-Sensitivity

• As of API v14.0, you can use the caseinsensitive({FIELD_NAME__V}) to bypass case-sensitivity. For example:
• Using the filter caseinsensitive(name__v) = 'cholecap' returns results even if the value is Cholecap.
• Learn more about performing case-insensitive queries.

Languages

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

Search Term Tokenization

• When performing keyword searches using API v8.0 or earlier, Vault automatically tokenizes CamelCase, alphanumeric, and delimited strings.
• As of API v9.0, Vault only tokenizes alphanumeric strings. To enable tokenization of CamelCase and delimited strings, set the tokenize request parameter to true.
• Learn more about search term tokenization.

Date & Time Formats

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

Maximum Query String Length

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

Maximum Number of Records Returned

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

Limiting the Number of Records Returned

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

Paginating Results

• When the number of results found exceeds the number displayed per page, use the OFFSET operator to display the next and previous pages of results.
• As of API v10.0, document and Vault Object queries include the next_page and previous_page URL endpoints when pagination is required. Learn more about paginating results.

Sorting/Ordering Results

• Results are returned in default order. To enforce a specific order, use the ORDER BY operator.
• The API supports primary and secondary sorting of fields in ascending/descending order. Learn more about Order By.
• The API also supports sorting results by relevance to a FIND operator search phrase using the ORDER BY rank operator. Learn more about Order By rank.

Response Formats

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

API Transaction Limits

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

User Permissions

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

About Vault Objects and Fields

Vault Objects are divided into the following categories:

Standard Vault Objects

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

Vault System Objects

• These include user__sys, rendition_type__sys, group_sys, and others.
• These are available for all Vault applications and configurations.
• The names of these objects always ends in __sys. For example, to query Groups, enter group__sys.

Custom Vault Objects

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

Vault Component Objects

• Vault Admins can query Vault Component Object like any other Vault Object
• These include Vault Components such as accountmessage__sys, signaturepage__sys, objecttype__sys, and others.
• Not all components are available for query.

Other Objects

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

Unsupported Objects

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.

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

The following objects are currently not queryable:

• Roles roles
• Lifecycles lifecycle__v
• Picklists picklists
• Security Policies securitypolicies

Fields Available for Query

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

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

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

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

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

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

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

LongText Field Type

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

Wildcard Exceptions

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)

Formula Fields

A formula field calculates field value based on a formula entered during field configuration. VQL allows you to query formula fields.

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.

Currency Fields

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 returns the numeric value like any other numeric field. 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'

Syntax & Structure

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

LIMIT {number}

OFFSET {number}

VQL Clauses

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.
LIMIT	Limits the number of query results per page.
OFFSET	When the query results found exceeds the number displayed per page, use OFFSET to display results in multiple pages.

SELECT & FROM Statements

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

Syntax

SELECT {fields} FROM {object}

Query

The following query returns the IDs, names, and status from all documents.

SELECT id, name__v, status__v
FROM documents

Response

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "limit": 1000,
        "offset": 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"
        }
  ]
}

WHERE Clause

Use the WHERE clause in VQL as a search filter to narrow and retrieve results that meet a specified condition.

• The WHERE filter can include a variety of “operators”, allowing you to further refine query results. Learn more about operators below.
• Fields vary depending on the document or object being queried.
• Unless otherwise noted, all fields that can be used in SELECT can also be used in WHERE.

Syntax

SELECT {fields} WHERE {field}{operator}{value}

Query

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'

Response

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "limit": 1000,
        "offset": 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"
        }
    ]
}

Comparison Operators

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.
(>=)	SELECT {fields} FROM {object} WHERE {field} >= {'value'}	Field values are greater than or equal to the specified value.

Equal to (=)

SELECT id, user_name__v, security_profile__v
FROM users
WHERE user_locale__v = 'es_US'

Not Equal to (!=)

SELECT workflow_id__v, workflow_name__v
FROM workflows
WHERE workflow_status__v != 'Cancelled'

Less than (<)

SELECT id, document_number__v
FROM documents
WHERE document_creation_date__v < '2016-04-23'

Greater than (>)

SELECT id, site_status__v, location__v
FROM site__v
WHERE modified_date__v > '2016-04-23'

Less than or Equal to (<=)

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

Standard Operators

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.
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.

AND

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'

OR

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.

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'

CONTAINS

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 workflow queries.

BETWEEN

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'

LIKE

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.

IN

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.

Using Date & DateTime Values

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

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

When using date formats, the time is assumed to be “00:00:00” (midnight of the specified date). For example, 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'

Performing Case-Insensitive Queries

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

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

As of API v14.0, Vault allows you to perform “case-insensitive” queries by using the caseinsensitive({field_name}) parameter in the WHERE filter. 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:

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

Using True/False & Null Values

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

SELECT id, name__v 
FROM documents 
WHERE crosslink__v = TRUE

SELECT id, name__v 
FROM documents 
WHERE locked__v = FALSE

Null (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

FIND

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.

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

Using FIND on Documents

• FIND for documents is available in API version v8.0+
• Document names and field names must be lower-case. The search phrase is not case-sensitive.

Syntax

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

Basic FIND Query

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

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

Multiple Keywords Query

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

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

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

All Keywords Query

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')

Exact Match Query

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"')

Using FIND on Vault Objects

• FIND for Vault objects is available in API version v14.0+
• Object names and field names must be lower-case. The search phrase is not case-sensitive.

Syntax

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

Query

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

Response

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "find": "('phosphate')",
        "limit": 1000,
        "offset": 0,
        "size": 1,
        "total": 1
    },
    "data": [
        {
            "id": "00P000000000205",
            "name__v": "Nyaxa",
            "status__v": [
                "active__v"
            ]
        }
    ]
}

SCOPE CONTENT

Use SCOPE CONTENT with FIND to search within document content. SCOPE CONTENT is available in v8.0+.

Syntax

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

Query

The following statement would return documents with the keyword “insulin” within the content.

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

SCOPE ALL

Use SCOPE ALL with FIND to search document fields and within document content. SCOPE ALL is available in v8.0+.

Syntax

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

Query

The example query below searches document content and all queryable fields.

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

SCOPE

Query (Documents)

The following statement searches within a specific document field. Note that you can only include one text/string-type or exactmatchstring-type document field.

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

SCOPE combined with SCOPE CONTENT

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

SCOPE

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__v
• subtype__v
• classification__v
• lifecycle__v
• status__v
  
• filename__v

Syntax

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

Query (Vault Objects)

The following statement searches within a specific object field:

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

Example Query using WHERE

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'

Using the Wildcard Character

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’:

Query

SELECT id, name__v
FROM documents
FIND ('ins* dia* glu*')

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

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

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

• FIND ('glu') is the same as FIND ('glu*') and will return all words starting with 'glu’. In this case, the wildcard character is not needed.

You can use the wildcard character when searching document content. For example, the following returns documents containing words ending in 'ology’ in the document content:

Query

SELECT id, name__v
FIND ('*ology' SCOPE CONTENT)

About Search Term Tokenization

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

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

To disable tokenization, set the tokenize request parameter to false.

Query

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

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

Query

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

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

ORDER BY

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+.

Syntax

SELECT {fields} FROM documents ORDER BY {field} ASC|DESC

Query (Ascending Order)

This following query returns document IDs in ascending order (1, 2, 3, etc.)

SELECT id, name__v
FROM documents
ORDER BY id ASC

Response

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "limit": 1000,
        "offset": 0,
        "size": 54,
        "total": 54
    },
    "data": [
        {
            "id": 1,
            "name__v": "Binders v10 Video"
        },
        {
            "id": 2,
            "name__v": "PowerPoints 18R1"
        },
        {
            "id": 3,
            "name__v": "Video Script Creating Tabular Reports"
        }
   ]
}

Query (Descending Order)

This query returns document names in descending order (Z, Y, X, etc.)

SELECT id, name__v
FROM documents
ORDER BY name__v DESC

Response

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "limit": 1000,
        "offset": 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:

Query

SELECT name__v, type__v
FROM documents
ORDER BY type__v DESC, name__v DESC

Response

The response includes results sorted first by type and then by name, both in descending order.

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "limit": 1000,
        "offset": 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"
        }
   ]
}

ORDER BY rank

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

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.

Query

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

Response

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "limit": 1000,
        "offset": 0,
        "size": 54,
        "total": 54
    },
    "data": [
        {
            "id": 26,
            "name__v": "Document ABC"
        },
        {
            "id": 44,
            "name__v": "Document ABCD"
        },
        {
            "id": 4,
            "name__v": "Document ABCDE"
        }
  ]
}

LIMIT

Use the LIMIT clause to limit the number of results returned per page.

• By default, the maximum number of results displayed per page is 1000 for documents and 1000 for objects.
• Using LIMIT does not change the total number of results found, only the number displayed per page.

Syntax

SELECT {fields} FROM documents LIMIT {number}

The following query returns 25 documents per page.

Query

SELECT
id
FROM documents LIMIT 25

Additional Details

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.

OFFSET

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

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

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

Response

{
    "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"

Response Details

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

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

Notes:

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

Vault Document Functions

You can use special document functions to retrieve specific versions of documents or documents in a specific state.

Document Functions

Name	Syntax	Description
STEADYTATE()	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.

STEADYSTATE() Query

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

OBSOLETESTATE() Query

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

SUPERSEDEDSTATE() Query

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

LATESTVERSION Query

SELECT LATESTVERSION id, minor_version_number__v
FROM ALLVERSIONS documents

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

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

Querying Vault Binders

Vault allows you use the binders and binder_node__sys objects to query binders for the following:

• Given a document, or a set of documents, find binders which contain them
• Given a binder, or a set of binders, find documents contained with them

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

Binder Relationships

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.

Binder Node Queryable Fields

Note: 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.

Binder Query Examples

The following are examples of standard binder queries:

Simple Binder Query

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()

Binder Query for Specific Documents

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'

Query Documents Within a Specific Section

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'

Query Binders and Sections Containing Documents

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'

Querying Vault Renditions

Vault allows you use the renditions object to query rendition properties for a document and document versions.

Renditions Queryable Fields

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.

Rendition Query Examples

The following are examples of queries for document and rendition properties.

Properties from a Collection of Documents

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)

Properties for Steady State Version Documents

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)

Custom Properties for Steady State Version Documents

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)

Particular Rendition Type

Query for Renditions of a particular type.

SELECT document_id, size_v, upload_date__sys 
FROM renditions 
WHERE rendition_type__sys = 'insight_rendition__c'

Querying Vault Groups

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

User & Group Relationships

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 object
• group__sysr relationship to the group__sys object

Group Queryable Fields

Note: 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.

Group Membership Queryable Fields

Note: 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.

Group Query Examples

The following are examples of standard group queries:

Simple Group Query

Find all user-managed groups:

SELECT id, name__v, label__v, type__sys
FROM group__sys
WHERE type__sys = 'user_managed__v'

Simple Group Membership Query

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

Users-to-Groups Query

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'

Groups-to-Users Query

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'

Relationship Queries (Joins)

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

Introduction to Vault Relationship Queries

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.

Requirements & Limitations

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:

• Create a join relationship between the main object and a secondary object in a nested query.
• Create an inner join to exclude records from the main object which doesn’t have a match in the nested query.

Object to Object Relationships

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.

Left Outer Join - Parent to Child (1:M)

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.

Inner Join - Parent to Child (1:M)

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).

Inner Join - Child to Parent (M:1)

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.

Lookup

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.

Vault Document Relationships: Document to Object / Object to Document

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.

Left Outer Join - Document to Product (M:M)

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

Left Outer Join - Product to Document (M:M)

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.

Finding Vault Relationships

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:

• Document Metadata API: /api/{version}/metadata/objects/documents/properties
• Object Metadata API: /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

• This states that the field is referencing an object.
• The field is not referencing a “string”, “number”, “picklist”, “Boolean”, etc.

objectType : vault_object__v

• The object being referenced by the field must be a Vault Object.
• This includes object__v for standard objects or object__c for custom objects, e.g., product__v, study__v, custom_object__c, etc.
• Vault Objects do not include users, groups, workflows, events, or document relationships.

relationshipType : reference, reference_inbound, reference_outbound, parent, or child.

• The type of relationship defines how the query must be structured, e.g., Left Outer Join, Inner Join, Lookup, etc.

relationshipName : relationship_name__vr for standard objects or relationship_name__cr for custom objects.

• This is the relationship name to use in relationship queries, e.g., study_number__vr, sites__vr, document_product__vr, etc.

Finding Document Relationships: Document to Object / Object to Document

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:

• The product__v relationship field has the relationship name: document_product__vr
• The country__v relationship field has the relationship name: document_country__vr
• The custom_object__c relationship has the relationship name: document_custom_object__cr

You 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.

Many-to-Many Relationship Queries

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.

Part 1: Creating Many-to-Many Relationships between Objects

• This section describes how to create a new join object (via the Admin UI) with fields referencing two parent objects. This creates a many-to-many relationship between the objects.
• This section also describes how to retrieve the inbound and outbound relationship metadata (via the API) and understand their use. These relationships form the basis of the example queries in this article.
• If you’ve already set up many-to-many relationships between objects and understand the concepts, you can skip this section and proceed to Part 2 below to begin building queries.
• For information about many-to-many relationships between documents and objects, refer to Part 3 below.

Create a Many-to-Many Relationship between Two Parents and a Child (Join) Object

1. Create a new custom object (approved_country__c). This must be done in the Admin UI. Learn how.
2. Create a new custom field (product_field__c) on the new object. Set the “Field Type” to “Parent Object” referencing the standard object product__v.
3. Create a new custom field (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.

Join Object Relationship Metadata: Approved Country (Child)

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
      },

Parent Object Relationship Metadata: Product (Parent 1)

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"
        }
      },

Parent Object Relationship Metadata: Country (Parent 2)

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"
        }
      },

Part 2: Traversing Many-to-Many Relationships between Objects

Once you’ve set up relationships between the join object and two parent objects (described in Part 1 above), you can start building queries.

Query 1: Parent Object 1 to Child (Join) Object to Parent Object 2

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.

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:

1. Retrieve the id and name__v from all products. The product__v (Parent 1) object is the “driver object” of this query.
2. Use the 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.
3. Use the 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.

Response

{
  "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"
          }
        ]
      }
    },

Query 2: Parent Object 2 to Child (Join) Object to Parent Object 1

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.

Query

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:

1. Retrieve the id and name__v from all countries. The country__v (Parent 2) object is the “driver object” of this query.
2. Use the 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.
3. Use the 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.

Response

{
  "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"
          }
        ]
      }
    },

Part 3: Creating Many-to-Many Relationships between Documents and Objects

• This section describes how to create a new document object relationship field (via the Admin UI) which references the join object created in Part 1 above.
• This creates a many-to-many relationship between documents and the object.
• This section also describes how to retrieve the relationship metadata (via the API) and understand its use. This relationship forms the basis of the example queries in this article.
• If you’ve already set up many-to-many relationships between documents and a join object and understand the concepts, you can skip this section and proceed to Part 4 below to begin building queries.

To create a many-to-many relationship between documents and a join object:

1. Create a new document field (approved_country__c). This must be done in the Admin UI. Learn how.
2. Set the “Field Type” to “Object” referencing the join object approved_country__c, which is a child of both the product__v and country__v fields.
3. Leave the document field set to allow only one value.

Document Object Relationship Metadata

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"
        },

Part 4: Traversing Many-to-Many Relationships between Documents and Objects

Once you’ve set up relationships between documents and the join object (described in Part 3 above), you can start building queries.

Query 3: Documents to Child (Join) Object to Parent Objects

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.

Query

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:

1. Retrieve the id, name__v, and type__v from all documents. The documents object is the “driver object” of this query.
2. Use the 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.
3. Use the 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.
4. Use the 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.

Response

{
  "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"
          }
        ]
      }
    },

Query 4: Child (Join) Object to Documents and Parent Objects

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.

Query

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:

1. Retrieve the 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.
2. Use dot-notation on the 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.
3. Use dot-notation on the 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.
4. Use the document_approved_country__cr relationship in a subselect to retrieve the id, name__v, and type__v from the documents object.

Response

{
  "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"
          }
        ]
      }
    }
  ]
}

Criteria VQL

Vault Query Language (VQL) is a SQL-like language which allows you to query information in Vault. This article provides detailed information for Vault Admins entering Criteria VQL when configuring:

• Rule Criteria for Dynamic Access Control on Objects. Learn more in Vault Help.
• Constrain Records in Referenced Object - Static. Learn more in Vault Help.
• Constrain Records in Referenced Object - Dynamic. Learn more in Vault Help.

The image below shows the Dynamic Security Rule Criteria in the Vault UI.

Use the “token” button to the right of the Criteria VQL text box to search for available tokens on an object. String field values are case-sensitive.

Dynamic Access Control & Static Reference Constraints

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.

ID Fields

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.

Text (String) Fields

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 Fields

Picklist behavior varies slightly between Documents, Objects, and Workflows.

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.

Documents

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'.

Objects

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'

Object Lookup Fields

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.

• For standard objects (vault_object__v), the lookup name is vault_object__vr.name__v.
  
• For custom objects (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.

Date & DateTime Fields

All Dates and DateTimes are entered and returned in UTC (Coordinated Universal Time) and not the user’s time zone.

• Dates formats are YYYY-MM-DD (‘2014-12-20’)
• DateTime formats are YYYY-MM-DD’T'HH:MM:SS.SSS'Z’ ('2014-12-20T15:30:00.000Z’)

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

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

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

Dynamic Reference Constraints

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}}.

Object Reference Constraints

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}})

Document Reference Constraints

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}})

Reference Constraints Expression Limitations

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.

Criteria VQL Operators

Comparison Operators

Criteria VQL supports the following comparison operators: = (equal to), != (not equal to), < (less than), > (greater than), <= (less than or equal to), and >= (greater than or equal to).

status__v = 'active__v'
study_status__v != 'Not Started'
created_date__v > '2014-12-20'

Logical Operators

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' 

The OR operator returns results if either expression is true.

therapeutic_area__vs = 'cardiology__vs' OR 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: NOT, AND NOT, OR NOT.