SoQL Howtos and examples

How to use SoQL in a project

SoQL is a separate Java Maven module. As of now using this module is required to parse SoQL queries into IQuery objects. These objects can then be passed to the search methods in the Sophora API.

Maven Dependency

Add the following dependency to your pom.xml and either define the dependency version in the soql.version property or replace the placeholder with the version. Please note that SoQL uses com.subshell.sophora.api as a provided dependency, meaning your project should also include com.subshell.sophora.api as a dependency.

<dependency>
	<groupId>com.subshell.sophora</groupId>
	<artifactId>sophora-query-language</artifactId>
	<version>${soql.version}</version>
</dependency>

If you are using Gradle, use this entry:

compile "com.subshell.sophora:sophora-query-language:${soql.version}"

Usage

Basically, the module's API has two relevant types: SoQL and SophoraQuery. SoQL can be used to convert a SoQL string to a SophoraQuery object.

The SophoraQuery object contains the parsed IQuery and a SearchParameters object which can both be passed to the search methods of the Sophora API.

In case of parse errors, a SoQLException will be thrown (which is a sub-class of SophoraException).

The following code demonstrates how to parse a SoQL string:

String soqlString = "primaryType in ('sophora-content-nt:story', 'sophora-content-nt:video') size 10 order by creationDate desc";
SophoraQuery result = SoQL.parse(soqlString);

As of Java 15 you can also write mutli line strings to make longer queries more readable:

String soqlString = """
		primaryType in ('sophora-content-nt:story', 'sophora-content-nt:video') 
		size 10
		order by creationDate desc
""";
SophoraQuery result = SoQL.parse(soqlString);

The resulting SophoraQuery can then be passed to the SophoraClient, e.g.:

ISophoraDocumentSummaryPage searchResult = client.findDocuments(result.query(), result.searchParameters());

Example queries

The following queries are examples to demonstrate how versatile SoQL is. It is highly recommended to always specify a sorting order to get deterministic query behaviour. When in doubt creationDate is a good default.

Example 1: Special Keywords

primaryType = 'sophora-nt:story' 
and state != 'published' 
and (creationDate before 2023-12-18T13:00:00 or modificationDate after 2023-12-18T13:00:00)
and isDeleted != false 
and editor = 'john' 
and author = 'jane' 
and not(has mixin 'sophora-nt:exampleMixin') 
size 100 page 42 
search in collection:default
order by creationDate desc

The above query finds every document which matches all of the following rules:

• the document's primaryType is sophora-nt:story
• the document is not published
• is has been created before or modified after 2023-12-18T13:00:00
• it is not deleted
• the last editor is the user with the username john
• the author is the user with the username jane
• it does not have the mixin sophora-nt:exampleMixin

The result will return the 42nd page with a page size of 100 and will search in the default collection in Solr.

In this query we made use of many of the convenient special keywords of the SoQL syntax. We did not have to write out any property name.

Example 2: Property Names

primaryType in ('custom-nt:story', 'custom-nt:news')
and custom:title ~= 'elections'
and custom:date between 2022-01-01T00:00:00 and 2023-01-01T00:00:00
and (state = 'published' or state = 'inProcess')
order by creationDate desc

This query will find all documents which match all of the following rules:

• the primaryType is custom-nt:story or custom-nt:news
• the custom:title property contains "elections"
• the custom:date property is anytime in the year 2022
• the document is either published or inProcess

Here we are using some special keywords but also direct property names.

Example 3: Child Nodes and Yellow Data

primaryType = 'custom-nt:story'
and custom:teaserImage[custom:name = 'jane']
and yellowData[custom:text ~= 'elections']
order by creationDate desc

This query will find all documents which match all of the following rules:

• the primary type is custom-nt:story
• the custom:teaserImage child node has a property custom:name matching "jane"
• it has yellowData with the custom:text property containing "elections"

As you can see, child nodes and yellow data of a document are queried using a "sub-query" in square brackets.

Example 4: Date calculations

creationDate between 2023-01-01 and 2024-01-01
and modificationDate after now - 14d
order by modificationDate desc

This query will find all documents which have been created in the year 2023 but have been modified in the last 14 days (counted from the moment the query is parsed at due to how now is implemented).