This is a collection of mini-recipes for doing JCR queries. Please add your own!
SQL (deprecated in JCR 2.0)
XPath (deprecated in JCR 2.0)
select * from mgnl:content
select * from [mgnl:page]
Pages with "News" in the title
select * from mgnl:content where title like '%News%'
//element(*, mgnl:content)[jcr:like(@title, '%News%')]
select * from [mgnl:page] where title like '%News%'
Pages where the title exactly matches "News" (case sensitive)
select * from mgnl:content where title like 'News'
//element(*, mgnl:content)[@title = 'News']
select * from [mgnl:page] where title like 'News'
STK pages that have a header image
select * from mgnl:content where image is not null
select * from [mgnl:page] where image is not null
Instances of a "Teaser" paragraph
select * from nt:base where mgnl:template = 'stkTeaser'
//*[@mgnl:template = 'stkTeaser']
select * from [nt:base] where [mgnl:template] = 'standard-templating-kit:components/teasers/stkTeaser'
Available paragraph types
select * from nt:base where jcr:path like '/modules/%/paragraphs/%' and type is not null
User with email 'firstname.lastname@example.org'
select * from mgnl:user where email = 'email@example.com'
//element(*, mgnl:user)[@email = 'firstname.lastname@example.org']
select * from [mgnl:user] where email = 'email@example.com'
|Component with template id||select * from [mgnl:component] where [mgnl:template] = 'project-site-name:components/landing/callout'|
Pages that have the word "component"
SELECT * from nt:base WHERE jcr:path like '/ftl-sample-site%' AND contains(*, 'component') AND (jcr:primaryType = 'mgnl:page' OR jcr:primaryType = 'mgnl:area' OR jcr:primaryType = 'mgnl:component') order by jcr:path
SELECT * from [nt:base] AS t WHERE ISDESCENDANTNODE('/ftl-sample-site') AND contains(t.*, 'component')
Template folders in module configuration
select * from mgnl:content where jcr:path like '/modules/%/templates'
select * from [mgnl:content] as t where ISDESCENDANTNODE('/modules') and name(t) = 'templates'
Modules that provide commands
select * from nt:base where jcr:path like '/modules/%/commands'
select * from [mgnl:content] as t where ISDESCENDANTNODE('/modules') and name(t) = 'commands'
All pages with a specific template ordered by title
SELECT p.* FROM [nt:base] AS p WHERE [mgnl:template] = 'xxx:pages/jobs' order by p.[title] asc
|Pages under given path with given template||select * from nt:base where jcr:path like '/demo-project/%' AND mgnl:template = 'standard-templating-kit:stkNews'|
** When using this query, one need to get results via getRows() instead of getNodes() since queries w/ joins can eventually return multiple different node types.
|Pages under given path with given template and category ordered by date||/jcr:root/demo-project//element(*, mgnl:metaData)[@mgnl:template = 'standard-templating-kit:pages/stkArticle']/..[@categories = 'ab9437db-ab2c-4df5-bb41-87e55409e8e1'] order by @date|
|Search a Node with a certain UUID||select * from nt:base where jcr:uuid = '7fd401be-cada-4634-93fa-88069f46297b'||SELECT * FROM [nt:base] WHERE [jcr:uuid] = '7fd401be-cada-4634-93fa-88069f46297b'|
|Search case insensitive||select * from nt:base where lower(name) like 'name_in_lowercase'||select * from [nt:base] where lower(name) like 'name_in_lowercase'|
|Search demo-project pages created in given time frame||select * from [mgnl:page] where ISDESCENDANTNODE('/demo-project/') and [jcr:created] > cast('2010-01-01T00:00:00.000+02:00' as date) and [jcr:created] < cast('2014-09-30T23:59:59.000+02:00' as date)|
|Pages in 'demo-project' which using a specific template ('stkSection' for example) and has the content just been modified by 'eric'||/jcr:root/demo-project/*[mgnl:template='standard-templating-kit:pages/stkSection']/*/content[mgnl:lastModifiedBy='eric']/../..|
Get all nodes which have a property 'date' which is not empty and this date starts at least 1 second after current midnight. Useful for events.
SELECT p.* FROM [nt:base] AS p WHERE ISDESCENDANTNODE('/') AND (p.[date] <> '' AND p.[date] > CAST('2015-11-30T00:00:01.000Z' AS DATE)) order by p.[date] asc
|Get all nodes which have any property with a given value. E.g., useful for finding references to a given uuid.||SELECT * FROM [nt:base] WHERE contains([nt:base].*, '4055e292-7b01-4075-b4c8-47d73e2e7d47')|
Java code example for 'date' query:
Date today = Calendar.getInstance().getTime(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); Calendar c = Calendar.getInstance(); c.setTime(today); // Now use today date. String path = "/"; // future events c.add(Calendar.DATE, -1); // minus 1 day String date = sdf.format(c.getTime()); String statement = "SELECT p.* FROM [nt:base] AS p WHERE ISDESCENDANTNODE('" + path + "') AND (p.[date] <> '' " + "AND p.[date] > CAST('" + date + "T00:00:01.000Z' AS DATE)) " + "order by p.[date] asc";
Note: you can use the translator to convert from one format to another.
Pages under given path with given template
Is it possible to retrieve node data (e.g. a list of all e-mail addresses) and not users having an e-mail address?
What would be the 'Result ItemType'?
Not sure how this would look like in xpath, but in sql2 you can run such query as
SELECT parent.email AS email FROM [mgnl:user] AS user
but then after calling
query.execute()you need to call
getValues()and for each row in the result set you need to call
Needed a solution for how to filter for templates but return content never the less. Here it is:
Hope it could be helpful.
thanks for the example. I've just added up to the page another query that does the same, but limits search space to only metadata of sub pages and returns page nodes in the result set directly rather then filtering them through the QueryUtil's
NodeTypeParentPredicatehowever such query requires working with rows and can't be executed using QueryUtil. Anyway, unless your server is running short on memory or you return huge number of results, there is most likely no difference in the performance.
BTW for what it's worth there's applet that can verify validity of the sql or xpath query and translate them between each other (AFAIK it's based on JCR 1.0 so don't expect to test more advanced search functions in it) http://people.apache.org/~mreutegg/jcr-query-translator/translator.html
Is it possible to retrieve all versions of a node in SQL2 query?
Not possible over JCR query. At least not directly, you might be able to construct set of queries to get from current node to frozen node from version store over uuid and then search version store for other references, but you would be banking on knowledge of internal structure of the particular repo impl so I would not go down that path.
Training Participants - FullStack Developer
Can we get the depth of a node over a jcr sql2?
Hi, you can get a node then use method "getDepth()" of javax.jcr.Item interface - the easiest way I think.
Not that I know of. However since you have the node already, you can just count the segments in the path to get the depth, no?
JCR Sql2 with 'inner join' example.
Use case: You have a table of registered courses with date/time and location of the courses and you have a table of registrations which contains the ID of the course as its foreign key. Then you want to query for registrations of a specified user (using userId) from now on (current querying time). Here is the query:
Here is how we get the result using Java:
Hope this help as an example of using JCR SQL2.
Is there a way to limit the query results?
I found this https://docs.jboss.org/jbossdna/0.7/manuals/reference/html/jcr-query-and-search.html#jcr-sql2-limits
But when I'm trying it out in the JCR Queries Dev Tool with this query for example:
I get this result message:
I don't really know what to do with it.
You can't set limit directly in the query string. You need to set it in the query object instead.
Ah, ok! That's why it didn't work.
Thanks for your reply!
Mario Ammann [X]
I whant to execute the following SELECT.
But now results are showing up.
Do you have an idea, whats wrong here?
More like this:
Mario Ammann [X]
Thank you. Title works so far, but I want to look for strings in the content-node (/VDB_xyz).
No prob. I cannot find a way to do it. ISDESCENDANTNODE doesn't seem to be working with wildcards. If I figure it out I'll let you know.
Try with sql, not SQL2:
select * from nt:base where contains("mgnl:group", '%VDB%')
Mario Ammann [X]
I've tried this too, unfortunately without success → 0 nodes returned
Could you actually try to explain in English what you are searching for? Since the query doesn't work for you, what you are trying to express by it is obviously wrong and so will be any followup since no one knows the real intentions you have.
There is no property called
mgnl:groupanywhere in Magnolia that I can think of and I sincerely doubt you had named anyting like that in your node/component yourself.
What you can see in Magnolia OOTB is either
In either case the query would not work.
In first case, you would need something like
select * from [mgnl:group] as t where name(t) = 'exact group name, no wild cards'or something like
select * from [mgnl:group] as t where ISDESCENDANTNODE([/%VDB%])(luckily for you by default there's no structure in groups unless you changed group manager)
In second case what you want is
select * from [nt:base] as t where ISDESCENDANTNODE([/%/mgnl:group/%]) and contains(t.*,'VDB')tho that works only on indexed words and i'm not sure if partial mapping is considered as match or not. Anyway, point I was trying to make is that it's not the property name that is
mgnl:groupin this case either.
You might want to provide example of how the structure looks like in JCR and highlight what you want to find for anyone to really help you putting together right query.
Consider an query
SELECT p.* FROM [mgnl:content] AS p WHERE ISDESCENDANTNODE('/ParentNode/ChildNode/GrandChildNode')
Is it possible to give ChildNode as wildcard entry? So final query will be something like
SELECT p.* FROM [mgnl:content] AS p WHERE ISDESCENDANTNODE('/ParentNode/*/GrandChildNode')
No, you cannot do that. You would need to post process to weed out the nodes.
If you looked (as me) for usage of jcr:score() function for SQL2:
The same in deprecated SQL:
Is it possible to have results with multiple column values? I am looking for node and it's last modified date information in the query results.
Is there a way to return actual values with queries in the JCR Tools App?
The results are always just the node paths, but sometimes it would be great to display the actual values
So instead of just doing
I would want to have sthg like this (expressed in pseudo-code, I am aware that this will not be how it would work..):
you might want to use the Groovy Scripts for such solutions. There is a very near example on my Website https://www.magnolia-central.com/code/groovy-scripts/create-list-of-used-templates
Otherwise you can also use our Excel Export Import module for such operations. It's more userfriendly and you can add actions for endusers to export these without having the need of give them access to groovy.