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) | SQL2 | |
---|---|---|---|
All pages | select * from mgnl:content | //element(*, 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 | //element(*, mgnl:content)[@image] | 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 | /jcr:root/modules[1]///paragraphs[1]//[@type] | |
User with email 'eric@example.com' | select * from mgnl:user where email = 'eric@example.com' | //element(*, mgnl:user)[@email = 'eric@example.com'] | select * from [mgnl:user] where email = 'eric@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' | SELECT parent.* ** 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
28 Comments
Sven Damm
Is it possible to retrieve node data (e.g. a list of all e-mail addresses) and not users having an e-mail address?
//element(*, mgnl:user)/@email
What would be the 'Result ItemType'?
Jan Haderka
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 callgetRows()
instead ofgetValues()
and for each row in the result set you need to callgetValue("email")
Manuel Helbing
Needed a solution for how to filter for templates but return content never the less. Here it is:
Hope it could be helpful.
Jan Haderka
Hi Manuel,
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
NodeTypeParentPredicate
however 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.Jan Haderka
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
Revathy Varadhan
Is it possible to retrieve all versions of a node in SQL2 query?
Jan Haderka
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
Hi,
Can we get the depth of a node over a jcr sql2?
Viet Nguyen
Hi, you can get a node then use method "getDepth()" of javax.jcr.Item interface - the easiest way I think.
Jan Haderka
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?
Viet Nguyen
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.
Oliver Sartun
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.
Jan Haderka
You can't set limit directly in the query string. You need to set it in the query object instead.
Oliver Sartun
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?
Richard Gange
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).
Richard Gange
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.
Tom Wespi
Try with sql, not SQL2:
select * from nt:base where contains("mgnl:group", '%VDB%')
Mario Ammann [X]
Thank you,
I've tried this too, unfortunately without success → 0 nodes returned
Jan Haderka
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:group
anywhere 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
[mgnl:group]
[mgnl:group]
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 likeselect * 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 ismgnl:group
in 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.
Karthik
Hi there,
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')
Richard Gange
Hello-
No, you cannot do that. You would need to post process to weed out the nodes.
HTH
Karthik
Thank you!
Sergey Syrozhkin
If you looked (as me) for usage of jcr:score() function for SQL2:
The same in deprecated SQL:
Karthik
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.
Christopher Chard
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..):
Thanks guys!
Marvin Kerkhoff
Hi Chris,
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.
https://marketplace.magnolia-cms.com/detail/excel-export-and-import.html