Vault-Named Queries

A vault-named query is a database query that can be defined by Corda users. The user can define the following:

• The name of the query
• The query functionality (state the type that the query will work on and the WHERE clause)
• Optional filtering logic of the result set
• Optional transformation logic of the result set
• Optional collection logic of the result set

The query creator must follow a few pre-defined steps so that the query is registered and usable.

Representing a State in the Database

Each state An immutable object representing a fact known by one or more participants at a specific point in time. You can use states to represent any type of data, and any kind of fact. type can be represented as a pre-defined JSON string (custom_representation column in the database). Use this JSON representation to write the vault-named queries. Implement the net.corda.v5.ledger.utxo.query.json.ContractStateVaultJsonFactory<T> interface. The <T> parameter is the type of the state that we want to represent.

For example, a state type called TestState and a simple contract called TestContract, would look like the following:

package com.r3.corda.demo.contract

class TestContract : Contract {
    override fun verify(transaction: UtxoLedgerTransaction) {}
}

@BelongsToContract(TestContract::class)
class TestState(
    val testField: String,
    private val participants: List<PublicKey>
) : ContractState {
    override fun getParticipants(): List<PublicKey> = participants
}

package com.r3.corda.demo.contract;

class TestContract implements Contract {
    @Override
    public void verify(@NotNull UtxoLedgerTransaction transaction) {}
}

@BelongsToContract(TestContract.class)
public class TestState implements ContractState {

    private final List<PublicKey> participants;
    private final String testField;

    public TestState(@NotNull String testField, @NotNull List<PublicKey> participants) {
        this.testField = testField;
        this.participants = participants;
    }

    @NotNull
    @Override
    public List<PublicKey> getParticipants() {
        return new LinkedList<>(this.participants);
    }

    @NotNull
    public String getTestField() {
        return testField;
    }
}

To represent a state as a JSON string, use ContractStateVaultJsonFactory as follows:

class TestStateJsonFactory : ContractStateVaultJsonFactory<TestState> {
    override fun getStateType(): Class<TestState> = TestState::class.java

    override fun create(state: TestState, jsonMarshallingService: JsonMarshallingService): String {
        return jsonMarshallingService.format(state)
    }
}

class TestUtxoStateJsonFactory implements ContractStateVaultJsonFactory<TestUtxoState> {
    @NotNull
    @Override
    public Class<TestUtxoState> getStateType() {
        return TestUtxoState.class;
    }

    @Override
    @NotNull
    public String create(@NotNull TestUtxoState state, @NotNull JsonMarshallingService jsonMarshallingService) {
        return jsonMarshallingService.format(state);
    }
}

After the output state finalizes, it is represented as the following in the database (custom_representation column):

{
  "net.corda.v5.ledger.utxo.ContractState" : {
    "stateRef": "<TransactionID>:<StateIndex>"
  },
  "com.r3.corda.demo.contract.TestState" : {
    "testField": ""
  }
}

Use this representation to create the vault-named queries in the next section.

Creating and Registering a Vault-Named Query

Registration means that the query is stored on sandbox An execution environment within a JVM process that provides isolation for a CorDapp. It shields it from outside threats but it also restricts what it can do so that running potentially dangerous code cannot harm others. creation time and can be executed later. Vault-named queries must be part of a contract CPK. Corda installs the vault-named query when the contract CPK is uploaded. To create and register a query, the net.corda.v5.ledger.utxo.query.VaultNamedQueryFactory interface must be implemented.

Basic Vault-Named Query Registration Example

A simple vault-named query implementation for this TestState would look like this:

class DummyCustomQueryFactory : VaultNamedQueryFactory {
    override fun create(vaultNamedQueryBuilderFactory: VaultNamedQueryBuilderFactory) {
        vaultNamedQueryBuilderFactory.create("DUMMY_CUSTOM_QUERY")
            .whereJson(
                "WHERE visible_states.custom_representation -> 'com.r3.corda.demo.contract.TestState' " +
                        "->> 'testField' = :testField"
            )
            .register()
    }
}

public class DummyCustomQueryFactory implements VaultNamedQueryFactory {
    @Override
    public void create(@NotNull VaultNamedQueryBuilderFactory vaultNamedQueryBuilderFactory) {
        vaultNamedQueryBuilderFactory.create("DUMMY_CUSTOM_QUERY")
                .whereJson(
                        "WHERE visible_states.custom_representation -> 'com.r3.corda.demo.contract.TestState' " +
                                "->> 'testField' = :testField"
                )
                .register();
    }
}

The interface called VaultNamesQueryFactory has one method:

void create(@NotNull VaultNamedQueryBuilderFactory vaultNamedQueryBuilderFactory);

This function is called during start-up and it defines how a query will operate in it with the following steps:

• To create a vault-named query with a given name, in this case it is DUMMY_CUSTOM_QUERY, call vaultNamedQueryBuilderFactory.create().
• To define how a query’s WHERE clause will work, call whereJson().

  Always start with the actual WHERE statement and then write the rest of the clause. Fields need to be prefixed with the visible_states. qualifier. Since visible_states.custom_representation is a JSON column, we can use some JSON specific operations.

  • Parameters can be used in the query in a :parameter format. In this example, use a parameter called :testField which can be set when executing this query. This works similarly to popular Java SQL libraries such as Hibernate.
• To finalize query creation and to store the created query in the registry to be executed later, call register(). This call must be the last step when defining a query.

Complex Query Example

To add some extra logic to the query:

• Only keep the results that have “Alice” in their participant list.
• Transform the result set to only keep the transaction A transaction is a proposal to update the ledger. IDs.
• Collect the result set into one single integer.

These optional logics will always be applied in the following order:

1. Filtering
2. Transforming
3. Collecting

Filtering

To create a filtering logic, implement the net.corda.v5.ledger.utxo.query.VaultNamedQueryStateAndRefFilter<T> interface. The <T> type here is the state type that will be returned from the database, which in this case is TestState. This interface has only one function:

This defines whether or not to keep the given element (row) from the result set. Elements returning true are kept and the rest are discarded.

In this example, keep the elements that have “Alice” in their participant list. This filter would look like this:

class DummyCustomQueryFilter : VaultNamedQueryStateAndRefFilter<TestState> {
    override fun filter(data: StateAndRef<TestUtxoState>, parameters: MutableMap<String, Any>): Boolean {
        return data.state.contractState.participantNames.contains("Alice")
    }
}

class DummyCustomQueryFilter implements VaultNamedQueryStateAndRefFilter<TestUtxoState> {

    @NotNull
    @Override
    public Boolean filter(@NotNull StateAndRef<TestUtxoState> data, @NotNull Map<String, Object> parameters) {
        return data.getState().getContractState().getParticipantNames().contains("Alice");
    }
}

Transforming

To create a transformer class, make sure to only keep the transaction IDs of each record. Transformer classes must implement the VaultNamedQueryStateAndRefTransformer<T, R> interface. The <T> is the type of results returned from the database, which in this case is TestState, <R> is the type to transform the results into, and transaction IDs should be Strings.

This interface has one function:

@NotNull`
`R transform(@NotNull T data, @NotNull Map<String, Object> parameters);

This defines how each record (“row”) will be transformed (mapped):

class DummyCustomQueryTransformer : VaultNamedQueryStateAndRefTransformer<TestState, String> {
    override fun transform(data: StateAndRef<TestState>, parameters: MutableMap<String, Any>): String {
        return data.ref.transactionId.toString()
    }
}

class DummyCustomQueryMapper implements VaultNamedQueryStateAndRefTransformer<TestUtxoState, String> {
    @NotNull
    @Override
    public String transform(@NotNull StateAndRef<TestUtxoState> data, @NotNull Map<String, Object> parameters) {
        return data.getRef().getTransactionId().toString();
    }
}

This transforms each element to a String object, which is the given state’s transaction ID.

Collecting

Collecting is used to collect results set into one single integer.

For this, implement the net.corda.v5.ledger.utxo.query.VaultNamedQueryCollector<R, T> interface. The <R> parameter is the type of the original result set (in this case String because of transformation) and <T> is the type collected into (in this can an Int).

This interface has only one method:

@NotNull
Result<T> collect(@NotNull List<R> resultSet, @NotNull Map<String, Object> parameters);

This defines how to collect the result set. The collector class should look like the following:

class DummyCustomQueryCollector : VaultNamedQueryCollector<String, Int> {
    override fun collect(
        resultSet: MutableList<String>,
        parameters: MutableMap<String, Any>
    ): VaultNamedQueryCollector.Result<Int> {
        return VaultNamedQueryCollector.Result(
            listOf(resultSet.size),
            true
        )
    }
}

class DummyCustomQueryCollector implements VaultNamedQueryCollector<String, Integer> {
    @NotNull
    @Override
    public Result<Integer> collect(@NotNull List<String> resultSet, @NotNull Map<String, Object> parameters) {
        return new Result<>(
                List.of(resultSet.size()),
                true
        );
    }
}

Registering our Complex Query

Register a complex query with a filter, a transformer, and a collector with the following example:

class DummyCustomQueryFactory : VaultNamedQueryFactory {
    override fun create(vaultNamedQueryBuilderFactory: VaultNamedQueryBuilderFactory) {
        vaultNamedQueryBuilderFactory.create("DUMMY_CUSTOM_QUERY")
            .whereJson(
                "WHERE visible_states.custom_representation -> 'com.r3.corda.demo.contract.TestState' " +
                        "->> 'testField' = :testField"
            )
            .filter(DummyCustomQueryFilter())
            .map(DummyCustomQueryMapper())
            .collect(DummyCustomQueryCollector())
            .register()
    }
}

public class JsonQueryFactory implements VaultNamedQueryFactory {
    @Override
    public void create(@NotNull VaultNamedQueryBuilderFactory vaultNamedQueryBuilderFactory) {
        vaultNamedQueryBuilderFactory.create("DUMMY_CUSTOM_QUERY")
                .whereJson(
                        "WHERE visible_states.custom_representation -> 'com.r3.corda.demo.contract.TestState' " +
                                "->> 'testField' = :testField"
                )
                .filter(new DummyCustomQueryFilter())
                .map(new DummyCustomQueryMapper())
                .collect(new DummyCustomQueryCollector())
                .register();
    }
}

Executing a Vault-Named Query

To execute a query use UtxoLedgerService. This can be injected to a flow Communication between participants in an application network is peer-to-peer using flows. via @CordaInject. To instantiate a query call the following:

utxoLedgerService.query("DUMMY_CUSTOM_QUERY", Int::class.java)

utxoLedgerService.query("DUMMY_CUSTOM_QUERY", Integer.class)

Provide the name of the query (in this case DUMMY_CUSTOM_QUERY) and the return type. Since the result set is collected into an integer in the complex query example, use Int (or Integer in Java).

Before executing, define the following:

• Which index the result set should start (setOffset), default 0.
• How many results should the query return (setLimit), default Int.MAX (2,147,483,647).
• Define named parameters that are in the query and the actual value for them.
  All parameters must be defined, otherwise the execution will fail. (setParameter or setParameters)
• Each state in the database has a timestamp value for when it was inserted. Set an * upper limit to only return states that were inserted before a given time. (setTimestampLimit)

In this case it would look like this:

    val resultSet = utxoLedgerService.query("DUMMY_CUSTOM_QUERY", Int::class.java) // Instantiate the query
                .setOffset(0) // Start from the beginning
                .setLimit(1000) // Only return 1000 records
                .setParameter("testField", "dummy") // Set the parameter to a dummy value
                .setCreatedTimestampLimit(Instant.now()) // Set the timestamp limit to the current time
                .execute() // execute the query

    PagedQuery.ResultSet<Integer> resultSet = utxoLedgerService.query("DUMMY_CUSTOM_QUERY", Integer.class) // Instantiate the query
                    .setOffset(0) // Start from the beginning
                    .setLimit(1000) // Only return 1000 records
                    .setParameter("testField", "dummy") // Set the parameter to a dummy value
                    .setCreatedTimestampLimit(Instant.now()) // Set the timestamp limit to the current time
                    .execute(); // execute the query

Results can be acquired by calling getResults() on the ResultSet. Paging can be achieved by increasing the offset until the result set has elements:

var currentOffset = 0;

val resultSet = utxoLedgerService.query("DUMMY_CUSTOM_QUERY", Integer.class) // instantiate the query
                .setOffset(0) // Start from the beginning
                .setLimit(1000) // Only return 1000 records
                .setParameter("testField", "dummy") // Set the parameter to a dummy value
                .setCreatedTimestampLimit(Instant.now()) // Set the timestamp limit to the current time
                .execute()

while (resultSet.results.isNotEmpty()) {
    currentOffset += 1000
    query.setOffset(currentOffset)
    resultSet = query.execute()
}

int currentOffset = 0;

ResultSet<Integer> resultSet = utxoLedgerService.query("DUMMY_CUSTOM_QUERY", Integer.class) // instantiate the query
                .setOffset(0) // Start from the beginning
                .setLimit(1000) // Only return 1000 records
                .setParameter("testField", "dummy") // Set the parameter to a dummy value
                .setCreatedTimestampLimit(Instant.now()) // Set the timestamp limit to the current time
                .execute();

while (resultSet.results.isNotEmpty()) {
    currentOffset += 1000;
    query.setOffset(currentOffset);
    resultSet = query.execute();
}

Or just calling the hasNext() and next() functionality:

val resultSet = utxoLedgerService.query("DUMMY_CUSTOM_QUERY", Integer.class) // instantiate the query
                .setOffset(0) // Start from the beginning
                .setLimit(1000) // Only return 1000 records
                .setParameter("testField", "dummy") // Set the parameter to a dummy value
                .setCreatedTimestampLimit(Instant.now()) // Set the timestamp limit to the current time
                .execute()

var results = resultSet.results

while (resultSet.hasNext()) {
    results = resultSet.next()
}

ResultSet<Integer> resultSet = utxoLedgerService.query("DUMMY_CUSTOM_QUERY", Integer.class) // instantiate the query
                .setOffset(0) // Start from the beginning
                .setLimit(1000) // Only return 1000 records
                .setParameter("testField", "dummy") // Set the parameter to a dummy value
                .setCreatedTimestampLimit(Instant.now()) // Set the timestamp limit to the current time
                .execute();

List<Integer> results = resultSet.getResults();

while (resultSet.hasNext()) {
    results = resultSet.next();
}

Vault-Named Query Operators

The following is the list of the standard operators for the vault-named query syntax:

• IN
• LIKE
• IS NOT NULL
• IS NULL
• AS
• OR
• AND
• !=
• >
• <
• >=
• <=
• ->
• ->>
• ?
• ::

Where the behaviour is not standard, the operators are explained in detail in the following sections.

Name: ->

Right Operand Type: Int

Description: Gets JSON array element.

Example:

custom_representation -> com.r3.corda.demo.ArrayObjState -> 0

Example JSON:

{
  "com.r3.corda.demo.ArrayObjState": [
    {"A": 1},
    {"B": 2}
  ]
}

This example returns:

{
  "A": 1
}

Name: ->

Right Operand Type: Text

Description: Get JSON object field.

Example:

custom_representation -> 'com.r3.corda.demo.TestState'

Selects the top-level JSON field called com.r3.corda.demo.TestState from the JSON object in the custom_representation database column.

Example JSON:

{
  "com.r3.corda.demo.TestState": {
    "testField": "ABC"
  }
}

This example returns:

{
  "testField": "ABC"
}

Name: ->>

Right Operand Type: Int

Description: Get JSON array element as text.

Example:

custom_representation -> 'com.r3.corda.demo.ArrayState' ->> 2

Selects the third element (indexing from 0) of the array type top-level JSON field called com.r3.corda.demo.ArrayState from the JSON object in the custom_representation database column.

Example JSON:

{
  "com.r3.corda.demo.ArrayState": [
    5, 6, 7
  ]
}

This example returns: 7.

Name: ->>

Right Operand Type: Text

Description: Get JSON object field as text.

Example:

custom_representation -> 'com.r3.corda.demo.TestState' ->> 'testField'

Selects the testField JSON field from the top-level JSON object called com.r3.corda.demo.TestState in the custom_representation database column.

Example JSON:

{
  "com.r3.corda.demo.TestState": {
    "testField": "ABC"
  }
}

This example returns: ABC.

Name: ?

Right Operand Type: Text

Description: Checks if JSON object field exists.

Example:

custom_representation ? 'com.r3.corda.demo.TestState'

Checks if the object in the custom_representation database column has a top-level field called com.r3.corda.demo.TestState.

Example JSON:

{
  "com.r3.corda.demo.TestState": {
    "testField": "ABC"
  }
}

This example returns: true.

Name: ::

Right Operand Type: A type, for example, Int

Description: Casts the element/object field to the specified type.

Example:

(visible_states.field ->> property)::int = 1234