CORE Query Joins

Scenario:

If you have a DB type core schema configuration where a core = table, is it possible to join and interact as if you are writing a join in a normal DB.

SQL:
Select core1.field1, core1.field2, core2.field1, core2.field2 from core1 innerjoin core2 where core1.id = core2.id
where core1.filter = "" and core.filter = ""

Is it possible to return the result from a query that joins lets say 3 cores in the following format:

"core1_id":"XXX",
"childDocuments":[
{
"core2_id":"yyy",
"core_2_fieldx":"ABC",
"childDocuments":[
{
"core3_id":"zzz",
"core_3_fieldx":"ABC",
"core3_fieldy":"123",
{
"core2_fieldy":"123",
{