Expand my Community achievements bar.

Don’t miss the AEM Skill Exchange in SF on Nov 14—hear from industry leaders, learn best practices, and enhance your AEM strategy with practical tips.

Need SQL2 query for AEM 6.2

Avatar

Level 2

Hi,

Please help me in writing SQL2 query to search for all the pages containing custom component property.

I have custom "bodycopy" component which has a property called "myproperty". Below are the sample paths. 

Conditions:
I can have any number of bodycopy components in "head-parsys" and at any level.
Fulltext search
Query should be performed on cq:Page

/content/mysite/jcr:content/head-parsys/bodycopy
/content/mysite/jcr:content/head-parsys/bodycopy_0
/content/mysite/jcr:content/head-parsys/parsys_1/bodycopy
/content/mysite/jcr:content/head-parsys/parsys_1/bodycopy_0

Below is the sample: 

select * from [cq:Page] as a where contains([dynamic/path/to/mycomponent/myproperty],'test') and isdescendantnode(a, '/content/mysite')

Thanks in advance.

6 Replies

Avatar

Level 10

What is the result set of this query?

Avatar

Level 2

CQ:Page

SELECT * FROM [cq:Page] AS s WHERE ISDESCENDANTNODE([/content/mysite]) and CONTAINS(s.[jcr:content/head-parsys/mycomponent/myproperty], 'test') - Working

"head-parsys/mycomponent" is dynamic and can be of any levels. when I replace the values with * also not working

SELECT * FROM [cq:Page] AS s WHERE ISDESCENDANTNODE([/content/mysite]) and CONTAINS(s.[jcr:content/*/*/myproperty], 'test') - Not Working in AEM 6.2. 

Avatar

Level 2

I have gone through it. But my question is when we search for component properties, the result will not be of type cq:Page, instead nt:unstructured. Below is the sample result:

/content/mysite/jcr:content/head-parsys/bodycopy
/content/mysite/jcr:content/head-parsys/bodycopy_0
/content/mysite/jcr:content/head-parsys/parsys_1/bodycopy
/content/mysite/jcr:content/head-parsys/parsys_1/bodycopy_0

So, the below query is working:

SELECT * FROM [nt:unstructured] AS s WHERE ISDESCENDANTNODE([/content/mysite]) and CONTAINS(s.[myproperty], 'test'); 

The same query is not qorking with [cq:Page]. So, is it not a expensive call to query on [nt:unstructured]? or that is the only way to search for component properties? 

Avatar

Level 10

"The same query is not qorking with [cq:Page]" 

When working with cq:page - you are restricted on the props you can add to this node, while using nt:unstructured - you can add any prop you need. So you have more flexibilty when working with nt:unstructured.

You cannot add a property named myproperty to a cq:page node. That is why the query will not work,  

Avatar

Level 2

@thiszparveen,

You can try something like this:

SELECT parent.* FROM [cq:Page] AS parent INNER JOIN [nt:base] AS child ON ISDESCENDANTNODE(child, parent) WHERE ISDESCENDANTNODE(parent, '/content/geometrixx/en/products') AND child.[sling:resourceType]='geometrixx/components/title'

This will return all [cq:Page] nodes that have nodes with property [sling:resourceType] set to geometrixx/components/title.

You can modify this for your property.