Multiple join from different bucket

SQL++
1

Hi,
I have 2 documents with names “first” and “second” residing in different buckets “default” and “cache” respectively.

bucket: default , document name:first

{
  "services": [
    {
      "serviceId": "ABC",
      "serviceType": "AAA"    
    },
    {
      "serviceId": "CDE",
      "serviceType": "BBB",      
      "serviceLocation": {
        "cityName": "Singapore",
        "countryName": "Singapore"
      }
    },
	 {
      "serviceId": "FGH",
      "serviceType": "CCC",     
      "serviceLocation": {
        "cityName": "Sydney",
        "countryName": "Australia"
      }
    }
  ]
}

bucket: cache , document name:second

	{
	  "availabilityInfo": [		
		{
		  "serviceId": "CDE",
		  "availability": 99.8,
		  "starttime": "11111222",
		  "endtime": "11113333"
		},
		{
		  "serviceId": "FGH",
		  "availability": 99.7,
		  "starttime": "11111222",
		  "endtime": "11113333"
		}
	  ]
	}

how to create a n1q1 query with left join to retrieve the following output, please assist.

{
  "services": [
    {
      "serviceId": "ABC",
      "serviceType": "AAA"
      
    },
    {
      "serviceId": "CDE",
      "serviceType": "BBB",
      "serviceLocation": {
        "cityName": "Singapore",
        "countryName": "Singapore"
      },
	  "availabilityInfo": {
	  "availability": 99.8,
		  "starttime": "11111222",
		  "endtime": "11113333"
	  }
    },
	 {
      "serviceId": "FGH",
      "serviceType": "CCC",
      "serviceLocation": {
        "cityName": "Sydney",
        "countryName": "Australia"
      },
	   "availabilityInfo": {
	    "availability": 99.7,
		  "starttime": "11111222",
		  "endtime": "11113333"
	  }
    }
	]
	}
2

N1QL joins are through document key. one of the document needs to contain other document document key.

If you don’t have relation you can modify data model to create one OR do two independent queries and JOIN through arrays

The following first SELECT shows how to produce desired results with arrays. Second SELECT shows how to JOIN when first document has document key of second as “av_id”

INSERT INTO default VALUES("name:first", { "services": [ { "serviceId": "ABC", "serviceType": "AAA" }, { "serviceId": "CDE", "serviceType": "BBB", "serviceLocation": { "cityName": "Singapore", "countryName": "Singapore" } }, { "serviceId": "FGH", "serviceType": "CCC", "serviceLocation": { "cityName": "Sydney", "countryName": "Australia" } } ] , "av_id": "name:second"});
INSERT INTO default VALUES("name:second", { "availabilityInfo": [    { "serviceId": "CDE", "availability": 99.8, "starttime": "11111222", "endtime": "11113333" }, { "serviceId": "FGH", "availability": 99.7, "starttime": "11111222", "endtime": "11113333" } ] });

SELECT  ARRAY OBJECT_ADD (s,"availabilityInfo", FIRST a FOR a IN av WHEN s.serviceId = a.serviceId END) FOR s IN  d.services END  AS services
FROM default AS d USE KEYS "name:first"
LET av = ARRAY_FLATTEN((SELECT RAW availabilityInfo FROM default USE KEYS "name:second"),2);

OR

SELECT  ARRAY OBJECT_ADD (s,"availabilityInfo", FIRST a FOR a IN d2.availabilityInfo WHEN s.serviceId = a.serviceId END) FOR s IN  d.services END  AS services
FROM default AS d JOIN default AS d2 ON KEYS d.av_id;
3

Thanks works fine but unfortunately I missed to include that same field “availability” exists in both the documents and I need to fetch the availability only from the second document . I would also need to sort by first document service id . Please let me know the possibility

4

Could you please update your sample document and output where is availability and where do you want.

5

In addition, please advise on how to sort by service id of first document .

please find the output below:-

[
{
“services”: [
{
“availability”: “1”,
“serviceId”: “ABC”,
“serviceType”: “AAA”
},
{
“availability”: “1”,
“availabilityInfo”: {
“availability”: 99.8,
“endtime”: “11113333”,
“serviceId”: “CDE”,
“starttime”: “11111222”
},
“serviceId”: “CDE”,
“serviceLocation”: {
“cityName”: “Singapore”,
“countryName”: “Singapore”
},
“serviceType”: “BBB”
},
{
“availability”: “1”,
“availabilityInfo”: {
“availability”: 99.7,
“endtime”: “11113333”,
“serviceId”: “FGH”,
“starttime”: “11111222”
},
“serviceId”: “FGH”,
“serviceLocation”: {
“cityName”: “Sydney”,
“countryName”: “Australia”
},
“serviceType”: “CCC”
}
]
}
]

6

Where is availability in the original documents.

7

As mentioned I missed to include in the first document. Please find it here
{
“av_id”: “name:second”,
“services”: [
{
“serviceId”: “ABC”,
“serviceType”: “AAA”,
“availability”: “1”
},
{
“serviceId”: “CDE”,
“serviceLocation”: {
“cityName”: “Singapore”,
“countryName”: “Singapore”
},
“serviceType”: “BBB”,
“availability”: “1”
},
{
“serviceId”: “FGH”,
“serviceLocation”: {
“cityName”: “Sydney”,
“countryName”: “Australia”
},
“serviceType”: “CCC”,
“availability”: “1”
}
]
}

Now my priorty is to sort by the serviceId of first document in the ascending order , please let me know this first. (The actual data has services disordered so there is a requirement to order it ) .Thanks.

8

Requires CB 4.6.3 and above

INSERT INTO default VALUES("name:first", { "services": [ { "availability": "1", "serviceId": "FGH", "serviceType": "CCC", "serviceLocation": { "cityName": "Sydney", "countryName": "Australia" } }, { "availability": "1","serviceId": "ABC", "serviceType": "AAA" }, { "availability": "1", "serviceId": "CDE", "serviceType": "BBB", "serviceLocation": { "cityName": "Singapore", "countryName": "Singapore" } }] , "av_id": "name:second"});
INSERT INTO default VALUES("name:second", { "availabilityInfo": [    { "serviceId": "CDE", "availability": 99.8, "starttime": "11111222", "endtime": "11113333" }, { "serviceId": "FGH", "availability": 99.7, "starttime": "11111222", "endtime": "11113333" } ] });

SELECT  (SELECT RAW s FROM (ARRAY OBJECT_ADD (s,"availabilityInfo", FIRST a FOR a IN av WHEN s.serviceId = a.serviceId END) FOR s IN  d.services END) AS s ORDER BY s.serviceId)  AS services
FROM default AS d USE KEYS "name:first"
LET av = ARRAY_FLATTEN((SELECT RAW availabilityInfo FROM default USE KEYS "name:second"),2);

SELECT  (SELECT RAW s FROM (ARRAY OBJECT_ADD (s,"availabilityInfo", FIRST a FOR a IN d2.availabilityInfo WHEN s.serviceId = a.serviceId END) FOR s IN  d.services END) AS s ORDER BY s.serviceId)  AS services
FROM default AS d JOIN default AS d2 ON KEYS d.av_id;
9

Thanks. The couchbase version I have is is 4.5 . with the 4.5 version will I be able to do a sort by serviceId

10

You can try this,

SELECT (
       SELECT RAW s FROM (SELECT ARRAY OBJECT_ADD (s,"availabilityInfo", FIRST a FOR a IN av WHEN s.serviceId = a.serviceId END)
                             FOR s IN  d.services END AS services
             FROM default AS d USE KEYS "name:first"
             LET av = ARRAY_FLATTEN((SELECT RAW availabilityInfo FROM default USE KEYS "name:second"),2)) AS d
       UNNEST d.services AS s
       ORDER BY s.serviceId) AS services;
11

Thanks this works fine.