Query to find list of pages with a particular property

[chandrareddy]

Hi Team,

I am trying to get list of documents with below property (ingredion:productId). I able to fetch the properties but I need only the properties with type Long.

How to query that, can someone suggest.

Below is the query i am using to fetch the records.

But I want only the records whose property is property type is long.

I tried property.Type=Long but still all records are coming.

path=/content/dam/xyz
type=dam:Asset
1_property=jcr:content/metadata/ingredion:productIds

Thanks,

Chandra

[Sanket_Kumbharkhane]

Hi @chandrareddy ,

you can refer to below examples: 

Using Xpath: 

path=/search/in/path
type=nt:unstructured
property=ingredion:productId
property.operation=exists
property.Type=Long
p.limit=-1

execute in =>  /libs/cq/search/content/querydebug.html

Or

SQL2 :

SELECT * FROM [nt:unstructured] AS node
WHERE ISDESCENDANTNODE(node, "/search/in/path")
AND PROPERTY(node.[ingredion:productId], "Long") LIKE "%"

[chandrareddy]

Thanks for the reply sanket. 

This is fetching all records.property.Type=Long  is not working it seems. 

[Sanket_Kumbharkhane]

Then best way to write a small program and fetch the result using Querybuilder. When you get the results, compare the property type with Long or String [] and filter out. Use Servlet so that you can get the result quickly using servlet path and without including any component/template code. Let me know if you need more help with this.

[Kiran_Vedantam]

Hi @chandrareddy,

You can use this SQL Query:

SELECT * FROM [nt:unstructured] AS node
WHERE ISDESCENDANTNODE(node, "CONTENT-PATH")
AND CONTAINS([PROPERTY-NAME], "PROPERTY-VALUE")

Execute this in your CRX-DE query section. You can find similar queries here

Hope this helps.

Thanks,

Kiran Vedantam.

[chandrareddy]

thanks for reply kiran. property.Type is String for all docs around 5k. only few may be 10 to 20 mistakenly created with property.Type=Long, need to identify those using qury

[chandrareddy]

https://gist.github.com/floriankraft/8b3720464318cd5cd9e2 helped me to find the required list.

SELECT * FROM [nt:unstructured] AS node
WHERE ISDESCENDANTNODE(node, "/search/in/path")
AND PROPERTY(node.[propertyName], "DATE") LIKE "%"

 Thanks a lot.  

[Anudeep_Garnepudi · Accepted solution]

@chandrareddy 

I think you can not directly get the multivalued property using Query. Do it this way, query for STRING which will return both String and String[] and then check if the value is multiple or not.

Query: "SELECT * FROM [nt:unstructured] AS node WHERE ISDESCENDANTNODE(node, '/content/your/path') AND PROPERTY(node.[ingredion:productId], 'String') LIKE '%'"

For each result check if property is  Multivalued.

while (resultNodes.hasNext()) {
    ...
    if (resultNode.getProperty("ingredion:productId").isMultiple()) {
        // Logic
    }
}