4. JCR Query - Examples

Below you will find some examples of complex queries. These examples demonstrate various aspects of JCR's query options.

Using Full-Text Search in Specific Fields

Searches for content nodes where the description field contains the phrase 'Magnolia CMS'.

//Search for text in specific property
SELECT * FROM [mgnl:content] WHERE CONTAINS(description, '%Magnolia CMS%')

//Search for text in all properties of a given node
SELECT * FROM [mgnl:content] AS result WHERE CONTAINS(result.*, '%Magnolia CMS%')

Set Date

You have to cast the current date

SELECT * FROM [mgnl:content] 
WHERE [mgnl:lastActivated] <= CAST("2024-01-01T00:00:00.000Z" AS DATE)

Date comparison

Function: Date comparison using >, <, >=, <=

SELECT * FROM [mgnl:content] WHERE [expirationDate] > '2024-12-31T23:59:59.000'

Filtering Based on Date Range

Retrieves pages published in the year 2023.

SELECT * FROM [nt:base] 
WHERE [mgnl:lastActivated] >= CAST('2023-01-01T00:00:00.000Z' AS DATE) 
AND [mgnl:lastActivated] <= CAST('2023-12-31T23:59:59.999Z' AS DATE)

[Tip: When you use nt:base in your query, you are not limiting your query to any specific node type. This is a fundamental node type defined by the JCR specification that serves as a base for all other node types.]

Nested Conditions and Logical Operators

Selects user nodes with email addresses ending in '@example.com' and located under '/users/external' or '/users/guests'.

SELECT * FROM [mgnl:user] 
WHERE [email] LIKE '%@example.com' 
AND (ISDESCENDANTNODE('/users/external') 
OR ISDESCENDANTNODE('/users/guests'))

Querying with Property Existence Check

Retrieves content nodes that have a 'specialFeature' property defined.

SELECT * FROM [mgnl:content] WHERE [specialFeature] IS NOT NULL

Searching for components

Finding pages with a specific component

select * from [nt:base] where [mgnl:template] = 'my-website-module:components/textImage'

Ordering

Ordered by creation date

SELECT * FROM [mgnl:page] WHERE [category] = 'Blog' 
ORDER BY [jcr:created]

Selecting UUID

Find a specific UUID

SELECT * FROM [nt:base] WHERE [jcr:uuid] = '0f83eea6-54e0-4dbf-9b92-c8e7dc1086b5'

Searching by node name

Sometimes you want to select a node by its name. Since you cannot query this directly, there is this solution

SELECT * FROM [mgnl:blog] WHERE name() = 'blogArticleName'