Query syntax is wrong - Can we make a query on data from different buckets

Couchbase Server
1

Hi ! I have a query that looks like this:

SELECT _.city,
travel-sample.inventory.airport.airportname,
_.ANN
FROM travel-sample.inventory.airport
JOIN (
SELECT *
FROM analysis.additional_data.temperatures
JOIN analysis.additional_data.cities ON analysis.additional_data.temperatures.state = analysis.additional_data.cities.state_id) AS _ ON travel-sample.inventory.airport.city = _.city;

The problem being a returned error as such:

[
{
“code”: 24042,
“msg”: “Cannot resolve ambiguous alias reference for identifier analysis (in line 8, at column 47)”,
“query_from_user”: “SELECT _.city,\n travel-sample.inventory.airport.airportname,\n _.ANN\nFROM travel-sample.inventory.airport\n JOIN (\n SELECT *\n FROM analysis.additional_data.temperatures\n JOIN analysis.additional_data.cities ON analysis.additional_data.temperatures.state = analysis.additional_data.cities.state_id) AS _ ON travel-sample.inventory.airport.city = _.city;”
}
]

The travel-sample is a bucket and the analysis one is another bucket. Can we make queries on data from different buckets ?

2

Also, I have tried copying data from one bucket to another bucket.scope.collection and it sends an error:

INSERT INTO analysis.additional_data.airport (KEY _k, VALUE _v)
SELECT META().id _k, _v
from travel-sample _v
WHERE type = ‘airport’;

[
{
“code”: 24000,
“msg”: "Syntax error: In line 1 >>INSERT INTO analysis.additional_data.airport (KEY _k, VALUE _v)<< Encountered "(" at column 48. ",
“query_from_user”: “INSERT INTO analysis.additional_data.airport (KEY _k, VALUE _v)\nSELECT META().id _k, _v\nfrom travel-sample _v\nWHERE type = ‘airport’;”
}
]

Whatever the configuration of the ` in the name of the collections

3

You can’t provide full multi-part qualification of fields - you must refer to them as alias.field; so try:

SELECT _.city, airport.airportname, _.ANN
FROM `travel-sample`.inventory.airport
JOIN
(
    SELECT *
    FROM analysis.additional_data.temperatures
    JOIN analysis.additional_data.cities
    ON temperatures.state = cities.state_id
) AS _
ON airport.city = _.city

(Of course you’ll need appropriate indices to support the query.)

Your INSERT would be:

INSERT INTO analysis.additional_data.airport (KEY _k, VALUE _v)
SELECT META().id _k, _v
FROM `travel-sample` _v                                                                                                             
WHERE type = 'airport'

You have to quote any field or keyspace etc. name that includes a minus sign using Grave Accent (“backtick”) characters; i.e.

`travel-sample`

always. This certainly works for me:

cbq> INSERT INTO analysis.additional_data.airport (KEY _k, VALUE _v)
   2 SELECT META().id _k, _v
   3 FROM `travel-sample` _v
   4 WHERE type = 'airport';
{
    "requestID": "9839e375-9897-4bba-a847-e41971c1c670",
    "signature": null,
    "results": [
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "348.539458ms",
        "executionTime": "348.449651ms",
        "resultCount": 0,
        "resultSize": 0,
        "serviceLoad": 3,
        "mutationCount": 1968
    }
}

HTH.

4

Is there q necessity to copy the data from the other bucket into the new bucket to query or is it possible to inter_bucket query ?

5

You can join across buckets. Your example SELECT does just that - joining analysis and travel-sample and runs without error when the correct indices exist.

6

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.