Need to know the number of asset type in DAM

[ranadyutis95334]

Can anyone tell me a query to get the count of type of asset in DAM.

For example number of images,number of pdf etc

[Jaideep_Brar]

Hi, SQL2 does not provide a distinct/count function that you can use in the query but you can query for "dam:MIMEtype" property to list all the assets by their corresponding asset types.

Select * from [nt:unstructured] where [dam:MIMEtype] is not null and ISDESCENDANTNODE('/content/dam/geometrixx-outdoors/')

[aneeta45259594]

Hello ranadyutis95334917​

You can run the following query to get the results for specific Mime Types in crx/de and it will give you a count when the results execute. The following query shows examples of png and jpeg images.

select * from [dam:Asset] where [jcr:content/metadata/dam:MIMEtype] = "image/png"
select * from [dam:Asset] where [jcr:content/metadata/dam:MIMEtype] = "image/jpeg"

You can find a list of Mime Types at Assets Supported Formats

Another alternative way is to execute the query using the QueryBuilder in AEM. Steps

1) Go to http://localhost:4502/libs/cq/search/content/querydebug.html

2) In the Query Builder Debugger field, type the following and click search

type=dam:Asset
1_property=jcr:content/metadata/dam:MIMEtype
1_property.value=image/png
p.limit=-1

3) It will execute the query and give you a quick count of number of assets of each type

Best Regards,

Aneet Arora

[Former Community Member · Accepted solution]

Hi,

Please have a look at below queries. They worked as expected for me -

Using SQL2:

Select * from [nt:unstructured] where  [dam:MIMEtype] like '%jpeg%' and ISDESCENDANTNODE('/content/dam/we-retail/en/activities/biking')

FYI: When I try to achieve the same with the same with - select * from [dam:Asset] where [jcr:content/metadata/dam:MIMEtype] = "image/jpeg", I see the following error:

ERROR: The query read or traversed more than 100000 nodes. To avoid affecting other tasks, processing was stopped.

Hence I've suggested the above query as it is working as expected.

Using query builder:

path=/content/dam/we-retail/en/activities/biking

type=dam:Asset

1_property=jcr:content/metadata/dc:format

1_property.value=image/jpeg

FYI: When I try to achieve the same with -

type=dam:Asset

1_property=jcr:content/metadata/dam:MIMEtype

1_property.value=image/png

p.limit=-1

I see the following error:

ERROR: The query read or traversed more than 100000 nodes. To avoid affecting other tasks, processing was stopped.

Hence I've suggested the above query as it is working as expected.

Using XPath:

/jcr:root/content/dam/we-retail/en/activities/biking//element(*, dam:Asset)

[

(jcr:content/metadata/@dc:format = 'image/jpeg')

]

Hope this helps!

Regards,

Lavanya Malyala

[smacdonald2008]

Great responses community!