How to get column wise data using AEM query SQL2?

Question

I am running a following AEM Query SQL2 on CRXDE and it is successfully returning me nodes as per following given screenshot.

But I need data like column wise (jcr properties) like SQL table. Can anyone help me if it is possible.

Answer 1

You can't do this with CRXDE. It shows only the path of the most outer node, even if the query has multiple columns. This is especially limiting, if your query uses joins.

In your case I would recommend the Query Builder. It has a totally different syntax, but the JSON or XML result contains all data you need.

I don't know other tools. As AEM developer I usually write a quick & dirty servlet, and let it run on my local instance (with production content)

Query Builder Debugger

http://localhost:4502/libs/cq/search/content/querydebug.html

Example Query

path=/content/we-retail/language-masters/en/experience
property=sling:resourceType
property.value=weretail/components/content/image
p.hits=full
p.nodedepth=2

Resulting JSON Query

http://localhost:4502/bin/querybuilder.json?p.hits=full&p.nodedepth=2&path=%2fcontent%2fwe-retail%2flanguage-masters%2fen%2fexperience&property=sling%3aresourceType&property.value=weretail%2fcomponents%2fcontent%2fimage http://localhost:4502/bin/querybuilder.json?p.hits=full&p.nodedepth=2&path=%2fcontent%2fwe-retail%2flanguage-masters%2fen%2fexperience&property=sling%3aresourceType&property.value=weretail%2fcomponents%2fcontent%2fimage

Documentation

https://docs.adobe.com/content/help/en/experience-manager-64/developing/platform/query-builder/querybuilder-api.html

In your case especially see: Refining What Is Returned

You will find much more with Google, as the Query Builder is pretty old in AEM/CQ.