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


 
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.