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'Contact the author
