Expand my Community achievements bar.

Dive into Adobe Summit 2024! Explore curated list of AEM sessions & labs, register, connect with experts, ask questions, engage, and share insights. Don't miss the excitement.
SOLVED

Need to know the number of asset type in DAM

Avatar

Level 4

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

1 Accepted Solution

Avatar

Correct answer by
Former Community Member

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

View solution in original post

4 Replies

Avatar

Employee Advisor

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/')

Avatar

Level 5

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

Avatar

Correct answer by
Former Community Member

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

The following has evaluated to null or missing: ==> liqladmin("SELECT id, value FROM metrics WHERE id = 'net_accepted_solutions' and user.id = '${acceptedAnswer.author.id}'").data.items [in template "analytics-container" at line 83, column 41] ---- Tip: It's the step after the last dot that caused this error, not those before it. ---- Tip: If the failing expression is known to be legally refer to something that's sometimes null or missing, either specify a default value like myOptionalVar!myDefault, or use <#if myOptionalVar??>when-present<#else>when-missing. (These only cover the last step of the expression; to cover the whole expression, use parenthesis: (myOptionalVar.foo)!myDefault, (myOptionalVar.foo)?? ---- ---- FTL stack trace ("~" means nesting-related): - Failed at: #assign answerAuthorNetSolutions = li... [in template "analytics-container" at line 83, column 5] ----