JOIN on two sub queries

SQL++
1

Hey I am working with this query and trying to get some direction on what and how would be the best way to execute my need. I referenced this post SELECT 2 ARRAY_AGG in 1 query. I am trying to do something similar I want to nest a couple of objects. Currently I have this doc once I perform the query against couchbase.

[
{
“HS”: [
{
“date”: “2019-03-15”,
“iC”: [
{
“cnts”: 6,
“hs”: “01”
}
],
“totalAC”: 6,
“totalDmn”: 30
},
{
“date”: “2019-03-16”,
“iC”: [
{
“cnts”: 1,
“hs”: “01”
},
{
“cnts”: 1,
“hs”: “02”
},
{
“cnts”: 1,
“hs”: “03”
}
],
“totalAC”: 3,
“totalDmn”: 13
}
],
“WW”: [
{
“CWAC”: [
{
“SW”: “10.00.00 12.00.00”,
“tCT”: “02”,
“wCnt”: 2
},
{
“SW”: “10.00.00 14.00.00”,
“tCT”: “02”,
“wCnt”: 1
},
{
“SW”: “13.00.00 17.00.00”,
“tCT”: “02”,
“wCnt”: 1
},
{
“SW”: “14.00.00 16.00.00”,
“tCT”: “02”,
“wCnt”: 2
}
],
“date”: “2019-03-15”
},
{
“CWAC”: [
{
“SW”: “1200 1600”,
“tCT”: “02”,
“wCnt”: 1
},
{
“SW”: "14.00.00 ",
“tCT”: “00”,
“wCnt”: 1
},
{
“SW”: "21.00.00 ",
“tCT”: “00”,
“wCnt”: 1
}
],
“date”: “2019-03-16”
}
]
}
]

Here is the query I am using:

SELECT
(SELECT d.date AS date, SUM(d.cnts) AS totalAC, SUM(d.dmn) as totalDmn, ARRAY_AGG({d.hs, d.cnts}) AS iC
FROM d1.hS AS d GROUP BY d.date ORDER BY d.date) AS HS,
(SELECT d.date AS date, ARRAY_AGG({d.tCT,d.SW, d.wCnt}) as CWAC
FROM d1.times AS d GROUP BY d.date ORDER BY d.date) AS WW
FROM (SELECT
(SELECT t.hs, t.date, SUM(t.cnts) AS cnts, SUM(t.dmn) as dmn FROM t1.data AS t GROUP BY t.hs, t.date) AS hS,
(SELECT t.date as date, t.tCT, t.sW || " " || CASE WHEN t.eW
IS NOT NULL THEN t.eW
ELSE “”
END as SW, COUNT(t.sW) as wCnt FROM t1.data AS t GROUP BY t.date, t.tCT, t.sW, t.eW) as times
FROM (SELECT data
LET data = (SELECT t.sAN AS acct, t.mD as date, t.hSC AS hs, t.dim as dmn, t.cd as tCT,
t.sT as sW, t.eT as eW, COUNT(1) AS cnts
FROM BUCKET_Viewable AS t
WHERE t.sAN IN [“test”]
AND t.mD BETWEEN “2019-03-01” AND “2019-03-25”
GROUP BY t.mD, t.dim, t.sAN, t.hSC, t.cd, t.sT, t.eT
) ) AS t1
) AS d1 ;

I would want the data to be in this format ideally:

[
{
“HS”: [
{
“date”: “2019-03-15”,
“iC”: [
{
“cnts”: 6,
“hs”: “01”
}
],
“WW”: [
{
“tCT”: “02”,
“CWAC”: [
{
“SW”: “10.00.00 12.00.00”,
“wCnt”: 2
},
{
“SW”: “10.00.00 14.00.00”,
“wCnt”: 1
},
{
“SW”: “13.00.00 17.00.00”,
“wCnt”: 1
},
{
“SW”: “14.00.00 16.00.00”,
“wCnt”: 2
}
]
}
],
“totalAC”: 6,
“totalDmn”: 30
},
{
“date”: “2019-03-16”,
“iC”: [
{
“cnts”: 1,
“hs”: “01”
},
{
“cnts”: 1,
“hs”: “02”
},
{
“cnts”: 1,
“hs”: “03”
}
],
“WW”: [
{
“tCT”: “02”,
“CWAC”: [
{
“SW”: “1200 1600”,
“wCnt”: 1
}
]
},
{
“tCT”: “01”,
“CWAC”: [
{
“SW”: "14.00.00 ",
“wCnt”: 1
}
],

      },
      {
        "tCT": "00",
        "CWAC": [
          {
            "SW": "21.00.00 ",
            "wCnt": 1
          }
        ]
      }
    ],
    "totalAC": 3,
    "totalDmn": 13
  }
]

}
]

Initially I was trying to nest the ARRAY_AGG(t.tCT, “xxx”:ARRAY_AGG({x,y})) but this did not work as i referenced in the other forum post. What I am trying to do now is a join on the two subqueries as they both have date fields, but when I do that is says something as an error related to the key space.

Any ideas on how to do this either this or anther way?

Thanks

2 
SELECT t3.date, t3.totalAC, t3.totalDmn, t3.iC,
       (SELECT t5.tCT, ARRAY_AGG({t5.SW, t5.wCnt}) AS CWAC
        FROM t3.times AS t5
        GROUP BY t5.tCT)  AS WW
FROM ( SELECT t2.date, t2.totalAC, t2.totalDmn,
              (SELECT d.hs, SUM(d.cnts) AS cnts FROM t2.hs AS d GROUP BY d.hs) AS iC,
              (SELECT d.tCT, d.SW, SUM(d.wCnts) AS wCnt FROM t2.times AS d GROUP BY d.tCT, d.SW) AS times
       FROM ( SELECT t1.date AS date,
              SUM(t1.cnts) AS totalAC,
              SUM(t1.dmn) AS totalDmn,
              ARRAY_AGG({t1.hs, t1.cnts}) AS hs,
              ARRAY_AGG({t1.wCnts, t1.tCT, "SW":t1.sW || " " || IFNULL(t1.eW,"")}) AS times
              FROM (SELECT t.sAN AS acct,
                            t.mD as date,
                            t.hSC AS hs,
                            t.dim as dmn,
                            t.cd as tCT,
                            t.sT as sW,
                            t.eT as eW,
                            COUNT(1) AS cnts,
                            COUNT(t.sW) as wCnts
                    FROM  BUCKET_Viewable  AS t
                    WHERE t.sAN IN ["test"] AND t.mD BETWEEN "2019-03-01" AND "2019-03-25"
                    GROUP BY t.mD, t.dim, t.sAN, t.hSC, t.cd, t.sT, t.eT
              ) AS t1
       GROUP BY t1.date) AS t2
     ) AS t3;
1 Like