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 templateselect * from nt:base where jcr:path like '/demo-project/%' AND mgnl:template = 'standard-templating-kit:stkNews'

SELECT parent.*
FROM [mgnl:page] AS parent
INNER JOIN [mgnl:metaData] AS child ON ISCHILDNODE(child,parent)
WHERE
ISDESCENDANTNODE(parent, '/demo-project')
AND child.[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 UUIDselect * 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 insensitiveselect * 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

  • No labels

28 Comments

  1. 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'?

    1. 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 call getRows() instead of getValues() and for each row in the result set you need to call getValue("email")

  2. Manuel Helbing

    Needed a solution for how to filter for templates but return content never the less. Here it is:

    String query = "select * from [mgnl:metaData] where [mgnl:template] = 'ns:path/template'";
    NodeIterator result = QueryUtil.search("website", query, "JCR-SQL2", "mgnl:page");

    Hope it could be helpful.

    1. 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.

  3. 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

  4. Revathy Varadhan

    Is it possible to retrieve all versions of a node in SQL2 query?

    1. 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.

  5. Training Participants - FullStack Developer

    Hi,

    Can we get the depth of a node over a jcr sql2?

    1. Viet Nguyen

      Hi, you can get a node then use method "getDepth()" of javax.jcr.Item interface - the easiest way I think.

    2. 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?

  6. 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:

    SELECT registrations.*, registeredCourses.* 
    FROM [registered-course] AS registeredCourses 
    INNER JOIN [registration] as registrations 
    ON registrations.[registeredCourseId] = registeredCourses.[jcr:uuid] 
    WHERE registrations.[userId] = '%s' 
    AND (registeredCourses.[obsoleted] <> 'true' OR registeredCourses.[obsoleted] IS NULL) 
    AND registeredCourses.[date] >= CAST('%s' AS DATE) 
    ORDER BY registeredCourses.[date] DESC ;

    Here is how we get the result using Java:

    int count = maxItems;
    for (Node user : userList) {
        Session jcrSession = MgnlContext.getJCRSession("data");
        QueryManager jcrQueryManager = jcrSession.getWorkspace().getQueryManager();
        Query query = jcrQueryManager.createQuery(String.format(sql2, user.getId(), 
                            dateFromat.format(Calendar.getInstance().getTime())), Query.JCR_SQL2);
        QueryResult queryResult = query.execute();
        RowIterator iterator = queryResult.getRows();
        while (iterator.hasNext() && count>0) {
            count--;
            Row currentRow = iterator.nextRow();
            Node node = currentRow.getNode("registeredCourses");//name defined in sql2 query as an alias
            result.add(node);
        }
    }

    Hope this help as an example of using JCR SQL2.

  7. 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:

    SELECT * FROM [mgnl:page] LIMIT 10

    I get this result message:

    Query:
SELECT * FROM [mgnl:page] LIMIT(*)10; expected: <end>

    I don't really know what to do with it.

    1. Jan Haderka

      You can't set limit directly in the query string. You need to set it in the query object instead.

      session = MgnlContext.getJCRSession("website");
qm = s.getWorkspace().getQueryManager();
q = qm.createQuery("select * from [mgnl:page]", "JCR-SQL2");
q.setLimit(10);
q.execute();

      1. Oliver Sartun

        Ah, ok! That's why it didn't work.

        Thanks for your reply!

  8. Mario Ammann [X]

    I whant to execute the following SELECT.

    But now results are showing up.

    SELECT * 
FROM [nt:base]
WHERE [mgnl:group] like '%VDB%'

    Do you have an idea, whats wrong here?

    1. Richard Gange

      More like this:

      select * from [mgnl:group] where title like 'Editors%'

      1. Mario Ammann [X]

        Thank you. Title works so far, but I want to look for strings in the content-node (/VDB_xyz).

        1. 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.

    2. Tom Wespi

      Try with sql, not SQL2:

      select * from nt:base where contains("mgnl:group", '%VDB%')

      1. Mario Ammann [X]

        Thank you,

        I've tried this too, unfortunately without success → 0 nodes returned

    3. 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

      • group node type called [mgnl:group]
      • node name of the list of groups assigned to user called [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 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: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. 

  9. 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')


    1. Richard Gange

      Hello-

      No, you cannot do that. You would need to post process to weed out the nodes.

      HTH

      1. Karthik

        Thank you!

  10. Sergey Syrozhkin

    If you looked (as me) for usage of jcr:score() function for SQL2:

    select * from [%s] as p where contains(p.*, '%s') ORDER BY score(p) DESC


    The same in deprecated SQL:

    SELECT * from %s WHERE contains(., '%s') ORDER BY jcr:score() DESC

  11. 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.

  12. 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

    select * from [nt:base] where [mgnl:template] like '%text-image%'

    I would want to have sthg like this (expressed in pseudo-code, I am aware that this will not be how it would work..):

    select text from [nt:base] where [mgnl:template] like '%text-image%' → give me value of text property

    select image from [nt:base] where [mgnl:template] like '%text-image%' → give me value of image property

    Thanks guys!

    1. 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