Package com.veeva.vault.sdk.api.query

package com.veeva.vault.sdk.api.query
This package provides interfaces to perform various operations using VQL queries and retrieve information from query responses. These operations include fetching data, calculating record counts, and validating queries.

Supported Objects

The following objects are available for query:

  • Vault objects
  • Documents
  • Binders
  • Relationships
  • System objects:
    • user__sys
    • group__sys
    • group_membership__sys
    • doc_role__sys
    • binder_node__sys
  • Workflow objects:
    • active_workflow__sys
    • inactive_workflow__sys
    • active_workflow_task__sys
    • inactive_workflow_task__sys
    • active_workflow_task_item__sys
    • inactive_workflow_task_item__sys
All other objects are not supported for query.

For help with VQL queries, refer to the VQL reference.

Using a QueryExecutionRequest to Execute a Record Query

The following example illustrates querying an object and creating a related record for all records returned by the query.

The Query.Builder is used to construct the query which is submitted in a QueryExecutionRequest. Success and error handlers are specified on the QueryOperation before execution. 

 
      // Locate services
      RecordService recordService = ServiceLocator.locate(RecordService.class);
      QueryService queryService = ServiceLocator.locate(QueryService.class);
      LogService logService = ServiceLocator.locate(LogService.class);
      // Query the Country object to retrieve all active countries
      Query countryQuery = queryService.newQueryBuilder()
          .withSelect(VaultCollections.asList("id",
              "LONGTEXT(review_info__c) AS info"))
          .withFrom("country__v")
          .withWhere("status__v = 'active'")
          .build();
      QueryExecutionRequest queryExecutionRequest = queryService.newQueryExecutionRequestBuilder()
          .withQuery(countryQuery)
          .build();
      queryService.query(queryExecutionRequest)
          .onSuccess(queryExecutionResponse -> {
              List countryReviewRecords = VaultCollections.newList();
              queryExecutionResponse.streamResults().forEach(queryExecutionResult -> {
                  String countryId = queryExecutionResult.getValue("id", ValueType.STRING);
                  // When a function alias is used, retrieve the value using the alias
                  String reviewInfo = queryExecutionResult.getValue("info", ValueType.STRING);
                  Record record = recordService.newRecord("country_review__c");
                  record.setValue("country__c", countryId);
                  record.setValue("info__c", reviewInfo);
                  countryReviewRecords.add(record);
              });
              // Batch save new Country Review records
              recordService.batchSaveRecords(countryReviewRecords).ignoreErrors().execute();
          })
          .onError(queryOperationError -> {
              logService.error("Failed to query country records: " + queryOperationError.getMessage());
          })
          .execute();

Using a QueryCountRequest to Check For Duplicate Records

The following example illustrates executing a count query to enforce a uniqueness rule that spans multiple fields.

There cannot be multiple subject__v records that share the same first_name__v and last_name__v. The Query.Builder is used to iteratively build the WHERE clause and the query is submitted in a QueryCountRequest. 

 
      // Locate services
      QueryService queryService = ServiceLocator.locate(QueryService.class);
      // Construct query to check for duplicate records
      Query.Builder queryBuilder = queryService.newQueryBuilder()
          .withSelect(VaultCollections.asList("id"))
          .withFrom("subject__v");
      recordTriggerContext.getRecordChanges().stream().forEach(recordChange -> {
          String firstName = recordChange.getNew().getValue("first_name__v", ValueType.STRING);
          String lastName = recordChange.getNew().getValue("last_name__v", ValueType.STRING);
          queryBuilder.appendWhere(QueryLogicalOperator.OR, "first_name__v = " + firstName + " AND last_name__v = " + lastName);
      });
      QueryCountRequest queryCountRequest = queryService.newQueryCountRequestBuilder()
          .withQuery(queryBuilder.build())
          .build();
      // Execute count query
      queryService.count(queryCountRequest)
          .onSuccess(queryCountResponse -> {
              if (queryCountResponse.getTotalCount() > 0) {
                  throw new RollbackException("DUPLICATE_RECORD", "Duplicate subject__v record detected.");
              }
          })
          .execute();

Using a QueryValidationRequest to Validate VQL Criteria

The following example illustrates using a QueryValidationRequest to validate a VQL WHERE clause that was provided by an Admin.

The kanban_config__c object allows Admins to configure records that control the contents of a kanban board. Each kanban board is targeted to an object, defined in the object_name__c field, and Admins can specify a VQL WHERE clause in the vql_criteria__c field to filter records. This snippet of VQL is validated when the kanban_config__c record is saved using a QueryValidationRequest. 

 
      // Locate services
      QueryService queryService = ServiceLocator.locate(QueryService.class);
      // Construct VQL query to validate VQL criteria
      Query.Builder queryBuilder = queryService.newQueryBuilder()
          .withSelect(VaultCollections.asList("id"))
          .withFrom(objectName)
          .withWhere(vqlCriteria);
      QueryValidationRequest queryValidationRequest = queryService.newQueryValidationRequestBuilder()
          .withQuery(queryBuilder.build())
          .build();
      // Validate query
      queryService.validate(queryValidationRequest)
          .onError(queryOperationErrorResult -> {
              throw new RollbackException("INVALID_VQL_CRITERIA", "VQL criteria [" + vqlCriteria + "] is invalid.");
          })
          .execute();

Using a TokenRequest

The following example illustrates submitting a TokenRequest in a Query.Builder, which is then submitted in a QueryExecutionRequest, QueryCountRequest, and QueryValidationRequest. 
 
      // Locate services
      TokenService tokenService = ServiceLocator.locate(TokenService.class);
      QueryService queryService = ServiceLocator.locate(QueryService.class);

      // Construct token request
      TokenRequest tokenRequest = tokenService.newTokenRequestBuilder()
          .withValue("Custom.username", username)
          .withValue("Custom.amount", amount)
          .build();

      // Construct query
      Query tokenQuery = queryService.newQueryBuilder()
          .withSelect(VaultCollections.asList("id", "name__v"))
          .withFrom("product__c")
          .withWhere("status__v = 'active'")
          .appendWhere(QueryLogicalOperator.AND, "username__c = ${Custom.username}")
          .appendWhere(QueryLogicalOperator.AND, "amount__c > ${Custom.amount})
          .build();

       // Execute query
       QueryExecutionRequest queryExecutionRequest = queryService.newQueryExecutionRequestBuilder()
           .withQuery(tokenQuery)
           .withTokenRequest(tokenRequest)
           .build();
       queryService.query(queryExecutionRequest).execute();

       // Execute count query
       QueryCountRequest queryCountRequest = queryService.newQueryCountRequestBuilder()
           .withQuery(tokenQuery)
           .withTokenRequest(tokenRequest)
           .build();
       queryService.count(queryCountRequest).execute();

      // Execute validation query
      QueryValidationRequest queryValidationRequest = queryService.newQueryValidationRequestBuilder()
          .withQuery(tokenQuery)
          .withTokenRequest(tokenRequest)
          .build();
      queryService.validate(queryValidationRequest).execute();

Using a QueryFacetRequest

The following example illustrates submitting a QueryFacetRequest using a QueryExecutionRequest.Builder.

The example shows a facet request on a query on the incident__c object to get the number of records relating to each adverse_effect__c type for a specific product. 

 
      // Locate services
      RecordService recordService = ServiceLocator.locate(RecordService.class);
      QueryService queryService = ServiceLocator.locate(QueryService.class);

      // Construct query request with a QueryFacetRequest
      QueryExecutionRequest request = queryService.newQueryExecutionRequestBuilder()
          .withQueryString("SELECT id FROM incident__c WHERE product__c = '" + productId + "' MAXROWS 0")
          .withQueryFacetRequest(
              // Construct facet request
              queryService.newQueryFacetRequestBuilder()
                  .withFacetFields(VaultCollections.asList("adverse_effect__c"))
                  .includeLabels(true)
                  .build()
          ).build();

      // Execute query request
      queryService.query(request).onSuccess(response -> {
          Record record = recordService.newRecord("product_common_side_effects_report__c");
          record.setValue("product__c", productId);

          // Get the facet result for the `adverse_effect__c` field
          QueryFacetResult result = response.getFacetResponse()
              .getResults().get(0);

          StringBuilder details = new StringBuilder("<h1> Most Common Side Effects </h1>");
          details.append("<ol>")
                 .append(
                     // Get the facet values for the `adverse_effect__c` field result
                     result.getValues(ValueType.STRING).stream()
                           .map(value -> String.format("<li>%s occurred in %d subjects</li>", value.getLabel(), value.getCount()))
                           .collect(Collectors.joining()))
                 .append("</ol>");
          record.setValue("details__c", details);

          // Batch the new Product Common Side Effects Report record
          RecordBatchSaveRequest recordBatchSaveRequest = recordService.newRecordBatchSaveRequestBuilder().withRecords(VaultCollections.asList(record)).build();
          recordService.batchSaveRecords(recordBatchSaveRequest).ignoreErrors().execute();
      }).execute();
 
 
Requesting Query Describe

 The following example illustrates executing a validation query with Query Describe enabled.
 

 
 
      // Locate services
      QueryService queryService = ServiceLocator.locate(QueryService.class);
      // Construct VQL query for validation and enable Query Describe on it
      Query.Builder queryBuilder = queryService.newQueryBuilder()
          .withSelect(VaultCollections.asList("id", "address__v"))
          .withFrom("subject__v");
      QueryValidationRequest queryValidationRequest = queryService.newQueryValidationRequestBuilder()
          .withQuery(queryBuilder.build())
          .withQueryDescribe()
          .build();
      queryService.validate(queryValidationRequest)
          .onSuccess(queryValidationResponse -> {
              if (queryValidationResponse.hasQueryDescribeResult()) {
                 QueryDescribeResult queryDescribeResult = queryValidationResponse.getQueryDescribeResult();
                 QueryDescribeTarget queryDescribeTarget = queryDescribeResult.getTarget();
                 List<QueryDescribeField> queryDescribeFields = queryDescribeResult.getFields();
                 List<QueryDescribeSubqueryResult> queryDescribeSubqueries = queryDescribeResult.getSubqueries();
                 if (!queryDescribeSubqueries.isEmpty()) {
                      queryDescribeSubqueries.get(0).getRelationshipName();
                      queryDescribeSubqueries.get(0).getTarget();
                      queryDescribeSubqueries.get(0).getFields();
                      queryDescribeSubqueries.get(0).getFields().get(0).getValueType().toString().equals(ValueType.BOOLEAN.toString());
                 }
              }})
              .execute();
 
 

 
Requesting Record Properties

 The following example illustrates fetching the hidden record properties to construct a user-facing result set.
 
 
      // Locate services
      QueryService queryService = ServiceLocator.locate(QueryService.class);
      // Construct query
      Query.Builder queryBuilder = queryService.newQueryBuilder()
          .withSelect(VaultCollections.asList("id", "address__v"))
          .withFrom("subject__v");
      // Construct request with the HIDDEN_FIELDS record property type
      QueryExecutionRequest queryExecutionRequest = queryService.newQueryExecutionRequestBuilder()
          .withQuery(queryBuilder.build())
          .withQueryRecordPropertyTypes(VaultCollections.asList(QueryRecordPropertyType.HIDDEN_FIELDS))
          .build();
      // Execute query
      Map<String, String> addressMap = VaultCollections.newMap();
      queryService.query(queryExecutionRequest)
          .onSuccess(queryExecutionResponse -> {
              queryExecutionResponse.streamResults().forEach(queryExecutionResult -> {
                  String id = queryExecutionResult.getValue("id", ValueType.STRING);
                  if (queryExecutionResult.getQueryRecordPropertyTypes().contains(QueryRecordPropertyType.HIDDEN_FIELDS)) {
                      if (queryExecutionResult.getTypedQueryRecordProperty(QueryHiddenFields.class).getFieldNames().contains("address__v")) {
                          addressMap.put(id, "hidden");
                      } else {
                          addressMap.put(id, queryExecutionResult.getValue("address__v", ValueType.STRING));
                      }
                  }
              });
          })
          .execute();
 
 

 
Batched Result Processing

 QueryService returns query results in a Stream. There is no concept of result pages as
 seen in the VQL API.
 The following example demonstrates processing records in batches of 500 within the stream.
 
 
      queryService.query(queryRequest)
          .onSuccess(queryExecutionResponse -> {
              List<QueryExecutionResult> batch = VaultCollections.newList();
              Iterator<QueryExecutionResult> iterator = queryExecutionResponse.streamResults().iterator();
              while (iterator.hasNext()) {
                  batch.add(iterator.next());
                  if (batch.size() == 500) {
                      // Process batch of results
                      processBatch(batch);
                      batch.clear();
                  }
              }
              if (batch.size() > 0) {
                  // Process final batch of results
                  processBatch(batch);
              }
          })
          .execute();
 
 


 
Using Query Target Options

 Query target options in QueryTargetOption modify the scope of a query's results.
 For example, you can scope results to recently viewed (QueryTargetOption.RECENT)
 or favorited (QueryTargetOption.FAVORITES) documents or Vault objects.
 Learn more about VQL query target options in the Developer Portal.
 

 The following example demonstrates how to execute a VQL query using the RECENT query target option.
 The query filters the results to show the 20 most recently viewed documents by the current ExecuteAs user.
 
 
      // Locate services
      QueryService queryService = ServiceLocator.locate(QueryService.class);
      LogService logService = ServiceLocator.locate(LogService.class);
      // Query the RECENT documents
      Query recentQuery = queryService.newQueryBuilder()
          .withSelect(VaultCollections.asList("id"))
          .withFrom("documents", QueryTargetOption.RECENT)
          .build();
      QueryExecutionRequest queryExecutionRequest = queryService.newQueryExecutionRequestBuilder()
          .withQuery(recentQuery)
          .build();
      queryService.query(queryExecutionRequest)
          .onSuccess(
              // Process results
          )
          .onError(
              // Process errors
          )
          .execute();
 
 


 
Retrieving Query Metadata

 The following examples illustrate executing metadata queries to retrieve metadata information about available VQL query targets, fields, and relationships.
 These metadata queries are helpful to understand the structure and capabilities of queryable objects in Vault.

 
Supported Metadata Queries

 
    
     
  • SHOW TARGETS: Retrieves available VQL query targets.
    • 
     
  • SHOW FIELDS FROM {target}: Retrieves metadata about fields of a specific query target.
    • 
     
  • SHOW RELATIONSHIPS FROM {target}: Retrieves metadata about relationships of a specific query target.
    • 
 


 
Executing a SHOW TARGETS Metadata Query

 The QueryShowTargetsRequest.Builder is used to construct a SHOW TARGETS metadata query.
 This query is submitted via QueryShowTargetsRequest to retrieve the available query targets.

 
 
     QueryService queryService = ServiceLocator.locate(QueryService.class);

     QueryExecutionRequest queryShowTargetsRequest = queryService.newQueryShowTargetsRequestBuilder()
         .like("doc%") // optional LIKE clause
         .build();

     queryService.query(queryShowTargetsRequest)
         .onSuccess(queryExecutionResponse -> {
             queryExecutionResponse.streamResults().forEach(r ->
                 System.out.println(r.getValue("name", ValueType.STRING) + " , " +
                     r.getValue("label", ValueType.STRING) + " , " +
                     r.getValue("label_plural", ValueType.STRING))
             );
         })
         .execute();
 
 


 

 For SHOW TARGETS, the metadata query returns fixed fields:
 
    
     
  • name
    • 
     
  • label
    • 
     
  • label_plural
    • 
 


 
Executing a SHOW FIELDS Metadata Query

 The QueryShowFieldsRequest.Builder is used to construct a SHOW FIELDS metadata query.
 This query is submitted via QueryShowFieldsRequest to retrieve metadata about fields for a specified target.

 
 
     QueryService queryService = ServiceLocator.locate(QueryService.class);

     QueryExecutionRequest queryShowFieldsRequest = queryService.newQueryShowFieldsRequestBuilder()
         .fromTarget("object__c")
         .like("name%") // optional LIKE clause
         .build();

     queryService.query(queryShowFieldsRequest)
         .onSuccess(queryExecutionResponse -> {
             queryExecutionResponse.streamResults().forEach(r ->
                 System.out.println(r.getValue("name", ValueType.STRING) + " , " +
                     r.getValue("label", ValueType.STRING) + " , " +
                     r.getValue("type", ValueType.STRING))
             );
         })
         .execute();
 
 


 

 For SHOW FIELDS, the metadata query returns fixed fields:
 
    
     
  • name
    • 
     
  • label
    • 
     
  • type
    • 
     
  • relationship_name (for Object types only)
    • 
 


 
Executing a SHOW RELATIONSHIPS Metadata Query

 The QueryShowRelationshipsRequest.Builder is used to construct a SHOW RELATIONSHIPS metadata query.
 This query is submitted via  QueryShowRelationshipsRequest to retrieve metadata about relationships for a specified target.

 
 
     QueryService queryService = ServiceLocator.locate(QueryService.class);

     QueryExecutionRequest queryShowRelationshipsRequest = queryService.newQueryShowRelationshipsRequestBuilder()
         .fromTarget("object__c")
         .like("rel%") // optional LIKE clause
         .build();

     queryService.query(queryShowRelationshipsRequest)
         .onSuccess(queryExecutionResponse -> {
             queryExecutionResponse.streamResults().forEach(r ->
                 System.out.println(r.getValue("name", ValueType.STRING) + " , " +
                     r.getValue("target", ValueType.STRING) + " , " +
                     r.getValue("type", ValueType.STRING))
             );
         })
         .execute();
 
 


 

 For SHOW RELATIONSHIPS, the metadata query returns fixed fields:
 
    
     
  • name
    • 
     
  • target
    • 
     
  • type
    • 
 


 
Performance Consideration

 Generally, it is more efficient to construct a query statement with a WHERE clause to retrieve all the records you
 want to work with rather than running queries in a loop. Reducing the number of queries improves performance and
 response time.