Interface Summary
Interface	Description
Query	Represents a VQL query.
Query.Builder	Creates an instance of `Query`.
QueryCountRequest	Represents a VQL count query request which can be submitted through `QueryService.count(QueryCountRequest)`.
QueryCountRequest.Builder	Creates an instance of `QueryCountRequest`.
QueryCountResponse	Represents the result of a count query execution.
QueryExecutionRequest	Represents a VQL query request which can be submitted through `QueryService.query(QueryExecutionRequest)`.
QueryExecutionRequest.Builder	Creates an instance of `QueryExecutionRequest`.
QueryExecutionResponse	Represents the result of a VQL query execution.
QueryExecutionResult	Represents a single row in `QueryExecutionResponse`.
QueryOperation<T>	A sequence of instructions that can be chained together, building a query operation which can be executed with `QueryOperation.execute()`.
QueryOperationError	Represents a VQL query operation error.
QueryResponse	Deprecated as of 21R3.4, see `QueryService.query(String)`
QueryResult	Deprecated as of 21R3.4, see `QueryService.query(String)`
QueryService	Service to execute VQL queries.
QueryValidationRequest	Represents a VQL query request which can be validated through `QueryService.validate(QueryValidationRequest)`.
QueryValidationRequest.Builder	Creates an instance of `QueryValidationRequest`.
QueryValidationResponse	Represents the result of a query validation execution.

Enum Summary
Enum	Description
QueryEscapeOverride	Valid overrides for the default escape strategy in a VQL query.
QueryLogicalOperator	Valid logical operators for VQL queries.
QueryOperationErrorType	Error types that may occur during `QueryOperation` execution.
QueryTargetOption	Valid options to modify the scope of a VQL query target.

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

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.

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

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 countries in a region
      Query countryQuery = queryService.newQueryBuilder()
          .withSelect(VaultCollections.asList("id",
              "LONGTEXT(review_info__c) AS info"))
          .withFrom("country__v")
          .withWhere("status__v = 'active'")
          .appendWhere(QueryLogicalOperator.AND, "region__c = '" + region + "'")
          .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();

Document Query

The following example illustrates querying documents and using the results to set a field on a document.

When a document enters the Approved state, this lifecycle entry action queries for the document's parent. Once found, we query a second time to get the value of the parent document's Expiration Date field. We then set this field value on the Parent Expiration Date field on our original, newly approved document.

 
      // Get current doc version and initialize services
      DocumentVersion docVersion = documentActionContext.getDocumentVersions().get(0);
      QueryService queryService = ServiceLocator.locate(QueryService.class);
      DocumentService documentService = ServiceLocator.locate(DocumentService.class);
      String docId = docVersion.getValue("id", ValueType.STRING);

      // Get parent doc id with query service
      String parentDocumentQuery = "SELECT target_doc_id__v FROM relationships WHERE source_doc_id__v = '"
              + docId+ "' AND relationship_type__v = 'basedon__v' ";
      QueryResponse parentDocument = queryService.query(parentDocumentQuery);

      Iterator<QueryResult> iterator = parentDocument.streamResults().iterator();
      if (iterator.hasNext()) {
          QueryResult idResults = iterator.next();
          String parentDocId = idResults.getValue("target_doc_id__v", ValueType.STRING);
          // Get expiration date field from parent doc with query service
          String expirationDateQuery = "SELECT LATESTVERSION expiration_date__c FROM documents WHERE id = '" + parentDocId + "'";
          QueryResponse expirationDate = queryService.query(expirationDateQuery);
          // Set parent expiration date field on current doc
          docVersion.setValue("parent_expiration_date__c",
                ((QueryResult) expirationDate.streamResults().iterator().next()).getValue("expiration_date__c", ValueType.DATE));

          // Update current doc with document service
          List<DocumentVersion> documentsToUpdate = VaultCollections.newList();
          documentsToUpdate.add(docVersion);
          documentService.saveDocumentVersions(documentsToUpdate);
      }
      else {
          throw new RollbackException("OPERATION_NOT_ALLOWED", "Cannot find parent for document: " + docId);
      }

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.