SQL2 Query to get all All AEM Assets having width greater than a given value

[S__k__Agarwal]

I have one requirement to write a query to get all the AEM Assets having width greater than a given value.

I tried to write the following SQL2 Query but it did not work.

SELECT asset.* FROM [dam:Asset] AS asset INNER JOIN [nt:unstructured] AS metadataNode ON ISCHILDNODE(metadataNode, asset) WHERE ISDESCENDANTNODE(asset, "/content/dam/wknd-shared") AND metadataNode.[jcr:content/metadata/tiff:ImageWidth] >= CAST("1600" AS LONG)

Please help me to write this query which return the expected response.

@aanchal-sikka @arunpatidar @kautuk_sahni 

@EstebanBustamante @Sudheer_Sundalam @Jörg_Hoh 

@lukasz-m @Rohan_Garg @Sady_Rifat 

[lukasz-m · Accepted solution]

Hi @S__k__Agarwal,

I am not sure why your query is so complex. Below works for me, hope this will solve your problem.

SELECT * FROM [dam:Asset] AS a WHERE ISDESCENDANTNODE([/content/dam/wknd-shared]) AND a.[jcr:content/metadata/tiff:ImageWidth] >= CAST("1600" AS LONG)

or

SELECT * FROM [dam:Asset] AS a WHERE ISDESCENDANTNODE([/content/dam/wknd-shared]) AND PROPERTY(a.[jcr:content/metadata/tiff:ImageWidth], "LONG") >= "1600"