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, outlining 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 statement also specifies the fields to retrieve in the SELECT clause and any optional filters (in the WHERE or FIND clause) to narrow your results:

SELECT {one field or comma-separated list of field names}
FROM {query target}
WHERE {optional search filters to narrow resulting data}

The following example query returns the id and name from all documents where the type is Promotional Piece:

SELECT id, name__v
FROM documents
WHERE type__v = 'Promotional Piece'

To start learning about VQL syntax, how to structure queries, and how to retrieve fields from a single object such as documents or product__v, see Getting Started.

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.

Conventions

The conventions in this table should be modified or entered as shown when creating a VQL query.

Convention Description Examples
SELECT An element in all caps is a keyword such as a logical operator, query target option, or function. SELECT id
FROM LATESTVERSION documents
documents An element without parentheses or brackets is a field name, object name, or value. Enter this element exactly as shown.

Boolean values (true or false) and null must be in lowercase or you may encounter errors.		 SELECT id
FROM documents
WHERE name__v = 'USA'

SELECT id
FROM documents

WHERE locked__v = true
{text} Curly braces indicate a variable. Replace both the curly braces and variable text with a value. SELECT {field}
FROM {query target}
SELECT name__v
FROM product__v
'{text}' Single quotation marks surrounding a variable indicate a string value. Replace the curly braces and variable text with a value inside the single quotation marks. WHERE {field} = '{value}'

WHERE name__v = 'USA'
FUNCTION() An element in all caps with parentheses is a function.

For readability, the function name may not indicate whether a parameter is required. Refer to syntax information for the required parameter.		 The STATETYPE() function

WHERE state__v = STATETYPE('{state_type_name}')
| The pipe character separates alternative elements. Enter only one of these elements. ORDER BY id ASC|DESC

Terminology

This table defines commonly used terms in the VQL reference.

Convention Description Examples
Clause A clause controls the source, filtering, and presentation of retrieved data. FROM documents

ORDER BY id ASC
SELECT statement The SELECT statement retrieves records from Vault. It must contain the SELECT and FROM clauses and may contain additional clauses. SELECT id
FROM documents

SELECT id
FROM documents
WHERE name__v = 'USA'
Expression An expression evaluates to a single value. A logical expression evaluates to a boolean value. created_date__v > '2014-12-20'
Logical operator A logical operator is used in a WHERE or FIND clause to build a logical expression. AND

IN

CONTAINS
Comparison operator A comparison operator is used in a WHERE or FIND clause to create a logical expression that compares two values. <=

!=
Function A function returns a value. It either retrieves or modifies the specified field value. CASEINSENSITIVE(name__v)
Query target A query target is a collection of Vault data that supports VQL queries.
The object in a VQL query may be a Vault object or another query target such as documents or users.		 SELECT {field}
FROM {query target}

SELECT id FROM product__v

SELECT id
FROM users
Query target option A query target option filters or modifies the queryable field, object, or set of results that follow it. LATESTVERSION name__v
Vault object A Vault object is part of the application data model, such as Product, Country, or Study, and can be used as a query target. product__v

Getting Started

This tutorial covers the basics of structuring and submitting a query and how to use VQL to retrieve document and object fields.

Structuring a Query

VQL’s syntax is similar to SQL and provides a programmatic way of searching your Vault’s data. A basic VQL query consists of a SELECT statement with a FROM clause:

SELECT {fields}
FROM {query target}

The SELECT clause provides the fields to retrieve, such as id. The FROM clause provides the query target, such as documents.

Sending a Query

To send a VQL query, use the /api/{version}/query endpoint in the REST API Reference.

Query

The following example sends a query that retrieves the ID and name of all documents in the specified Vault:

curl -X POST -H "Authorization: {session_id}" \
-H "Content-Type: application/x-www-form-urlencoded" \
-d 'q=select id, name__v from documents' \
"https://myvault.veevavault.com/api/v20.3/query"

Response

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "pagesize": 1000,
        "pageoffset": 0,
        "size": 96,
        "total": 96
    },
    "data": [
        {
            "id": 119,
            "name__v": "Test File 1"
        },
        {
            "id": 1,
            "name__v": "Test File 2"
        }
    ]
}

Querying Documents

To retrieve data from documents in your Vault, use the documents query target.

To add fields to a query, enter the field’s name attribute in the SELECT clause. For example, to retrieve the Name document field, enter name__v. You can get the name attribute of queryable fields using the metadata API.

Query

The following query retrieves the IDs and names of all documents in your Vault:

SELECT id, name__v
FROM documents

Response

The response provides the response status, the number of results, pagination information, and the requested Vault data. You can also include metadata on the query target and any included fields by requesting the queryDescribe object.

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "pagesize": 1000,
        "pageoffset": 0,
        "size": 3,
        "total": 3
    },
    "data": [
        {
            "id": 26,
            "name__v": "Verntorvastatin Batch Manufacturing Record"
        },
        {
            "id": 6,
            "name__v": "Cholecap-Logo"
        },
        {
            "id": 5,
            "name__v": "CholeCap Visual Aid"
        }
    ]
}

Query: Retrieve a Document by Name

To filter and refine your search, you can add clauses, functions, and query target options to a SELECT statement.

The following query uses the WHERE clause to retrieve the IDs and names of all documents named “WonderDrug Information”:

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

Response: Retrieve a Document by Name

The following response shows that there is only one (1) document named “WonderDrug Information”:

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "pagesize": 1000,
        "pageoffset": 0,
        "size": 1,
        "total": 1
    },
    "data": [
        {
            "id": 534,
            "name__v": "WonderDrug Information" 
        }
    ]
}

Querying Objects

To retrieve object record data from your Vault, use the object’s name attribute. For example, to query the Product object, enter product__v as the query target.

Query

The following query retrieves the IDs and names of all Product object records in your Vault:

SELECT id, name__v
FROM product__v

Response

This response includes seven (7) Product object records:

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "pagesize": 1000,
        "pageoffset": 0,
        "size": 7,
        "total": 7
    },
    "data": [
        {
            "id": "00P000000000301",
            "name__v": "Nyaxa"
        },
        {
            "id": "00P000000000302",
            "name__v": "Focusin"
        },
        {
            "id": "00P000000000303",
            "name__v": "Lexipalene"
        },
        {
            "id": "00P000000000304",
            "name__v": "Pazofinil"
        },
        {
            "id": "00P000000000401",
            "name__v": "Vetreon"
        },
        {
            "id": "00P000000000A01",
            "name__v": "Gludacta"
        },
        {
            "id": "00P000000000E01",
            "name__v": "Wonderdrug"
        }
    ]
}

Query Describe

When you set the X-VaultAPI-DescribeQuery header to true, the response includes the queryDescribe object. This object provides metadata on your query, such as the name and label of the query target and retrieved fields. Learn more in the REST API Reference.

For example, this response provides a description of a query that retrieves id and name__v from documents:

{
    "responseStatus": "SUCCESS",
    "queryDescribe": {
        "object": {
            "name": "documents",
            "label": "documents",
            "label_plural": "documents"
        },
        "fields": [
            {
                "type": "id",
                "required": true,
                "name": "id"
            },
            {
                "label": "Name",
                "type": "String",
                "required": true,
                "name": "name__v",
                "max_length": 100
            }
        ]
    },
    "responseDetails": {
        "pagesize": 1000,
        "pageoffset": 0,
        "size": 3,
        "total": 3
    },
    "data": [
        {
            "id": 26,
            "name__v": "Verntorvastatin Batch Manufacturing Record"
        },
        {
            "id": 6,
            "name__v": "Cholecap-Logo"
        },
        {
            "id": 5,
            "name__v": "CholeCap Visual Aid"
        }
    ]
}

Pagination & Performance

This section provides best practices for working with VQL and minimizing the computational expense of your queries.

Paginating Results

After you submit a query, the Vault REST API returns the first page of query results instead of all results at once. When the number of query results is greater than the page size, the response provides next_page and previous_page URLs for pagination. These URLs include information from the original query and allow you to paginate results with a POST request. We recommend using the next_page and previous_page URLs over manual pagination with PAGEOFFSET. Learn more about query performance with pagination.

The next_page and previous_page endpoints only remain active for 20 minutes after you submit the original query. After that, you must resubmit the original query.

When querying workflows (legacy), events, or users, the response does not include the next_page or previous_page URL endpoints and displays all results on one page. In these cases, submit additional queries using PAGEOFFSET to paginate over the result set. Learn more about paginating results with PAGEOFFSET.

Query Performance Best Practices

Some VQL queries can degrade Vault stability and performance, especially when you’re working with large datasets. This section provides workarounds for queries that are likely to be computationally expensive.

When testing your queries, the size of the datasets should always match the size in production.

Working with Large Datasets

Using VQL to query large datasets is likely to cause performance issues. For example:

To prevent these issues when working with large datasets, we recommend using the Scheduled Data Exports job instead of VQL. Learn more about the Scheduled Data Exports job in Vault Help.

Joining Datasets

Using a relationship query to join datasets may take a long time to complete. It is often more performant to use two separate queries.

For example, you could use the following computationally expensive query to join two large datasets:

SELECT id, (SELECT id, name__v FROM country__cr)
FROM product__v
WHERE country__cr.name__v = 'USA'

For better performance, split this example into two queries:

  1. Query the primary object:

    SELECT id, country__c
FROM product__v

  2. Query the secondary object to retrieve the related data:

    SELECT id, name__v
FROM country__v
WHERE name__v = 'USA'

You can then join the two sets of results outside of VQL.

Sorting Results

Using VQL’s ORDER BY clause to sort records can take a long time to complete. For large datasets, we recommend sorting records directly in your database.

In v23.1+, VQL returns a warning response for queries with ORDER BY that take longer than 5 minutes.

Paginating Results

Queries that use PAGEOFFSET to manually paginate results can be computationally expensive:

For better performance, use the previous_page and next_page response URLs to paginate over your results. Learn more about paginating VQL queries.

Duplicate Queries

Duplicate query strings executed within a short amount of time can cause performance issues. For better performance, we recommend caching frequently requested data.

In v23.1+, VQL detects and returns a warning for duplicate query strings executed within a fixed period of 5 minutes.

Unfiltered Queries

Queries that are unfiltered (missing the WHERE clause) can take a long time to complete. You can improve query performance by adding a WHERE clause to decrease the number of results.

In v23.1+, VQL returns a warning response for unfiltered queries that take longer than 5 minutes.

Leading Wildcard on FIND

A FIND search can take a long time to complete when a search term begins with the wildcard character *:

Clauses

The basic structure of a VQL query is a SELECT statement with a FROM clause. You can filter and sort the results with additional clauses such as WHERE and ORDER BY.

You can add the following clauses to a VQL query:

Name Syntax Description
SELECT SELECT {field} Select one or more fields to return from a specified object.
FROM FROM {query target} Specifies the object from which to return the fields in the SELECT statement.
WHERE WHERE {operator} {value} Use the WHERE clause to apply search filters and narrow results.
FIND FIND ('{search phrase}') Use FIND to search document or Vault object fields for specific keywords.
ORDER BY ORDER BY {field} ASC|DESC Sets the sort order of query results.
ORDER BY RANK FIND ('{search phrase}') ORDER BY RANK Order documents by their relevance to the FIND search phrase.
MAXROWS MAXROWS {number} Set the maximum number of results to retrieve.
SKIP SKIP {number} Set the number of results to skip.
PAGESIZE PAGESIZE {number} Limits the number of query results per page.
PAGEOFFSET PAGEOFFSET {number} When the query results found exceeds the number displayed per page, use PAGEOFFSET to display results in multiple pages.
AS AS {alias} Define an optional alias when using a function on a field in the SELECT statement. Not all fields support aliases.

SELECT & FROM

The SELECT and FROM clauses form the basis of a VQL query and are both required. The SELECT clause specifies the fields to retrieve. The FROM clause indicates the query target object from which to retrieve those fields.

Syntax

SELECT {fields}
FROM {query target}

Functions & Options

You can use the following functions and query target options in the SELECT and FROM clauses:

Name Syntax Description API Version
ALLVERSIONS SELECT {field}
FROM ALLVERSIONS documents		 Retrieve fields from all document versions. Only available when querying documents. v8.0+
FAVORITES SELECT {fields}
FROM FAVORITES documents		 Retrieve fields from documents the currently authenticated user has marked as a favorite. v22.2+
LATESTVERSION SELECT LATESTVERSION {field}
FROM ALLVERSIONS documents		 Retrieve fields from the latest version of all documents. Only available when querying documents. v8.0+
LONGTEXT() SELECT LONGTEXT({field})
FROM {query target}		 Retrieve the full value of Long Text fields in the SELECT clause. v17.1+
RECENT SELECT {fields}
FROM RECENT documents		 Filter results to the 20 documents the currently authenticated user has viewed most recently (in descending order by date). v22.2+
RICHTEXT() SELECT RICHTEXT({field})
FROM {query target}		 Retrieve the full value (with HTML markup) of Rich Text fields in the SELECT clause. v21.1+
TONAME() SELECT TONAME({field})
FROM {query target}		 Retrieve the field name instead of the label. Only available when querying documents. v20.3+

Query Examples

The following are examples of queries using SELECT and FROM.

Query: Retrieving All Documents

The following query returns the ID, name, and status of all documents in a Vault:

SELECT id, name__v, status__v
FROM documents

Response: Retrieving All Documents

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "pagesize": 1000,
        "pageoffset": 0,
        "size": 54,
        "total": 54
    },
    "data": [
        {
            "id": 68,
            "name__v": "Cholecap Akathisia Temporally associated with Adult Major Depressive Disorder",
            "status__v": "Draft"
        },
        {
            "id": 65,
            "name__v": "Gludacta Package Brochure",
            "status__v": "Approved"
        },
        {
            "id": 64,
            "name__v": "Gludacta Logo Light",
            "status__v": "Approved"
        }
  ]
}

WHERE

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

Syntax

SELECT {fields}
FROM {query target}
WHERE {field} {operator} {value}

Functions & Options

You can use the following functions and query target options in the WHERE clause:

Name Syntax Description API Version
CASEINSENSITIVE() WHERE CASEINSENSITIVE({field}) {operator} {value} Bypass case sensitivity of field values in the WHERE clause. v14.0+
DELETEDSTATE()
OBSOLETESTATE()
STEADYSTATE()
SUPERSEDEDSTATE()		 WHERE status__v = DELETEDSTATE()
WHERE status__v = OBSOLETESTATE()
WHERE status__v = STEADYSTATE()
WHERE status__v = SUPERSEDEDSTATE()		 Filter results to documents in a steady, obsolete, superseded, or deleted state (status__v). Only available when querying documents. v8.0+

DELETEDSTATE(): v19.2+
STATETYPE() WHERE state__v = STATETYPE('{state_type}') Retrieve object records with a specific object state type. v19.3+
TONAME() WHERE TONAME({field}) {operator} {value} Filter by the name of fields in the WHERE clause. Only available when querying documents. v20.3+

Operators

You can use all comparison operators and the following logical operators in the WHERE clause:

Name Syntax Description
AND WHERE {field_1} = {value_1} AND {field_2} = {value_2} Field values are equal to both specified values.
BETWEEN WHERE {field} BETWEEN {value_1} AND {value_2} Used with AND to compare data between two different values.
CONTAINS WHERE {field} CONTAINS ({value_1},{value_2},{value_3}) Field values are equal to any of the specified values.
IN WHERE {field} IN (SELECT {fields} FROM {query target}) The IN operator can only be used for inner join relationship queries on documents and objects.
LIKE WHERE {field_1} LIKE '{value%}' Used with the wildcard character % to search for matching field values when you don’t know the entire value.
OR 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.

Query Examples

The following are examples of queries using WHERE.

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": {
        "pagesize": 1000,
        "pageoffset": 0,
        "size": 6,
        "total": 6
    },
    "data": [
        {
            "id": 68,
            "name__v": "Cholecap Akathisia Temporally associated with Adult Major Depressive Disorder",
            "status__v": "Draft"
        },
        {
            "id": 65,
            "name__v": "Gludacta Package Brochure",
            "status__v": "Approved"
        },
        {
            "id": 64,
            "name__v": "Gludacta Logo Light",
            "status__v": "Approved"
        },
        {
            "id": 63,
            "name__v": "Gludacta Logo Dark",
            "status__v": "Approved"
        }
    ]
}

Query: Retrieve Documents by Date or DateTime Value

The following query returns the ID and name of all documents created after October 31, 2015. The value '2015-11-01' corresponds to November 1st, 2015 at midnight (00:00:00), so results will include documents created on November 1st at 00:00:01 or later. Learn more about Date and DateTime field values.

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

Query: Retrieve Products by Case-Insensitive Value

The following query returns results even if the field value is “Cholecap”, “choleCap”, or another case variation. Learn more about case sensitivity in VQL queries and the CASEINSENSITIVE() function.

SELECT id
FROM product__v
WHERE CASEINSENSITIVE(name__v) = 'cholecap'

Query: Retrieve Products by State Type

The following query returns all products in the Complete state. Learn more about the STATETYPE() function.

SELECT id
FROM product__v
WHERE state__v = STATETYPE('complete_state__sys')

Query: Retrieve Documents by Boolean Field Value

The following query returns all documents containing a Crosslink field with the value true. Learn more about using boolean values.

SELECT id, name__v
FROM documents
WHERE crosslink__v = true

Query: Retrieve Documents with Null Field Values

The following query returns all documents with no value in the External ID field. Learn more about using null values.

SELECT id, name__v
FROM documents
WHERE external_id__v = null

FIND

Use FIND to perform keyword searches on documents and Vault objects. When using FIND on documents, Vault searches all queryable document fields. All FIND statements must be enclosed in parentheses.

Syntax

SELECT {fields}
FROM {query target}
FIND ('{search phrase}')

The FIND search phrase must be enclosed in single quotation marks. The search phrase and scope must be enclosed in parentheses.

The search phrase is not case sensitive.

Functions & Options

You can use the following functions and query target options in the FIND clause:

Name Syntax Description API Version
SCOPE ALL FIND ('{search phrase}' SCOPE ALL) Search all fields and document content. v8.0+
SCOPE CONTENT FIND ('{search phrase}' SCOPE CONTENT) Search document content only. v8.0+
SCOPE {field} FIND ('{search phrase}' SCOPE {field}) Search a specific document or object field. v15.0+

Operators

You can use all comparison operators and the following logical operators in the FIND clause:

Name Syntax Description
AND FIND ('{value_1} AND {value_2}') Field values are equal to both specified values. If all terms in a search string are joined by the AND operator, all terms must match.
NOT FIND (NOT '{search phrase}') Field values do not match the specified search string.
OR FIND ('{value_1} OR {value_2}') Field values are equal to either specified value. If all terms in a search string are joined by the OR operator, at least one term must match.

Query Examples

The following are examples of queries using FIND.

Query: Find Documents with a Keyword Match

The following query returns all documents that contain the keyword insulin (case insensitive): 

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

Response: Find Documents with a Keyword Match

{
   "responseStatus": "SUCCESS",
   "responseDetails": {
       "find": "('insulin')",
       "pagesize": 1000,
       "pageoffset": 0,
       "size": 3,
       "total": 3
   },
   "data": [
       {
           "id": 200,
           "name__v": "Test Doc Nyaxa Insulin"
       },
       {
           "id": 198,
           "name__v": "Test Doc Diabetes Insulin"
       },
       {
           "id": 197,
           "name__v": "Test Doc Nyaxa Diabetes Insulin"
       }
   ]
}

Query: Find Objects with a Keyword Match

The following query returns the ID, compound ID, and abbreviation of all Product records that contain the keyword cc in one or more fields (case insensitive):

SELECT id, compound_id__c, abbreviation__c
FROM product__v
FIND ('cc')

Response: Find Objects with a Keyword Match

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
       "find": "('cc')",
       "pagesize": 1000,
       "pageoffset": 0,
       "size": 2,
       "total": 2
    },
    "data": [
       {
           "id": "00P000000000302",
           "compound_id__c": "CC-123",
           "abbreviation__c": "CC"
       },
       {
           "id": "00P000000000301",
           "compound_id__c": "CC-127",
           "abbreviation__c": null
       }
    ]
}

Query: Find Documents with Any Keyword

To search document fields containing any of the provided keywords, use the OR operator between them. The following query returns all documents with a field that contains the word insulin, diabetes, or both: 

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

This is equivalent to searching for ‘insulin diabetes’ using the Any of these words field in Vault UI’s Advanced Search.

Query: Find Documents with All Keywords

To search document fields containing all keywords, use the AND operator between each keyword. Vault requires all of the terms to match, but the terms do not need to match within the same field. The following query returns all documents with fields that contain the words insulin, diabetes, and Nyaxa, but does not include results matching only one (1) or two (2) of the terms: 

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

This is equivalent to searching for ‘diabetes insulin Nyaxa’ using the All of these words field in Vault UI’s Advanced Search.

Query: Find Documents with an Exact Keyword Match

To search for an exact match to all keywords, enclose the string in double quotes within the single quotes. The following query returns all documents with a field that contains the exact phrase blood sugar:

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

Query: Find Documents Using Strict Matching

A FIND search phrase without operators uses strict matching. The following query returns all documents where two (2) or three (3) keywords match. All matches must be within the same field.

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

Query: Find Documents with a Partial Keyword Match

The following query uses the wildcard character (*) to retrieve all documents with a partial keyword match for two (2) or three (3) of ins, dia, and glu:

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

Query: Find Documents Without a Keyword Match

Use NOT with FIND to exclude results from a query. The following query searches for all documents that do not contain the word insulin in any field:

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

Query: Find Documents Without a Keyword Match (Strict Matching)

When strict matching is enabled, NOT negates the entire search phrase. For example, the following query returns results containing pain and medication on their own but not pain medication:

SELECT id, name__v
FROM documents
FIND (NOT 'pain medication')

Query: Search Only Document Content

The following example uses the SCOPE CONTENT option to search document content for the keyword insulin:

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

Query: Filter Search Results with WHERE

When using FIND with or without SCOPE, you can use the WHERE filter to narrow results. WHERE must be placed after the FIND clause. The following query returns all product records with the generic_name__vs field set to “phosphate” and the therapeutic_area_vs field set to “cardiology__vs”:

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

ORDER BY

You can use the ORDER BY and ORDER BY RANK clauses to order the results returned from your Vault.

Ordering by Field

In v8.0+, use ORDER BY to control the order of query results. You can specify either ascending (ASC) or descending order (DESC).

VQL does not support sorting by reference objects such as product__v.name__v.

Syntax

SELECT {fields}
FROM {query target}
ORDER BY {field} ASC|DESC

Query Examples

The following are examples of queries using ORDER BY.

Query: Retrieve Documents in Ascending Order

This following query returns document IDs in ascending numerical order:

SELECT id, name__v
FROM documents
ORDER BY id ASC
Response: Retrieve Documents in Ascending Order
{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "pagesize": 1000,
        "pageoffset": 0,
        "size": 54,
        "total": 54
    },
    "data": [
        {
            "id": 1,
            "name__v": "Binders v10 Video"
        },
        {
            "id": 2,
            "name__v": "PowerPoints 20R3"
        },
        {
            "id": 3,
            "name__v": "Video Script Creating Tabular Reports"
        }
   ]
}
Query: Retrieve Documents in Descending Order

This query returns document names in descending alphabetical order:

SELECT id, name__v
FROM documents
ORDER BY name__v DESC
Response
{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "pagesize": 1000,
        "pageoffset": 0,
        "size": 54,
        "total": 54
    },
    "data": [
        {
            "id": 44,
            "name__v": "WonderDrug Research"
        },
        {
            "id": 26,
            "name__v": "Ways to Get Help"
        },
        {
            "id": 4,
            "name__v": "VeevaProm Information"
        },
        {
            "id": 7,
            "name__v": "Time-Release Medication"
        },
  ]
}
Query: Enforcing Primary and Secondary Order

You can enforce both the primary and secondary order of results by using a comma-separated string of field names. The field sort priority is left to right. For example, the following query returns results sorted first by type and then by name, both in descending order:

SELECT name__v, type__v
FROM documents
ORDER BY type__v DESC, name__v DESC
Response: Enforcing Primary and Secondary Order

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

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "pagesize": 1000,
        "pageoffset": 0,
        "size": 54,
        "total": 54
    },
    "data": [
        {
            "name__v": "VeevaProm Resource Doc",
            "type__v": "Resource"
        },
        {
            "name__v": "Nyaxa Resource Doc",
            "type__v": "Resource"
        },
        {
            "name__v": "CholeCap Logo",
            "type__v": "Promotional Material"
        }
   ]
}

Ordering by Rank

Use the ORDER BY RANK clause with FIND to sort documents by relevance to a search phrase. Doing so matches the default result ordering for the same search in the Vault UI.

Syntax

SELECT {fields}
FROM documents
FIND ('{search phrase}')
ORDER BY RANK

Query Examples

The following are examples of queries using ORDER BY RANK.

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": {
        "pagesize": 1000,
        "pageoffset": 0,
        "size": 54,
        "total": 54
    },
    "data": [
        {
            "id": 26,
            "name__v": "Document ABC"
        },
        {
            "id": 44,
            "name__v": "Document ABCD"
        },
        {
            "id": 4,
            "name__v": "Document ABCDE"
        }
  ]
}

MAXROWS

Use the MAXROWS clause to retrieve a maximum of N results, applied after any filters.

Syntax

SELECT {fields}
FROM documents
MAXROWS {number}

Query Examples

The following are examples of queries using MAXROWS.

Query

The following query returns a maximum of 500 documents:

SELECT id
FROM documents
MAXROWS 500

SKIP

Use the SKIP clause to skip first N results. The results start at result N + 1.

Syntax

SELECT {fields}
FROM documents
SKIP {number}

Query Examples

The following are examples of queries using SKIP.

Query

The following query skips the first 25 results. The first result returned is result 26.

SELECT id
FROM documents
SKIP 25

PAGESIZE

By default, the maximum number of results displayed per page is 200 for documents and 1000 for objects. Use the PAGESIZE clause to limit the number of results returned per page.

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

Learn more about limiting page results.

If you’re using VQL version v20.2 or lower, you must use LIMIT instead of PAGESIZE. If you use both LIMIT and PAGESIZE in a query, the query ignores LIMIT.

Syntax

SELECT {fields}
FROM {query target}
PAGESIZE {number}

Query Examples

The following are examples of queries using PAGESIZE.

Query

The following query returns 25 documents per page:

SELECT id
FROM documents
PAGESIZE 25

LIMIT

By default, the maximum number of results displayed per page is 200 for documents and 1000 for objects. Use the LIMIT clause to limit the number of results returned per page.

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

Learn more about limiting page results.

Syntax

SELECT {fields}
FROM {query target}
LIMIT {number}

Query Examples

The following are examples of queries using LIMIT.

Query

The following query returns 25 documents per page:

SELECT id
FROM documents
LIMIT 25

PAGEOFFSET

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

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

The response includes the next_page and previous_page URL endpoints when pagination is available. Learn more about paginating results.

In v20.2 or lower, you must use OFFSET instead of PAGEOFFSET. If you use both OFFSET and PAGEOFFSET in a query, the query will ignore OFFSET.

Syntax

SELECT {fields}
FROM {query target}
PAGEOFFSET {number}

Query Examples

The following are examples of queries using PAGEOFFSET.

Query

The following query returns results 200 to 299:

SELECT id
FROM documents
PAGESIZE 100 PAGEOFFSET 200

Response

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "pagesize": 100,
        "pageoffset": 200,
        "size": 100,
        "total": 500,
        "previous_page": "/api/v20.3/query/c2b58293-1606-4c99-925d-b9b89e83670e?pagesize=100&pageoffset=100",
        "next_page": "/api/v20.3/query/c2b58293-1606-4c99-925d-b9b89e83670e?pagesize=100&pageoffset=300"

OFFSET

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

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

The response includes the next_page and previous_page URL endpoints when pagination is available. Learn more about paginating results.

Syntax

SELECT {fields}
FROM {query target}
OFFSET {number}

Query Examples

The following are examples of queries using OFFSET.

Query

The following query returns results 200 to 299:

SELECT id
FROM documents
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"
    }
}

AS

By default, VQL queries return fields in key-value pairs where the key is the field name. In v20.3+ (documents) and v21.1+ (objects), use the AS clause to define an optional alias when using a function on a field in the SELECT clause. Note that aliases:

Aliases are scoped to the query target. Therefore, you can use the same alias name in the main query and a sub-select. Note that if a query includes the same field both by itself and modified by a function, you must use the AS clause to define an alias for the function.

Syntax

SELECT {field} AS {alias}
FROM {query target}

Query Examples

The following are examples of queries using AS.

Query: Aliasing Document Fields

The following query uses the TONAME() function to retrieve the ID, Lifecycle field label, and Lifecycle field name of documents with type Promotional. The query uses an alias to distinguish between the field label and the field name.

SELECT id, lifecycle__v, TONAME(lifecycle__v) AS lifecycle_name
FROM documents
WHERE type__v ='Promotional'

Response: Aliasing Document Fields

{
   "responseStatus": "SUCCESS",
   "responseDetails": {
       "pagesize": 1000,
       "pageoffset": 0,
       "size": 1,
       "total": 1
   },
   "data": [
       {
           "id": 36,
           "lifecycle__v": "General Lifecycle",
           "lifecycle_name": "general_lifecycle__c"
       }
   ]
}

Query: Aliasing Object Fields

The following query uses the LONGTEXT() function to retrieve the ID, the default value of the Packaging Text field, and the full (long text) value of the Packaging Text field. The query uses an alias to distinguish between the default and full field values.

SELECT id, packaging_text__c, LONGTEXT(packaging_text__c) AS package_fulltext
FROM product__v
WHERE name__v = 'Cholecap'

Response: Aliasing Object Fields

{
   "responseStatus": "SUCCESS",
   "responseDetails": {
       "pagesize": 1000,
       "pageoffset": 0,
       "size": 1,
       "total": 1
   },
   "data": [
       {
           "id": "cholecap",
           "packaging_text__c": "Your blood cholesterol level has a lot to do with\nyour chances of getting heart disease. High blood cholesterol is one of the major risk factors for heart disease. A risk factor is a condition that increases your chance of getting a disease. In fact,",
           "package_fulltext": "Your blood cholesterol level has a lot to do with\nyour chances of getting heart disease. High blood cholesterol is one of the major risk factors for heart disease. A risk factor is a condition that increases your chance of getting a disease. In fact, the higher your blood cholesterol level, the greater your risk for developing heart disease or having a heart attack. Heart disease is the number one killer of women and men in the United States. Each year, more than a million Americans have heart attacks, and about a half million people die from heart disease."
       }
   ]
}

Operators

Use comparison and logical operators in a WHERE clause to filter results from a VQL query. You can also use certain logical operators in the FIND search string.

Comparison Operators

All comparison operators are available in API v1.0+. You can use the following operators in the WHERE clause:

Operator Syntax Description
= WHERE {field} = {value} Field values equal to the specified value.
!= WHERE {field} != {value} Field values not equal to the specified value.
< WHERE {field} < {value} Field values are less than the specified value.
> WHERE {field} > {value} Field values are greater than the specified value.
<= WHERE {field} <= {value} Field values are less than or equal to the specified value. Not supported when querying workflows(legacy).
>= WHERE {field} >= {value} Field values are greater than or equal to the specified value. Not supported when querying workflows (legacy).

Equal to (=)

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

Not Equal to (!=)

SELECT label__sys, due_date__sys
FROM active_workflow_task__sys
WHERE status__sys != 'available__sys'

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'

Logical Operators

When querying documents or Vault objects, you can use the following operators in the WHERE clause and FIND search string.

Operator Syntax Description
AND WHERE {field_1} = {value_1} AND {field_2} = {value_2}

FIND ('{value_1} AND {value_2}')		 Field values are equal to both specified values.
BETWEEN WHERE {field} BETWEEN {value_1} AND {value_2} Used with AND to compare data between two different values.
CONTAINS WHERE {field} CONTAINS ({value_1},{value_2},{value_3}) Field values are equal to any of the specified values.
IN WHERE {field} IN (SELECT {fields} FROM {query target}) The IN operator can only be used for inner join relationship queries on documents and objects.
LIKE WHERE {field} LIKE '{value%}' Used with the wildcard character % to search for matching field values when you don’t know the entire value.
NOT FIND (NOT '{search string}') Field values do not match the specified search string. You can only use NOT to negate a FIND search string. This operator is not supported on other clauses.
OR WHERE {field_1} = {value_1} OR {field_2} = {value_2}

FIND ('{value_1} OR {value_2}')		 Field values are equal to either specified value. VQL does not support using the OR operator between different query objects in a WHERE clause, or with Static Reference Constraints for documents.

AND

Use the AND operator to retrieve results that meet two or more 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'

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'

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

IN

Use the IN operator to test whether a field value (placed 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.

LIKE

Use the LIKE operator 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. 

SELECT id, name__v
FROM documents
WHERE name__v LIKE 'N%'

VQL does not support LIKE strings that start with a wildcard (%). For example, name__v LIKE '%_DOC' will not work.

NOT

Use the NOT operator to negate an entire search string in the FIND clause. This operator is not supported on other clauses.

The following query returns documents that do not contain the word cholecap (case insensitive):

SELECT id, name__v
FROM documents
FIND (NOT 'cholecap' )

OR

Use the OR operator to retrieve results that meet any of two or more conditions. Note that VQL does not support the OR operator between different query objects in a WHERE clause or with Static Reference Constraints for documents.

The following query includes documents with a version creation date or modified date after midnight on April 23, 2018:

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

Operator Limitations

This section provides limitations on operators when used with certain fields and objects.

Multi-Value Picklist Fields in High Volume Objects

Queries on multi-value picklists in high volume objects support a maximum of five (5) AND operators.

The following operators are not supported: >, <, >=, <=.

You cannot combine AND and OR operators on the same multi-value picklist field. For example, the following expression is not valid:

WHERE department__c = 'clinical_operations__c' AND department__c = 'biostatistics__c' OR department__c = 'it__c'

When querying more than one field, you must use parentheses to group the operations on each field. For example, the following expression is valid:

WHERE department__c = 'clinical_operations__c' AND (equipment_type__c = 'mri__c' OR equipment_type__c = 'xray__c')

The following expression is not valid:

WHERE department__c = 'clinical_operations__c' AND equipment_type__c = 'mri__c' OR equipment_type__c = 'xray__c'

Query Targets

The target of a query is the object specified in the FROM clause. The target object represents a collection of Vault data such as documents and must be queryable.

This section provides a reference on common query targets and their queryable fields and relationships, including those with metadata that is not retrievable via the standard metadata API.

Query targets include documents, Vault objects, users, roles, groups, lifecycles, events, and relationships.

Not all object fields are queryable.

To query one of these objects, use its name attribute. For example, to query the documents object, enter documents as the query target. To query the Product object, enter product__v as the query target.

Documents

Use the documents object to query Vault documents.

To retrieve document fields and field properties, use the Retrieve All Document Fields API. To retrieve document and binder relationships, use the Retrieve Document Type Relationships API.

Query results do not include archived documents by default. In v15.0+, you can use the archived_documents object to query archived documents.

By default, documents and archived_documents queries return the latest document version. To retrieve all versions or retrieve the latest version that meets a condition, use the ALLVERSIONS and LATESTVERSION query target options.

Document Query Examples

The following are examples of common document queries.

Query: Retrieve the Latest Version of a Document

The following query retrieves the ID and latest version number of all documents called “WonderDrug Information”:

SELECT id, minor_version_number__v, major_version_number__v
FROM documents
WHERE name__v = 'WonderDrug Information'
Response: Retrieve the Latest Version of a Document

This response returns the latest version of the document, version 2.2.

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "pagesize": 1000,
        "pageoffset": 0,
        "size": 1,
        "total": 1
    },
    "data": [
        {
            "id": 534,
            "minor_version_number__v": 2,
            "major_version_number__v": 2
        }
    ]
}

Vault Objects

To retrieve all objects in your Vault, use the Retrieve Object Collection API. To retrieve metadata for a specific object and its fields, use the Retrieve Object Metadata API.

This section outlines the various types of Vault objects.

Custom Vault Objects

All custom Vault objects and object fields are queryable.

The names of these objects always end in __c. For example, to query Custom Object, enter custom_object__c.

Standard Vault Objects

All standard Vault objects and object fields are queryable. The available standard objects vary by application, such as PromoMats or Submissions.

The names of these objects always end in __v. For example, to query the Product object, use product__v.

Vault Object Types

You can use the object_type__v object to query Vault object types.

Use the Retrieve Object Metadata API to get the object_type__v queryable fields and object relationships.

Vault System Objects

Vault system objects 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 end in __sys. For example, to query Groups, use group__sys.

The following system objects are versioned:

Vault Object Query Examples

The following are examples of Vault object and object type queries.

Query: Retrieve Active Object Records

The following query retrieves the ID and name of all active Product records:

SELECT id, name__v
FROM product__v
WHERE status__v = 'active__v'

Response: Retrieve Active Object Records

{
   "responseStatus": "SUCCESS",
   "responseDetails": {
       "pagesize": 1000,
       "pageoffset": 0,
       "size": 29,
       "total": 29
   },
   "data": [
       {
           "id": "00P000000000301",
           "name__v": "Nyaxa"
       },
       {
           "id": "00P000000000302",
           "name__v": "Focusin"
       },
       {
           "id": "00P000000000303",
           "name__v": "Lexipalene"
       }
   ]
}

Query: Retrieve All Object Types

The following query retrieves the ID, API name, and object name of all object types:

SELECT id, api_name__v, object_name__v
FROM object_type__v

Response: Retrieve All Object Types

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "pagesize": 1000,
        "pageoffset": 0,
        "size": 173,
        "total": 173
    },
    "data": [
        {
            "id": "OOT000000000101",
            "api_name__v": "base__v",
            "object_name__v": "product__v"
        },
        {
            "id": "OOT000000000102",
            "api_name__v": "base__v",
            "object_name__v": "application_role__v"
        },
        {
            "id": "OOT000000000103",
            "api_name__v": "base__v",
            "object_name__v": "doc_type_group__v"
        }
    ]
}

Binders

You can use the binders and binder_node__sys objects to query binders for the following:

The binders object is an extension of the documents object and supports the same VQL functions. Binders are available for query in v18.2+.

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

You cannot retrieve this metadata 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 has a value for nodes of type “section” and is null for document and binder nodes. Note that the top level node is a section.
parent_binder_id__sys ID of the parent binder where this node lives.
parent_binder_major_version__sys The major version of the parent binder.
parent_binder_minor_version__sys The minor version of the parent binder.
parent_node_id__sys ID of the parent binder_node__sys object.
section_id__sys Document ID or section ID specific to the binder. For documents, this is different from the document’s actual document id. Available in v22.1+.
parent_section_id__sys Section ID of the parent node, such as “rootNode”. Available in v22.1+.
order__sys The ordinal position of the node within its parent.
content_id__sys Document ID or binder ID.
content_major_version__sys The major version of the content. This field has a value for documents bound to a specific version and is null otherwise.
content_minor_version__sys The minor version of the content. This field has a value for documents bound to a specific version and is null otherwise.
type__sys Points to the standard picklist binder_node_types__sys.
created_date__v Timestamp when the node was created.
created_by__v ID of the user who created the node.
modified_date__v Timestamp when the node was updated.
modified_by__v ID of the 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'

Document Events

Use the events object to query document events.

To retrieve document event fields and field properties, use the Retrieve Document Event SubType Metadata.

Document Events Query Examples

The following are examples of document events queries.

Query: Retrieve Document Events

The following query retrieves the document ID, event type, and event date of all document events:

SELECT document_id__v, event_type__v, event_date__v
FROM events

Response: Retrieve Document Events

{
   "responseStatus": "SUCCESS",
   "responseDetails": {
       "pagesize": 1000,
       "pageoffset": 0,
       "size": 1,
       "total": 1
   },
   "data": [
        {
            "document_id__v": "123",
            "event_date__v": "2015-03-20T22:06:40.000Z",
            "event_type__v": "Distribution Event"
        }
    ]
}

Document Relationships

You can use the relationships object to query document attachments and other document relationships.

Use the Retrieve Document Type Relationships API to get the relationships queryable fields and object relationships.

Document Attachment Query Examples

The following are examples of document attachment queries.

Document Attachment Query

Get relationship properties for document attachments:

SELECT source_doc_id__v, source_major_version__v, source_minor_version__v, relationship_type__v, target_doc_id__v, target_major_version__v, target_minor_version__v
FROM relationships
WHERE relationship_type__v = 'attachments__v' AND source_doc_id__v != null

Source Document Query

Get source document properties for attachments using the source__vr object relationship. Note that the target__vr relationship is not available for attachments, but you can use the target_doc_id__v field to retrieve the attached file’s document ID.

SELECT id, source__vr.name__v, source__vr.product__v, source__vr.country__v, source__vr.id, relationship_type__v, source__vr.version__id, target_doc_id__v
FROM relationships 
WHERE relationship_type__v = 'attachments__v'

Document Roles

You can use the documents and doc_role__sys objects to query document roles. This allows you to see which users and groups are assigned to certain roles on a document, as well as filter documents by the users and groups assigned to roles. Document roles are available for query in v21.1+ only.

Document Roles Relationships

The documents object exposes the doc_roles__sysr relationship. This is a one-to-many relationship which points to doc_role__sys child objects.

The doc_role__sys object exposes the following relationships:

Name Description
user__sysr This is a child relationship allowing a join with the user__sys object.
group__sysr This is a child relationship allowing a join with the group__sys object.
document__sysr This is a parent relationship allowing a join with documents.

Document Roles Queryable Fields

You cannot retrieve this metadata via the standard metadata API.

The following fields are queryable for the doc_role__sys object:

Name Description
role_name__sys The name of the role, for example reviewer__v.
document_id The document ID.
user__sys The ID of the user in the role.
group__sys The ID of the group in the role.

Document Role Query Examples

The following are examples of standard document roles queries:

Query Roles by Document

Find all roles and their assigned users and groups on a document with the document_id 627:

SELECT role_name__sys, user__sys, group__sys
FROM doc_role__sys
WHERE document_id = 627

Query by Users

Find documents where user 123 is in any role:

SELECT document_id, user__sys, user__sysr.username__sys, role_name__sys
FROM doc_role__sys
WHERE user__sys = '123'

Query by Groups

Find documents with the legal reviewers group assigned the reviewer role:

SELECT document_id, role_name__sys
FROM doc_role__sys
WHERE role_name__sys = 'reviewer__v' AND group__sysr.label__v = 'Legal Reviewers'

Query Documents With Specific Users or Groups in a Specific Role

Find the ID and name for documents where users 123 or 456 and groups 9876 or 5432 are assigned the approver role:

SELECT id, name__v
FROM documents
WHERE id IN (SELECT document_id FROM doc_roles__sysr WHERE user__sys CONTAINS (123, 456) OR group__sys CONTAINS (9876, 5432) AND role_name__sys = 'approver__v')

Query Documents with in a Specific Role Subquery

Find the ID, name, and owner role for documents with document_id 123 or 456:

SELECT id, name__v, (SELECT id, user__sysr.email__sys FROM doc_roles__sysr WHERE role_name__sys = 'owner__c')
FROM documents
WHERE id CONTAINS (123, 456)

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:

Group Queryable Fields

You cannot retrieve this metadata 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

You cannot retrieve this metadata 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__sys'

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

Matched Documents

Expected document lists (EDLs) measure the completeness of projects such as clinical studies by linking documents to EDL Item records based on matching field values. In v17.3+, you can use the matched_documents object to query matched documents.

Learn more about EDLs in Vault Help.

Matched Documents Queryable Fields

You cannot retrieve this metadata via the standard metadata API.

The following fields are queryable for the matched_documents object:

Name Description
id The id of the matched_documents record.
edl_item_id__v The id of the EDL Item record linked to the document.
matching_doc_id__v The document id.
major_version__v The document’s major version number.
minor_version__v The document’s minor version number.
created_date__v The date the document was created.
created_by__v The id of the user who created the document.
modified_date__v The date the document was last modified.
modified_by__v The id of the user who last modified the document.
include_in_total__v When set to true, indicates that the document should be applied to the matching document count for the EDL Item.
version_is_locked__v When set to true, confirms that Vault has locked the document version to the EDL Item.
source__v Indicates if the document was matched by user or auto.

Matched Document Query Examples

The following are examples of matched document queries.

Query: Retrieve All Matched Documents

The following query retrieves the ID, EDL Item ID, and other data from all matched documents:

SELECT id, edl_item_id__v, matching_doc_id__v, major_version__v, minor_version__v, created_date__v, created_by__v, modified_date__v, modified_by__v, include_in_total__v, version_is_locked__v, source__v
FROM matched_documents

Renditions

You can 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 the rendition type, for example, viewable_rendition__v. There is no lookup to rendition type metadata.
document_id The parent document id.
major_version_number__sys The major version of the parent document.
minor_version_number__sys The minor version of the parent document.
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 = 'imported_rendition__c'

Users

The users query target allows you to query the users in your Vault.

To retrieve user fields and field properties, use the Retrieve User Metadata API.

When querying users across Vaults, Vault uses the private key values (external, readOnly, and full) for the license_type__v field.

User Query Examples

The following are examples of user queries.

Query: Retrieve Users With a Specific License Type

The following query uses the external private key value instead of external__v to retrieve the first and last names of all external users:

SELECT user_first_name__v, user_last_name__v, license_type__v
FROM users
WHERE license_type__v = 'external'

Response: Retrieve Users With a Specific License Type

The response returns external__v instead of the private key value external:

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "pagesize": 1000,
        "pageoffset": 0,
        "size": 1,
        "total": 1
    },
    "data": [
        {
            "user_first_name__v": "Abigail",
            "user_last_name__v": "Smith",
            "license_type__v": "external__v"
        }
    ]
}

Vault Component Objects

Vault Admins can query Vault component objects like any other Vault object. These include Vault components such as accountmessage__sys, signaturepage__sys, and objecttype__sys.

Not all components are available for query.

Workflows

You can query document and object workflow instances in the following ways:

Workflow and workflow task definitions are not queryable.

The following workflow objects are available for query on document workflows in v21.2+ and object workflows in v22.2+. You must have the Application: Workflow: Query or Application: Workflow: Participate permission to query these objects.

Name Description
active_workflow__sys Each record represents a single in-progress workflow instance. This workflow is running.
inactive_workflow__sys Each record represents a single completed or cancelled workflow instance. This workflow is no longer running.
active_workflow_item__sys Each record represents a single document or object record associated with an in-progress workflow instance.
inactive_workflow_item__sys Each record represents a single document or object record associated with a cancelled or completed workflow instance.
active_workflow_task__sys Each record represents a user task instance of an in-progress workflow. There is one task instance per task-user iteration of a workflow process. For example, a workflow task assigned to three users will have three active_workflow_task__sys records. If a user completes their task, it remains an active_workflow_task__sys until the entire workflow completes or is cancelled.
inactive_workflow_task__sys Each record represents a user task instance of a cancelled or completed workflow. There is one task instance per task-user iteration of a workflow process. For example, a workflow task assigned to three users will have three inactive_workflow_task__sys records.
active_workflow_task_item__sys Each record represents an item of a workflow task of an in-progress workflow instance. For object records, there is one item per task instance. For documents, there is one item for each document in the workflow document set, per task instance. For example, a workflow on two documents with a task sent to three users will have three active_workflow_task__sys, each with two active_workflow_task_item__sys. If a user completes a task item on one document, it remains as active_workflow_task_item__sys until the entire workflow completes or is cancelled.
inactive_workflow_task_item__sys Each record represents an item of a workflow task of an in-progress workflow instance. For object records, there is one item per task instance. For documents, there is one item for each document in the workflow document set, per task instance. For example, a workflow on two documents with a task sent to three users will have three inactive_workflow_task__sys, each with two inactive_workflow_task_item__sys.

Workflow Objects

The active_workflow__sys and inactive_workflow__sys objects contain workflow-level information about each workflow instance. A single workflow can have multiple instances, so each record for these objects represents a unique instance of a workflow. For example, an in-progress Read & Understood workflow instance can be queried with active_workflow__sys, and instances of the same Read & Understood workflow which have already completed can be queried with inactive_workflow__sys.

Workflow Object Relationships

The active_workflow__sys and inactive_workflow__sys objects expose the following relationships:

Name Description
owner__sysr An outbound reference to this workflow instance owner user (user__sys).
{in}active_workflow_tasks__sysr An inbound reference to this workflow’s task instances.
{in}active_workflow_task_items__sysr An inbound reference to this workflow’s task item instances.

Workflow Object Queryable Fields

You cannot retrieve this metadata via the standard metadata API.

The active_workflow__sys and inactive_workflow__sys objects allow queries on the following fields:

Name Description
id The workflow instance ID.
label__sys The workflow label visible to Admins in the Vault UI.
name__sys The name of this workflow.
owner__sys An object reference to the user__sys record in the workflow owner role.
cardinality__sys Indicates how many items can be included in this workflow; either one__sys or one_or_many__sys.
type__sys The workflow type, which is either document__sys or object__sys.
status__sys The workflow status, which is either active__v, cancelled__v, or completed__v.
cancelled_date__sys The date this workflow was cancelled. If the workflow was not cancelled, this value will be null.
completed_date__sys The date this workflow was completed. If the workflow has not completed, this value will be null.
created_by__sys An object reference to the user__sys record which created this workflow instance.
created_date__sys The date this workflow was created.
modified_by__sys An object reference to the user__sys record which last modified this workflow instance.
modified_date__sys The date this workflow was last modified.
class__sys If this is a Read & Understood workflow, this value is read_and_understood__sys. Otherwise, this value is null.

Workflow Item Objects

The active_workflow_item__sys and inactive_workflow_item__sys objects contain item-level information about each document or object record associated with a workflow. Each record in these objects represents a document or object record associated with a unique instance of a workflow. A single document or object record can only be in one workflow at a time, but it may be associated with multiple cancelled or completed workflow instances. For example, cancelling a workflow associated with three documents would create three unique item instances in the inactive_workflow_item__sys object: one for each document. Starting and completing the same workflow would create another set of unique item instances, for a total of six unique item instances.

Workflow Item Relationships

The active_workflow_item__sys and inactive_workflow_item__sys objects expose the following relationships:

Name Description
{in}active_workflow__sysr An outbound reference to the parent workflow instance.
document__sysr An outbound reference to the documents object.

Workflow Item Queryable Fields

You cannot retrieve this metadata via the standard metadata API.

The active_workflow_item__sys and inactive_workflow_item__sys objects allow querying the following fields:

Name Description
id The workflow instance ID.
workflow__sys An object reference to the parent workflow__sys.
type__sys The type of workflow item, either document__sys or object__sys.
document__sys The document ID if the workflow item is a document, null otherwise.
object_name__sys The object record name if the workflow item is an object record, null otherwise.
object_record_id__sys The object record ID if the workflow item is an object record, null otherwise.

Workflow Task Objects

The active_workflow_task__sys and inactive_workflow_task__sys objects contain task-level information about each user task associated with a workflow. A single task can be assigned to multiple users, so each record for these objects represents a unique instance of a task-user iteration. For example, assigning the same Approval task to three unique users would create three unique task instances: one for each user. If the workflow later needed to assign new Approval tasks, it would create new task instances.

Workflow tasks remain active until their parent workflow completes. For example, when a user completes a task it remains an active_workflow_task__sys until the entire parent workflow completes or is cancelled. Once the parent workflow completes or is cancelled, the task is available from inctive_workflow_task__sys.

These objects only contain Task or Document Task steps, which are steps assigned to users which require action. Other step types such as Decision or Content Action are not included in these objects. Learn more about object workflow and document workflow step types in Vault Help.

Workflow Task Relationships

The active_workflow_task__sys and inactive_workflow_task__sys objects expose the following relationships:

Name Description
{in}active_workflow__sysr An outbound reference to the parent workflow instance.
owner__sysr An outbound reference to the workflow task instance owner user (user__sys).
{in}active_workflow_task_items__sysr An inbound reference to the workflow task’s item instances.

Workflow Task Queryable Fields

You cannot retrieve this metadata via the standard metadata API.

The active_workflow_task__sys and inactive_workflow_task__sys allow querying the following fields:

Name Description
id The workflow task instance ID.
workflow__sys An object reference to the parent workflow__sys.
label__sys The workflow task label visible to users in the Vault UI.
name__sys The name of this workflow task.
owner__sys An object reference to the user__sys record assigned to this task. If unassigned, this value is null.
status__sys The status of this workflow task, either available__sys, assigned__sys, completed__sys, or cancelled__sys.
assigned_date__sys The date this workflow task was assigned.
cancelled_date__sys The date this workflow task was cancelled. If the task was not cancelled, this value will be null.
completed_date__sys The date this workflow task was completed. If the task was not completed, this value will be null.
created_date__sys The date this workflow task instance was created.
due_date__sys The date by which this task must be completed. If the task has no due date, this value will be null.
modified_date__sys The date this workflow task was last modified.
iteration__sys The number of times this task instance has iterated. For example, a user completed an Approval task, then something changed on the document or object record, requiring the user to complete the Approval task a second time. In this case, iteration__sys would be 2.
instructions__sys The written instructions for this workflow task. This is custom text created by your Vault Admin when configuring this workflow task.
group_instructions__sys The written instructions for this workflow task, sent when a task is made available to multiple participants. This is custom text created by the workflow owner. This field will only have a value if the workflow is configured to Allow workflow initiator to select participants and to Allow task instructions for these participants.

Workflow Task Item Objects

The active_workflow_task_item__sys and inactive_workflow_task_item__sys objects contain item-level information about each user task associated with a workflow. A single Document Task can be assigned on multiple documents, so for document workflows, each record in these objects represents a unique instance of a task-user-document iteration. For example, assigning an Approval task on three documents would create three unique task item instances: one for each document. Assigning this Approval task on three documents to two users would create two task instances and six item instances: one task instance for each user, and three task item instances for each task instance.

Workflow Task Item Relationships

The active_workflow_task_item__sys and inactive_workflow_task_item__sys objects expose the following relationships:

Name Description
{in}active_workflow__sysr An outbound reference to the parent workflow instance.
{in}active_workflow_tasks__sysr An outbound reference to the parent task instance.
document__sysr An outbound reference to the documents object.

Workflow Task Item Queryable Fields

The active_workflow_task_item__sys and inactive_workflow_task_item__sys allow queries on the following fields:

Name Description
id The workflow task item instance ID.
task__sys An object reference to the parent task record.
workflow__sys An object reference to the parent workflow record.
status__sys The status of this workflow task item, either completed__sys, cancelled__sys, excluded__sys, or pending__sys.
capacity__sys If configured, task verdicts may require a capacity. This is a user-inputted value providing more context for the verdict. If this task item is still in progress or capacity is not required, this value may be null.
verdict__sys If configured, task items may require a verdict. This is a user-inputted value providing context for task completion. If this task item is still in progress or verdict is not required, this value may be null.
verdict_reason__sys If configured, task verdicts may require a reason. This is a user-inputted value providing more context for the verdict. If this task item is still in progress or a verdict reason is not required, this value may be null.
verdict_comment__sys If configured, task verdicts may require a comment. This is a user-inputted value providing more context for the verdict. If this task item is still in progress or a comment is not required, this value may be null.
type__sys The type of workflow task item, either document__sys, document_version__sys, or object__sys.
document_id__sys The document ID for this task item if the workflow task item is for documents, null otherwise.
verdict_document_major_version_number__sys If this task item has a verdict__sys, this field value is the major version of the document associated with the verdict.
verdict_document_minor_version_number__sys If this task item has a verdict__sys, this field value is the minor version of the document associated with the verdict.
verdict_document_version_id__sys If this task item has a verdict__sys, this field value contains the ID, major version, and minor version of the document associated with the verdict. The format is id_major_minor.
object__sys The object record name if the workflow item is an object record, null otherwise.
object_record_id__sys The object record ID if the workflow item is an object record, null otherwise.

Workflow Query Examples

The following query retrieves the due dates for all currently unassigned tasks:

SELECT label__sys, due_date__sys
FROM active_workflow_task__sys
WHERE status__sys = 'available__sys’

The following query retrieves the documents associated with a specified active workflow:

SELECT id, workflow__sys, type__sys
FROM active_workflow_item__sys
WHERE workflow__sys = 123

The following query retrieves the open tasks for a given user:

SELECT label__sys, workflow__sys, owner__sys, due_date__sys, active_workflow__sysr.owner__sys
FROM active_workflow_task__sys
WHERE owner__sysr.username__sys = 'olivia@veepharm.com'
AND status__sys = 'active__v'

The following query retrieves the task details of a specified completed workflow:

SELECT id, owner__sys, name__sys, iteration__sys, inactive_workflow__sysr.owner__sys, inactive_workflow__sysr.name__sys, inactive_workflow__sysr.type__sys,
  (SELECT verdict__sys, verdict_reason__sys, capacity__sys
  FROM inactive_workflow_task_items__sysr)
FROM inactive_workflow_task__sys
WHERE workflow__sys = 123

Legacy Workflow

The workflows object allows queries on legacy workflows. Learn more about legacy workflows in Vault Help.

Note that the escape sequences available for special characters are not standardized for legacy workflows and may behave differently.

Functions & Options

Use query functions and query target options to modify returned values or refine your search by bypassing default query behavior.

If a document field is included twice, both by itself and modified by a function, you must define an alias for the function. When querying objects, you can only create an alias with the LONGTEXT() and RICHTEXT() functions.

Name Syntax Description API Version
ALLVERSIONS SELECT {field}
FROM ALLVERSIONS documents		 Retrieve fields from all document versions. Can only be used when querying documents. v8.0+
CASEINSENSITIVE() WHERE CASEINSENSITIVE({field}) {operator} {value} Bypass case sensitivity of fields in the WHERE clause. v14.0+
DELETEDSTATE()
OBSOLETESTATE()
STEADYSTATE()
SUPERSEDEDSTATE()		 WHERE status__v = DELETEDSTATE()
WHERE status__v = OBSOLETESTATE()
WHERE status__v = STEADYSTATE()
WHERE status__v = SUPERSEDEDSTATE()		 Retrieve fields from all documents in a steady, obsolete, superseded, or deleted state. Can only be used when querying documents. v8.0+
DELETEDSTATE(): v19.2+
FAVORITES SELECT {fields}
FROM FAVORITES documents		 Filter results to those the currently authenticated user has marked as a favorite. v22.2+
LATESTVERSION SELECT LATESTVERSION {field}
FROM ALLVERSIONS documents		 Retrieve fields from the latest matching version. Can only be used when querying documents. v8.0+
LONGTEXT() SELECT LONGTEXT({field})
FROM {query target}		 Retrieve the full value of a Long Text field v17.1+
RECENT SELECT {fields}
FROM RECENT documents		 Filter results to the 20 documents the currently authenticated user has viewed most recently (in descending order by date). v22.2+
RICHTEXT() SELECT RICHTEXT({field})
FROM {query target}		 Retrieve the full Rich Text field value with HTML markup. v21.1+
SCOPE {field} FIND ('{search phrase}' SCOPE {field}) Search a specific document or object field. v15.0+
SCOPE CONTENT FIND ('{search phrase}' SCOPE CONTENT) Search document content only. v8.0+
SCOPE ALL FIND ('{search phrase}' SCOPE ALL) Search all fields and document content. v8.0+
STATETYPE() WHERE state__v = STATETYPE('{state_type}') Retrieve object records in a specific object state type. v19.3+
TONAME() SELECT TONAME({field})
FROM {query target}

WHERE TONAME({field}) {operator} {value}		 With SELECT, retrieve the field name instead of the label.

With WHERE, filter by the field name instead of the label.		 v20.3+

CASEINSENSITIVE()

By default, field values in the WHERE clause are case sensitive. In v14.0+, use the CASEINSENSITIVE() function in the WHERE clause to bypass field case sensitivity.

Syntax

SELECT {field}
FROM {query target}
WHERE CASEINSENSITIVE({field}) {operator} {value}

The {field} parameter must be the name of an object field of type String.

Query Examples

The following are examples of queries using CASEINSENSITIVE().

Query

The following example returns Product records where the Name field value is ‘cholecap’ in any letter case:

SELECT name__v
FROM product__v
WHERE CASEINSENSITIVE(name__v) = 'cholecap'

Response

{
   "responseStatus": "SUCCESS",
   "responseDetails": {
       "pagesize": 1000,
       "pageoffset": 0,
       "size": 1,
       "total": 1
   },
   "data": [
       {
           "name__v": "CholeCap"
       }
   ]
}

Document Functions

Document functions allow you to further refine your document search.

State Functions

By default, document queries retrieve documents in any lifecycle state. In v8.0+, use the special document functions with WHERE to retrieve documents in a lifecycle state (status__v) associated with a specific state type:

In v19.2+:

Learn more about document state types in Vault Help.

Syntax

In v9.0+, the syntax is as follows:

SELECT {fields}
FROM documents
WHERE status__v = STEADYSTATE()|OBSOLETESTATE()|SUPERSEDEDSTATE()|DELETEDSTATE()

You can only use these document functions with the status__v field.

In API v8.0 and earlier, the syntax is as follows:

SELECT {fields}
FROM documents
WHERE STEADYSTATE()|OBSOLETESTATE()|SUPERSEDEDSTATE() = true

Query Examples

The following are examples of queries using the document state functions.

Query: Retrieve Documents in a Steady State

The following query returns the ID and lifecycle state of all documents in a steady state:

SELECT id, name__v
FROM documents
WHERE status__v = STEADYSTATE()
Response: Retrieve Documents in a Steady State
{
   "responseStatus": "SUCCESS",
   "responseDetails": {
       "pagesize": 1000,
       "pageoffset": 0,
       "size": 1,
       "total": 1
   },
   "data": [
       {
           "id": 9,
           "status__v": "Approved"
       }
   ]
}
Query: Retrieve Documents in an Obsolete State

The following query returns the ID and lifecycle state of all documents in an obsolete state:

SELECT id, name__v
FROM documents
WHERE status__v = OBSOLETESTATE()
Query: Retrieve Documents in a Superseded State

The following query returns the ID and lifecycle state of all documents in a superseded state:

SELECT id, name__v
FROM documents
WHERE status__v = SUPERSEDEDSTATE()
Query: Retrieve Documents in a Deleted State

The following query returns the ID and lifecycle state of all documents in a deleted state:

SELECT id, status__v
FROM documents
WHERE status__v = DELETEDSTATE()

TONAME()

By default, queries on documents use field labels instead of field names. In v20.3+, use the TONAME() function to use the field name when querying document fields:

You can only use TONAME() in documents queries on the following document fields:

Syntax

SELECT TONAME({field})
FROM {query target}
WHERE TONAME({field}) {operator} {value}

The {field} parameter must be the name of one of the supported fields.

Query Examples

The following are examples of queries using TONAME().

Query

The following query filters by the status__v field name and returns both the field name and label:

SELECT TONAME(status__v) AS StatusName, status__v
FROM documents
WHERE TONAME(status__v) = 'draft__c'
Response
{
   "responseStatus": "SUCCESS",
   "responseDetails": {
       "pagesize": 1000,
       "pageoffset": 0,
       "size": 3,
       "total": 3
   },
   "data": [
       {
           "StatusName": "draft__c",
           "status__v": "Draft"
       },
       {
           "StatusName": "draft__c",
           "status__v": "Draft"
       },
       {
           "StatusName": "draft__c",
           "status__v": "Draft"
       }
   ]
}

Document Options

Query target options on documents allow you to search all document versions and retrieve favorite and recent documents.

ALLVERSIONS & LATESTVERSION

By default, a query on the documents object targets the latest version of all documents. In v8.0+, you can query by document version:

Syntax

SELECT LATESTVERSION {fields}
FROM ALLVERSIONS documents

Query Examples

The following are examples of queries using ALLVERSIONS and LATESTVERSION.

Query: ALLVERSIONS

The following query returns the ID and major and minor version numbers of all document versions:

SELECT id, major_version_number__v, minor_version_number__v
FROM ALLVERSIONS documents
Response: ALLVERSIONS
{
   "responseStatus": "SUCCESS",
   "responseDetails": {
       "pagesize": 1000,
       "pageoffset": 0,
       "size": 3,
       "total": 3
   },
   "data": [
       {
           "id": 6,
           "major_version_number__v": 1,
           "minor_version_number__v": 1
       },
       {
           "id": 6,
           "major_version_number__v": 1,
           "minor_version_number__v": 0
       },
       {
           "id": 5,
           "major_version_number__v": 0,
           "minor_version_number__v": 1
       }
   ]
}
Query: LATESTVERSION

In this example, a user has assigned a value to the Country field in a document with version 1.1. The following query returns the latest version of this document where the Country field is empty:

SELECT LATESTVERSION id, major_version_number__v, minor_version_number__v
FROM ALLVERSIONS documents
WHERE id = 6 AND country__c = NULL
Response: LATESTVERSION
{
   "responseStatus": "SUCCESS",
   "responseDetails": {
       "pagesize": 1000,
       "pageoffset": 0,
       "size": 1,
       "total": 1
   },
   "data": [
       {
           "id": 6,
           "major_version_number__v": 1,
           "minor_version_number__v": 0
       }
   ]
}

FAVORITES & RECENT

In v22.2+, you can use the FAVORITES and RECENT options to return results from the currently authenticated user’s Favorites and Recent Documents lists in the Vault UI.

You cannot use the FAVORITES and RECENT options in subqueries or with other query target options such as ALLVERSIONS.

Syntax

SELECT {fields}
FROM FAVORITES|RECENT documents

Query Examples

The following are examples of queries using MAXROWS.

Query: Retrieve Favorite Documents

The following query returns the ID and name of all documents in the currently authenticated user’s Favorites list:

SELECT id, name__v
FROM FAVORITES documents
Query: Retrieve Recent Documents

The following query returns the ID and name of all documents in the currently authenticated user’s Recent Documents list:

SELECT id, name__v
FROM RECENT documents

FIND Scopes

By default, FIND queries search all document or object fields of type String. This is equivalent to the default SCOPE PROPERTIES option. Use search options with FIND to scope the search to a specific text field or document content.

SCOPE ALL

In v8.0+, use SCOPE ALL with FIND to search document fields and within document content.

Syntax

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

Query Examples

The following are examples of queries using 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 CONTENT

In v8.0+, use SCOPE CONTENT with FIND to search within document content.

Syntax

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

Query Examples

The following are examples of queries using SCOPE CONTENT.

Query

The following query retrieves documents with the keyword insulin within the content:

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

SCOPE Fields

In v15.0+, use SCOPE to search specific document or Vault object fields.

SCOPE does not support the following document fields:

Syntax

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

Query Examples

The following are examples of queries using SCOPE {field}.

Query: Search a Specific Document Field

The following query searches the name__v document field for the keyword insulin. Note that you can only include one document field.

SELECT id, name__v
FROM documents
FIND ('insulin' SCOPE name__v)
Query: Search a Specific Document Field

The following query searches the generic_name__vs object field for the keyword phosphate:

SELECT id, name__v
FROM product__v
FIND ('phosphate' SCOPE generic_name__vs)
Query: SCOPE combined with SCOPE CONTENT

The following query returns all documents where the product field contains the keyword cholecap and the document content also contains prescribing or information:

SELECT id, name__v
FROM documents
FIND ('cholecap' SCOPE product__v AND 'prescribing information' SCOPE CONTENT)
Query: Combining FIND with WHERE

When using FIND and/or SCOPE, you can use the WHERE filter to narrow results. WHERE must be placed after FIND and SCOPE. The following query searches the generic_name__vs field for the keyword phosphate in all Product records with a specific therapeutic area:

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

Vault Object Functions

Object functions allow you to further refine your search for object records.

LONGTEXT()

By default, VQL returns only the first 250 characters of a Long Text field value and does not include HTML markup. In v17.1+, use LONGTEXT() in the SELECT clause to retrieve the full value of a Long Text field. You can also use LONGTEXT() to retrieve the full value of a Rich Text field without HTML markup.

All retrieval methods return newline characters.

Syntax

SELECT LONGTEXT({field})
FROM {query target}

The {field} parameter must be the name of a Long Text or Rich Text field.

Query Examples

The following are examples of queries using LONGTEXT().

Query

The following VQL query returns both the first 250 characters of this field and the full value without markup:

SELECT name__v,
 rich_text_field__c,
 LONGTEXT(rich_text_field__c) AS LongTextOnRich
FROM campaign__c
WHERE name__v = 'Veepharm Marketing Campaign'
Response
{
"data": [
       {
           "name__v": "Veepharm Marketing Campaign",
           "rich_text_field__c": "A two-hour reduction in sleep per night for one week is associated with a significant reduction in psychomotor performance.\n\nGet a good night's sleep with Veepharm, clinically proven to help you fall asleep faster and stay asleep longer. Ask your",
           "LongTextOnRich": "A two-hour reduction in sleep per night for one week is associated with a significant reduction in psychomotor performance.\n\nGet a good night's sleep with Veepharm, clinically proven to help you fall asleep faster and stay asleep longer. Ask your doctor if Veepharm is right for you."
       }
   ]
}

RICHTEXT()

By default, VQL returns only the first 250 characters of a Rich Text field value and does not include HTML markup. In v21.1+, use RICHTEXT() in the SELECT clause to retrieve the full value of a Rich Text field, including HTML markup.

All retrieval methods return newline characters.

Syntax

SELECT RICHTEXT({field})
FROM {query target}

The {field} parameter must be the name of a Rich Text field.

Query Examples

The following are examples of queries using RICHTEXT().

Query

The following query retrieves the full value of this field with HTML markup:

SELECT name__v,
  RICHTEXT(rich_text_field__c) AS RichTextFunction
FROM campaign__c
WHERE name__v = 'Veepharm Marketing Campaign'
Response
{
"data": [
       {
           "name__v": "Veepharm Marketing Campaign",
           "RichTextFunction": "<blockquote><p style=\"text-align: left;\">A two-hour reduction in sleep per night for one week is associated with a significant reduction in psychomotor performance.</p></blockquote><p><br></p><p>Get a good night's sleep with <b>Veepharm</b>, clinically proven to help you fall asleep faster and stay asleep longer. Ask your doctor if <b>Veepharm&nbsp;</b>is right for you.</p>"
       }
   ]
}

STATETYPE()

By default, object queries retrieve fields from all object records in any state. In v19.3+, use the STATETYPE() function with the WHERE clause to filter object records by lifecycle state (state__v) associated with an object state type. Learn more about object record state types in Vault Help.

Syntax

SELECT {fields}
FROM {query target}
WHERE state__v = STATETYPE('{state_type}')

You can only use the STATETYPE() function with the state__v field. The {state_type} parameter must be the name of a state type and must be enclosed in single quotes.

Query Examples

The following are examples of queries using STATETYPE().

Query

The following query returns the ID and lifecycle state of all Product records in a lifecycle state associated with the Initial state type:

SELECT id, state__v
FROM product__v
WHERE state__v = STATETYPE('initial_state__sys')
Response
{
   "responseStatus": "SUCCESS",
    "responseDetails": {
       "pagesize": 1000,
       "pageoffset": 0,
       "size": 2,
       "total": 2
   },
   "data": [
       {
           "id": "00P000000000508",
           "state__v": "draft_state__c"
       },
       {
           "id": "00P000000000509",
           "state__v": "draft_state__c"
       }
   ]
}

Standards & Specifications

The following standards and specifications apply to Vault queries.

Case Sensitivity

Keywords

Field & Object Names

Field Values

Bypassing Field Value Case-Sensitivity

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

Special Characters

Several escape sequences for special characters are supported on all document fields, object fields, and other VQL endpoints.

Name Escape Sequence Behavior When Unescaped
Backslash (\) \\ Returns error unless used in escape sequence
Single quote (‘) \’ or ‘’ (two single quotes) Creates query string
Double quote (“) \” Creates exact match query in FIND clause and literal “ elsewhere
Percent sign (%) \% Acts as wildcard in LIKE clause and literal % elsewhere
Asterisk (*) \* Acts as wildcard in FIND clause and literal * elsewhere
Line feed \n Literal n
Tab \t Literal t
Carriage return \r Literal r

Languages

Date & Time Formats

Vault Tokens

You can include Vault tokens in queries made using API v22.2+.

Maximum Query String Length

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

Number of Records Returned

There is no limit to the total number of records returned. The default maximum number of records displayed per page is 200 for documents and 1000 for objects. You can adjust this limit with the PAGESIZE clause.

Limiting the Number of Records Returned

VQL provides the PAGESIZE clause to limit the number of results displayed per page.

For some object types, the response does not include the next_page and previous_page pagination URLs and instead displays all results on one page. Learn more about paginating results.

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.

Sorting/Ordering Results

Response Formats

VQL Transaction Limits

The following transaction limits apply to the Vault Query Language (VQL) API endpoint. Learn more about API Rate Limits.

Burst Limit

Vault limits the number of API calls each user can make. The default burst limit is 2000 calls every 5 minutes.

In v21.1+, when the burst limit is reached, Vault delays all calls by 500 ms until the next 5-minute period begins.

Timeout Limit

Vault query requests time out after a period of time. The default timeout limit is 30 minutes.

In v23.1+, when VQL queries fail due to timeout, Vault will prevent the same query from being executed again for a period of time. The default is 24 hours.

Searching & Filtering

The FIND clause searches object and document fields and document content. When searching your Vault with a VQL query, behaviors such as stemming and tokenization usually match the search behavior in the Vault UI.

About Stemming

Vault uses stemming to return results for metadata (but not content) searches that include inflections of the search term:

To return only the exact search term, for example, “large” but not “larger” or “largest”, use an exact match query.

Stemming is available for English, French, and German fields.

Using the Wildcard Character

When searching documents and objects using the FIND clause, use the wildcard character * to find partial matches.

Query

This query searches documents containing words starting with ‘ins’, 'dia’, 'glu’:

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

You can place the wildcard character in any part of the keyword except at the beginning. In v22.3+, VQL does not support leading wildcards. In earlier versions, we strongly discourage adding wildcards at the beginning of search terms due to negative effects on performance and relevance.

Automatic Wildcarding

Vault automatically adds a wildcard to the end of single search terms that do not match the ID pattern, as well as to the end of the last search term for SCOPE CONTENT queries.

About the ID Pattern

Vault applies special handling to single search terms that either have punctuation in the middle of the term or are a combination of characters and digits. The ID pattern describes the purpose of the special handling but does not comprehensively describe all the searches that qualify. For example, a search for ‘10mg’ is clearly not an ID but matches the ID pattern and receives the ID pattern handling.

For searches that fit the ID pattern, all tokens of the ID must match to be included in search results. Vault applies additional handling to document numbers to ensure the desired document is the first search result. For example, a search for VV-123-456 would return VV-123-456 and VV-456-123, but special handling ensures that VV-123-456 appears first in the search results. Additionally, documents that only include VV are not included.

About Synonyms

When searching documents and objects using the FIND operator, Vault also returns documents or objects containing synonyms if a thesaurus exists in your Vault. Vault does not search for synonyms if a query contains:

About Search Term Tokenization

Beginning in v20.2, Vault automatically tokenizes CamelCase, alphanumeric, and delimited strings when performing keyword searches on document and object fields. When performing keyword searches on content, Vault only tokenizes delimited strings.

To enable tokenization in API versions 9.0 - 20.1, set the tokenize request parameter to true. As of API v20.2, Vault ignores the tokenize parameter. Learn more about search term tokenization in Vault Help.

Query

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

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

Query

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

About Strict Matching & Using Operators with FIND

A simple search is any FIND string that does not contain operators. Beginning in v20.2, simple searches use strict matching by default to return the same results as a basic search in the Vault UI. However, when strict matching is disabled, Vault places an implicit OR operator between each keyword, so results can be different than expected. Learn more about strict matching in Vault Help.

In most cases, strict matching is disabled when there is at least one operator in the search string. See Operator Exceptions for details.

A multi-scope query can have one scope that is simple and another that is not. For example:

FIND ('diabetes AND insulin OR Nyaxa' SCOPE Properties OR 'cholesterol Cholecap' SCOPE CONTENT)

In this case, Vault will apply strict matching to the CONTENT scope, but not to the PROPERTIES scope.

Operator Exceptions

If all terms in a search string are joined by the AND operator, Vault requires all of the terms to match. For example, the following query would return results containing both ‘diabetes’ and ‘insulin’ but would not include results containing only one of the terms.

FIND ('diabetes AND insulin')

Using NOT outside of the search string negates the entire expression with strict matching applied. For example, the following query could return results containing ‘pain’ and ‘medication’ but not ‘pain medication’.

FIND (NOT 'pain medication')

User Permissions

Null Field Values

Use null to find documents and objects with or without a value set on a particular field. For example, the following query returns all documents where the External ID field has no value but the Country field does have a value:

SELECT id, name__v
FROM documents
WHERE external_id__v = null AND country__v != null

Vault considers fields with null values in inequalities. For example, the following query returns all documents where the country__v field is either null or a value not equal to 'Canada’:

SELECT id, name__v
FROM documents
WHERE country__v != 'Canada'

Queryable Field Types

This section provides information on queryable field types and their limitations.

Long Text 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

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)

Rich Text Field Type

As of v21.1, the RichText field type allows users to enter text Strings up to 32,000 plaintext characters, with an additional 32,000 characters reserved for HTML markup. For example, <b>Hello</b> is 5 plaintext characters and 7 HTML markup characters.

Vault does not support all HTML tags and attributes in Rich Text fields. Learn more about supported HTML for Rich Text in the API documentation.

Using FIND on a Rich Text field only searches the text, not the HTML markup. While a FIND clause does search the entire text without markup, other queries will only return the first 250 characters of a Rich Text field by default. To return all of the characters, use the RICHTEXT() function.

Formula Fields

A formula field calculates the field-value based on a formula entered during field configuration. VQL allows you to query formula fields for custom and standard objects.

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.

Lookup Fields

A lookup field contains a read-only value that is populated from a field on a parent or referenced object. VQL allows you to query lookup fields, but ORDER BY and WHERE only support searchable lookup fields. Learn more about lookup fields on Vault Help.

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 always includes trailing decimal places. For example, if a USD currency field value was entered by a user as $10, VQL returns $10.00 as a String. The number of trailing decimal places depends on the currency.

To retrieve corporate currency fields, you must use <field name>_corp__sys to retrieve the corporate currency numeric value.

For example, the following query returns a result with the numeric value of the market_value_corp__sys field with for a list of active products.

SELECT name__v, market_value_corp__sys
FROM product__v
WHERE status__v = 'active__v'

Number Fields

In VQL versions v21.2+, VQL displays number fields based on the field’s configured Decimal places. If a user enters 10.00 and the number field is configured with Decimal places of 1, VQL returns 10.0. Likewise, if Decimal places is 0, VQL returns 10. If Decimal places is 9, VQL returns 10.000000000, and so on.

Decimal places are configured by your Vault Admin, and can be different for each unique field. For example, the same document can have multiple number fields each with a different configuration for Decimal places.

Previous Version Behavior

In previous versions of VQL, Number fields have slightly different behavior when displaying decimal places, depending both on your VQL version and the type of data you’re querying.

v20.3 - v21.1

For document queries, VQL displays number fields exactly as the user entered them. If a user enters 10.00, VQL returns 10.00.

For object record queries, VQL displays number fields based on the field’s configured Decimal places. If a user enters 10.00 and the number field is configured with Decimal places of 1, VQL returns 10.0. Likewise, if Decimal places is 0, VQL returns 10. if Decimal places is 9, VQL returns 10.000000000, and so on.

v20.1 - v20.2

VQL displays number fields exactly as the user entered them. If a user enters 10.00, VQL returns 10.00.

v19.3 and Below

VQL truncates trailing zeros on number fields. If a user enters 10.00, VQL returns 10.

Yes/No Fields

You can use boolean (true or false) field values to filter documents and objects by a Yes/No field. Enter true for 'Yes’ and false for 'No’. For example, the following query retrieves the ID and name of all documents where the CrossLink field is set to ‘Yes’:

SELECT id, name__v
FROM documents
WHERE crosslink__v = true

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.

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:

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.

Team Player Object Relationship

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.

Team Mascot Object Relationship

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.

Document Product Relationship

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)

Document Product Result

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:

To find relationships on a document or object, search the metadata response for fields with the following attributes (an example is provided in the JSON response below):

type : ObjectReference

objectType : vault_object__v

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

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

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:

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

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

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

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. Criteria VQL is a slimmed-down version of VQL, entered through the Vault UI. This article provides detailed information for Vault Admins entering Criteria VQL when configuring:

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

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

Use a backslash (\) to escape special characters in VQL.

Dynamic Access Control & Static Reference Constraints

The following applies to static constraints; dynamic reference constraints use tokens.

Rules for dynamic access control and static reference constraints use the same criteria. The sections below explain the available fields.

Note that nested expressions (join relationships) are not allowed. Additionally, OR is not supported for constraints on documents.

ID Fields

Object record IDs are system-managed fields used in the API and are not visible in the Vault UI. If you know the object record ID, you can use it to identify the object record. However, you can also use the object record name.

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

When querying picklists, the 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”, so you would enter therapeutic_area__vs = '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”, so you would enter therapeutic_area__vs = 'Hematology'.

If you supply an invalid value for the label, VQL will treat the label as a string. This means inequalities will still operate alphabetically 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”, so you would enter therapeutic_area__vs = 'hematology__vs'. To find picklist value names, go to Business Admin > Picklists.

If you supply an invalid value for the label, VQL will treat the label as a string. This means inequalities will still operate alphabetically 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, 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.

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'

Date & DateTime Fields

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

Here are some examples of commonly used criteria:

Object Label Field Label Field Name Field Value (example) Rule Criteria Entry
Product Created Date created_date__v 2014-12-20T15:30:00.000Z created_date__v != '2014-12-20T15:30:00.000Z'
Study Start Date study_start_date__vs 2014-12-20 study_start_date__vs >= '2014-12-20'

Numeric Fields

Numeric fields are always used with comparison operators (=, !=, <, >, <=, >=). 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

Expression Limitations on Static Reference Constraints

You can use the following standard VQL operators when defining static reference constraints: =, !=, >, <, >=, <=.

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

There are several limitations on operations when querying multi-value picklists in high volume objects.

Learn more about Criteria VQL Operators below.

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

Expression Limitations on Dynamic Reference Constraints

Dynamic tokens only support the = and != comparison operators.

There are several limitations on operations when querying multi-value picklists in high volume objects.

Learn more about Criteria VQL Operators below.

Query Object Rules Filter Clause

When working with integration rules, Vault Admins can define Query Object Rules which are additional operations to perform against field rules. While field rules provide the SELECT portion of a query, query object rules provide the WHERE. To do this, a Vault Admin enters Criteria VQL as the Filter Clause for the query object rule. For example, a field rule can select the Country field on Product object records. By defining your query object rule’s Filter Clause as WHERE status__v = 'active__v', you can filter for object records where the Status is Active.

Filter clauses support the following operators in addition to the standard Criteria VQL Operators:

Name Description
IN Determines whether or not a value is “in” the list of values provided after the IN operator. Can only be used for inner join relationship queries on documents and objects.
LIKE Used with the wildcard character % to search for matching field values when you don’t know the entire value. Note that VQL does not support fields which begin with a wildcard.
OR Returns results when any of the values are true.

Validate Limitations

To check your syntax, use the Validate link in the Vault UI. This link only validates the following:

Example

The following are examples of Filter Clauses in object query rules:

Description Filter Clause
Only query for objects (or documents) which are in the Complete lifecycle stage and have an Active Status. stage__v = 'complete__c' AND status__v = 'active__v'

If your object’s page layout is configured to display related sections, you can filter the items that users can select in these sections with Criteria VQL. Learn more about page layouts for related sections in Vault Help.

Related sections also support dynamic tokens, such as {{this.field__c}}. Note that dynamic tokens only support the = and != comparison operators. For example, max_dosage__c = {{this.dosage__c}} is supported, while a comparison such as max_dosage__c > {{this.dosage__c}} is not supported.

In addition to the standard Criteria VQL operators, related sections also support the following operators:

Name Description
LIKE Used with the wildcard character % to search for matching field values when you don’t know the entire value. Note that VQL does not support fields which begin with a wildcard.

Criteria VQL for related object sections also support the following unique functions:

Name Description
{{IN_LAST(numberOfDays)}} Used to specify a date field that falls between the current date and a number of days beforehand. Dates are inclusive. For example, created_date__v {{IN_LAST(7)}} queries for object records created in the last seven days.
{{IN_NEXT(numberOfDays)}} Used to specify a date field that falls between the current date and a number of days afterwards. Dates are inclusive. For example, expiration_date__v {{IN_NEXT(7)}} queries for object records expiring in the next seven days.

Learn more about token selection in Vault Help.

Note that these functions cannot be included in a Criteria VQL statement which also contains dynamic tokens. For example, the following Criteria VQL is valid:

While the following Criteria VQL is not valid:

You can check your criteria VQL syntax with the Validate link.

Standard Criteria VQL Operators

Comparison Operators

Criteria VQL supports the following comparison operators:

Operator Description
= Equal to
!= Not equal to
< Less than
> Greater than
<= Less than or equal to
>= Greater than or equal to 
status__v = 'active__v'
study_status__v != 'Not Started'
created_date__v > '2014-12-20'

Logical Operators

AND

The AND operator returns results if the first and second expression are both true.

therapeutic_area__vs = 'cardiology__vs' AND therapeutic_area__vs = 'hematology__vs'

Parentheses can be used to enclose searches.

therapeutic_area__vs = 'neurology__vs' AND (therapeutic_area__vs = 'cardiology__vs' OR therapeutic_area__vs = 'hematology__vs')

CONTAINS

The CONTAINS operator is used with parentheses to enclose multiple values.

therapeutic_area__vs CONTAINS ('hematology__vs','cardiology__vs')

BETWEEN

The BETWEEN operator is used with AND to compare data between two values.

created_date__v BETWEEN '2014-10-15' AND '2014-04-20'

Unsupported

The following logical operators are not supported in Criteria VQL: NOT, AND NOT, OR NOT, FIND.