N1QL Query Array

SQL++
1

Hi,

I am looking to breakout an array from an event which could have multiple acts per tour night from my data. The data is structured in event.data. The aim is to replicate the table below by writing a n1ql query. How should the N1QL query be written?

   "event": {
        "data": {
          "Tour0_Date": "2016-08-22 20:00:00.0",
          "Tour0_Id": "5050",
          "Act0_Id": "1",
          "Act0_Name": "The Bettles",
          "Act1_Id": "2",
          "Act1_Name": "Elvis Presley",
		  "Act2_Id": "3",
          "Act2_Name": "Led Zeppelin",
          "Tour_Name": "TributeNight",

2

Hi @markmiko,

First, I’d probably advise against that data structure, and switch to using an array if you can.

But beyond that, do you have a set number of “Acts” (e.g. you only have Act0_*, Act1_*, Act2_*)? If so, you could probably do a UNION to get what you want. I don’t know if this is the best way, and if you have a variable number of “Acts”, then that approach probably wouldn’t work.

3

Thanks Mathew.

Unfortunately its a variable number of acts and the model I am building is to feed into a Business Intelligence tool for slicing and dicing,

4

Since you have variable number of tours and acts, generalized implementation would be arrays of arrays.

Event: {
data : {
Tours: [
{ActId: 0, Acts: [ … ]},
{ActId: 1, Acts: [ … ]},
]
}

Once you have this, you can write the query in a generalized way.
This will give you flattened data.

SELECT t.Tourdate, t.tourID, t.TourName, a.actId,a.act_name
FROM event UNNEST data.Tours t UNNEST t.Acts as a;