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
Legacy 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 QueryPageRequest to Execute a Paginated Query

The following example illustrates executing a query with explicit pagination and iterating through results page by page.

The Query.Builder.withPageSize(int) method is used to enable pagination. Subsequent pages are retrieved using QueryPageRequest. If pagination is explicitly configured on a query (e.g. via Query.Builder.withPageSize(int)), the query execution will enter Pagination Mode. In this mode, QueryExecutionResponse.streamResults() returns only the records contained in the current page. Subsequent pages must be retrieved using QueryPageRequest.

If pagination is not configured, the service remains in Streaming Mode, where all results are returned in a single continuous stream. 

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

      // Build the initial query with a page size of 500
      Query query = queryService.newQueryBuilder()
          .withSelect(VaultCollections.asList("id", "name__v"))
          .withFrom("product__v")
          .withPageSize(500)
          .build();

      QueryExecutionRequest request = queryService.newQueryExecutionRequestBuilder()
          .withQuery(query)
          .build();

      final QueryExecutionResponse[] pageHolder = new QueryExecutionResponse[1];
      final boolean[] done = new boolean[] {false};

      // Execute the initial request
      queryService.query(request)
         .onSuccess(response -> {
             pageHolder[0] = response;
         })
         .onError(err -> {
             done[0] = true;
             // error handling
         })
         .execute();


      while (!done[0]) {
         QueryExecutionResponse current = pageHolder[0];

         current.streamResults().forEach(r -> {
             // process record
         });

         if (!current.hasNextPage()) {
             done[0] = true;
             break;
         }

         // Fetch subsequent pages if they exist
         long nextOffset = current.getNextPageOffset();

         QueryPageRequest nextReq = queryService.newQueryPageRequestBuilder()
             .withQueryId(current.getQueryId())
             .withPageOffset(nextOffset)
             .build();

         queryService.query(nextReq)
             .onSuccess(response -> {
                 pageHolder[0] = response;
             })
             .onError(err -> {
                 done[0] = true;
                 // error handling
             })
             .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();
                 QueryDescribeType queryDescribeType = queryDescribeResult.getType();
                 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();

Mapping Records to a User-Defined Model (UDM)

The following example illustrates querying an object and mapping the results directly to a UserDefinedModel (UDM). This simplifies data handling because it automates the conversion of raw JSON and other intermediate formats into the UDM.

The QueryService.query(QueryExecutionRequest, Class) method takes a QueryExecutionRequest and a Class that extends UserDefinedModel. The results are streamed as instances of the provided UDM class. 

 
      // Define a UDM for Country data
      public interface CountryModel implements UserDefinedModel {
          @UserDefinedProperty(name = "id")
          String getId();
          void setId(String id);

          @UserDefinedProperty(name = "name__v")
          String getName();
          void setName(String name);
      }

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

      // Query the Country object to retrieve all countries
      QueryExecutionRequest queryExecutionRequest = queryService.newQueryExecutionRequestBuilder()
          .withQuery("SELECT id, name__v FROM country__v")
          .build();

      queryService.query(queryExecutionRequest, CountryModel.class)
          .onSuccess(queryUdmResponse -> {
              queryUdmResponse.streamResults().forEach(country -> {
                  System.out.println("Country ID: " + country.getId() + ", Name: " + country.getName());
              });
          })
          .onError(
              // Process errors
          )
          .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 for available query targets, fields, and relationships. These metadata queries provide information on the structure and capabilities of queryable objects in Vault. Learn more about SHOW queries on the Developer Portal.

Supported Metadata Queries

  • SHOW TARGETS: Retrieves VQL query targets the authenticated user has permission to query.
  • SHOW FIELDS FROM {target}: Retrieves queryable fields on the specified query target.
  • SHOW RELATIONSHIPS FROM {target}: Retrieves queryable relationships on the specified query target.

Executing a SHOW TARGETS Metadata Query

The QueryShowTargetsRequest.Builder constructs a SHOW TARGETS metadata query. The QueryShowTargetsRequest submits the query 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 constructs a SHOW FIELDS metadata query. The QueryShowFieldsRequest submits this query to retrieve queryable fields on the 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 constructs a SHOW RELATIONSHIPS metadata query. The QueryShowRelationshipsRequest submits this query to retrieve queryable relationships on the 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

Use filters to reduce your result set

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.

Batch-Process Query Results

It is recommended to use explicit pagination by setting a page size in the Query.Builder and requesting subsequent pages using a QueryPageRequest for more granular control over record retrieval and processing.

When not using explicit pagination, QueryService returns all query results as a Stream. Even in that case, your code will still benefit from running logic on batches of records rather than processing records off the stream. 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();