SoQL Syntax

SoQL borrows syntax elements from other popular query languages like SQL. On this page we will go through all syntax features and possibilities and provide short syntax examples. For a comprehensive how-to and complete examples head over to SoQL Howtos and examples.

Usage example

Let's start right with an example. The following query searches for documents of certain primary types with a search result page size of 10:

primaryType in ('sophora-content-nt:story', 'sophora-content-nt:video') size 10

General syntax

A full query follows this structure:

[QUERY] [page y] [size x] [order by p1 <asc|desc> [, p2 <asc|desc> ...]] [include <unsearchable|deleted|disabled> ...] [search in <database|database-live|collection:solr-collection-name>]

The actual search query can be followed by search parameters. [QUERY] consists of one or multiple combined expressions each with a property name, a child node name or a special keyword on the lefthand side of the expression, followed by an operator and a search value or a keyword-specific sub-query.

The keywords of SoQL are case-insensitive. The order of the query parts after [QUERY] does not matter. Property and child node names on the left side of an expression can be unquoted as long as they don't collide with any of the SoQL keywords. SoQL uses ' (single quotes) for strings. Dates are not considered strings and should not use quotes.

Property names or child node names are always on the left side of an expression, while a list of keywords and operators can be used to construct the rest of the expression.

Child nodes and yellow data

Querying properties of child nodes requires to use a bracket syntax with a nested query inside:

sophora-extension:imagedata[author = 'Jane']

The same bracket syntax is used in combination with the yellowData special keyword (see below).

Expressions can be combined with parentheses ( ( and ) ) to create conjuntions or disjunctions using the AND and OR keywords.

Allowed date and time formats

Basically, SoQL accepts a subset of ISO 8601 formatted dates. The following formats are valid SoQL date formats:

• YYYY-MM-DD
• YYYY-MM-DDThh:mm:ss
• YYYY-MM-DDThh:mm:ssZ
• YYYY-MM-DDThh:mm:ss±hh:mm

For example:

• 2024-02-01
• 2024-02-01T16:01:00

The time format has time zone support by appending Z for UTC or an offset in the format ±hh:mm, for example:

• 2024-02-01T16:01:00Z
• 2024-02-01T16:01:00-03:00
• 2024-02-01T16:01:00+03:00

You can also use the keyword now to reference the moment the query is parsed at (not executed!).

Date calculations

SoQL allows to perform simple date calculations in the form of a date or datetime minus or plus a duration. The duration consists of a number and a unit. Supported units are:

• D for days
• H for hours
• M for minutes
• S for seconds

As all operators in SoQL, the units are case insensitive. For example to find all documents which have been modified in the last two days, you could use the following query:

modificationDate after now - 2d

SoQL Keywords and Operators

Keywords

SoQL includes the following keywords to construct expressions:

• between a and b
• after
• before
• not
• in
• is
• and
• or
• null
• now
• true
• false

between can only be used with dates, datetimes or numbers. The keywords after and before only apply to dates and datetimes

The keywords is and is not can be used as synonyms for the operators = and !=.

The in keyword (or the combination not in) can be used to compare the lefthand side of an expression with a list of values on the righthand side, e.g. the following query will match all documents with one of the primary types in the righthand side:

primaryType in ('sophora-content-nt:story', 'sophora-content-nt:video')

Operators

The following operators can be used, to check for equality, unequality or likeness ("contains"):

• = (equals)
• != (not equals)
• ~= (like)

Numerical Operators

The following numerical operators can only be used in combination with numbers:

• > (greater than)
• >= (greater than or equal)
• < (less than)
• <= (less than or equal)

Special Keywords

Additionally, SoQL includes a wide range of special keywords which can be used as shortcuts for many common document properties.

To give an example for the alert above, the following SoQL string:

sophoraId = 'news100'

results in the IQuery:

new DocumentIdQuery("news100");

While the following SoQL string:

sophora:id = 'news100'

will result in this IQuery:

new PropertyQuery("sophora:id", "news100", true);

Both queries will find the same document with the Sophora ID news100. Often the difference will not be relevant but there are some edge case features and optimizations happening under the hood when performing searches with specified IQuery implementations. Therefore we recommend using the following special keywords over the direct property names.

primaryType

Used as an alias to search for documents including or excluding documents with a specific primary type.

primaryType = 'sophora-nt:story'

uuid

Queries for a document with a specific UUID (jcr:uuid).

uuid = '2dc1b51d-dda2-4549-b213-304a2c0b58e5'

sophoraId

Queries for a document with a specific Sophora ID (sophora:id) or ID stem. ID stem searches are performed, when the value is an ID stem followed by a 0 like in the second example below.

sophoraId = 'news100'

sophoraId = 'news0'

externalId

Queries for a document with a specific externalId (sophora:externalId).

externalId = 'my-own-externalId'

path

Queries documents under the specified structure path.

path = '/mysite/news/politics'

structureNodeUuid

Queries documents located under the structure node with the given UUID.

structureNodeUuid = '2dc1b51d-dda2-4549-b213-304a2c0b58e5'

siteUuid

Queries documents located under the site specified by the given UUID.

siteUuid = '2dc1b51d-dda2-4549-b213-304a2c0b58e5'

author

Finds documents that have been created by the user with the given username.

author = 'john_doe'

editor

Finds documents that have been last edited by the user with the given username.

editor = 'jane_doe'

state

Finds documents with the given state. Permitted values are: inProcess, released, publishAt, prePublished, published, permanentlyDeleted

state = 'published'

creationDate

Finds documents that were created at the given time.

creationDate after 2023-12-18T13:00:00

modificationDate

Finds documents that were modified at the given time.

modificationDate after 2023-12-18T13:00:00

isDeleted

Finds documents which have the sophora:isDeleted property set to the given boolean value.

isDeleted != false

has channel

Finds documents that have the specified channel name.

has channel 'my-channel'

has mixin

Finds documents that have the specified mixin.

has mixin 'sophora-mix:documentTemplate'

yellowData

Finds documents that have yellow data nodes that match the inner query. In the following example my-note:property1 and my-note:property2 are properties of the yellow data node.

yellowData[my-note:property1 = 'so cool' and my-note:property2 ~= 'happy']

Other Keywords

SoQL also includes keywords to specify which documents to find, where to search for them and the settings for the returned result:

size

Specifies the page size.

sophora-content-title = 'Hamburg' size 10

page

The page index returned by the query. Should always be used in combination with size.

sophora-content:title='Hamburg' page 2 size 10

include

Specifies criteria for the documents to be found:

• unsearchable
• deleted
• disabled

sophora-content:title='Hamburg' include deleted

search in

Defines where the query is executed. Possible values:

• database
• database-live
• collection:${solr-collection-name}

sophora-content:title='Hamburg' search in collection:default-live

order by

Defines the order of the search result. The sort direction is specified via the keywords asc for ascending and desc for a descending. To sort by multiple fields you can provide multiple comma-separated sorting arguments. The left-most argument is evaluated first.

sophora-content:title='Hamburg' order by sophora-custom:author desc, sophora:modificationDate desc