AEM DAM技术咨询：查询指定路径下原始资产数量及发布状态

原查询问题说明 #

原查询的核心问题在于检查原始rendition存在的语法错误：[jcr:content/renditions/original]不符合JCR SQL2规范，无法正确识别子节点存在；另外若仅做统计，使用SELECT *会返回冗余数据，效率低下。

针对需求的正确查询 #

1. 统计路径下所有原始资产总数 #

查询/content/dam/mySpecificpath下包含original rendition的所有dam:Asset数量：

SELECT COUNT([jcr:path]) AS totalOriginalAssets
FROM [dam:Asset] AS asset
WHERE ISDESCENDANTNODE(asset, [/content/dam/mySpecificpath])
AND EXISTS(SELECT * FROM [nt:unstructured] AS original 
           WHERE ISCHILDNODE(original, asset.[jcr:content/renditions]) 
           AND NAME(original) = 'original')

2. 统计路径下已发布的原始资产数量 #

筛选出带有jcr:cq:lastReplicated属性（表示已发布）的原始资产：

SELECT COUNT([jcr:path]) AS publishedOriginalAssets
FROM [dam:Asset] AS asset
WHERE ISDESCENDANTNODE(asset, [/content/dam/mySpecificpath])
AND EXISTS(SELECT * FROM [nt:unstructured] AS original 
           WHERE ISCHILDNODE(original, asset.[jcr:content/renditions]) 
           AND NAME(original) = 'original')
AND [jcr:cq:lastReplicated] IS NOT NULL

3. 查询资产详情及发布状态 #

若需要查看具体资产的路径、发布时间、发布动作（如激活/取消激活）：

SELECT [jcr:path], [jcr:cq:lastReplicated], [jcr:cq:lastReplicationAction]
FROM [dam:Asset] AS asset
WHERE ISDESCENDANTNODE(asset, [/content/dam/mySpecificpath])
AND EXISTS(SELECT * FROM [nt:unstructured] AS original 
           WHERE ISCHILDNODE(original, asset.[jcr:content/renditions]) 
           AND NAME(original) = 'original')

内容的提问来源于stack exchange，提问作者Rutwik Konduru