AEM/CQ: UI for one-off JCR-SQL / SQL2 / XPath queries with tabular output?

1
votes

I am doing a little usage research in our AEM installation. I would like to find all instances of a component and see what value they have for a certain configuration option. So basically, I want to select the title and myOption properties from all nodes with sling:resourceType myComponent.

I believe the JCR-SQL2 query would be:

SELECT [title], [myProperty] FROM [nt:base]
where [sling:resourceType] like 'path/to/my/component'

My problem is, I can't find a UI in which to run this query.

  • The CRX:DE Query tool (CRX:DE > Tools > Query) accepts XPath or SQL2, but only shows me the path to the matching nodes. Sure I can click on each one to zoom to that node, but that's tedious. I want a tabular output with title and property value.

  • The Query Builder Debugger (/libs/cq/search/content/querydebug.html) uses its own URL-based query syntax and only outputs JSON. I recreated the query in terms of URL parameters...

    property=sling:resourceType
property.value=path/to/my/component
p.hits=selective
p.properties=title myProperty
p.limit=-1

    ... But I still don't want to write code to parse the JSON and display a table.

  • I understand that I could write a class / component to display this but again, this is a one-off query and I am just digging around. I'd prefer not to have to write Java, JS, or JSP just to send and display this query.

So my question is...

Is there another query UI buried somewhere in CQ that lets me put in one-off JCR-SQL / SQL2 / XPath queries with columns specified, and get a tabular output, the way you would with a SQL prompt or tool?

I would settle for a query builder incantation to produce pretty-printed JSON, so I could at least eyeball the JSON results.

2
aemjcr-sql2

2 Answers

2
votes

You can use the bulk editor /etc/importers/bulkeditor.html . It is a great match for your requirement

Just set root path as /content, uncheck content mode and for query parameters use "sling:resourceType":path/to/my/component. Set custom properties to title,myOption. It display's the result in a table.

For more : http://docs.adobe.com/docs/en/cq/5-6/administering/bulk_editor.html

Bulk editor only works for simple queries,for complex queries you can try this : http://adobe-consulting-services.github.io/acs-aem-tools/features/query-editor.html .

1
votes

Consider using the Groovy Console. You may easily write a short script producing desired output:

def QUERY = "SELECT [jcr:lastModified], [jcr:title] FROM [nt:base] WHERE [sling:resourceType] = 'foundation/components/title'"

session.workspace.queryManager.createQuery(QUERY, "JCR-SQL2").execute().rows.each { r ->
    println r.values.collect { it.string }.join('\t')
}

Script can be simply pasted into the text area, no need to compile or upload anything to the server.