Joining two documents / query + variable

.NET SDK
, ,
1

Hi

I am trying to join a variable(from a query) like below but not working


var str2= "1234,7890";

 var query = $"SELECT b.ID, A.CustId FROM `Test` A UNNEST Custhps b WHERE A.`$Type` = \"Customer\"  AND A.CustId IN ["+ str2 +"]";

            var result = await cluster.QueryAsync<Cust>(query);
            var items = await result.Rows.ToListAsync<Cust>();


            var query1 = $"SELECT A.Id, B.LocNm, r.GtmuCustId FROM `Test` A UNNEST A.SrLoc AS B JOIN "+ items.ToArray() +" AS r ON A.RteId = r.RteID WHERE (A.`$Type` = \"Route\" AND B.LocTypCdv !=\"05\")";

            var result1 = await cluster.QueryAsync<Cust2>(query1);
            var items1 = await result1.Rows.ToListAsync<Cust2>();

The 'Item.ToArray is from the first query . I tried with list , array but not working .Is there a way for me to pass this into the second query .

Please let me know your thoughts

Thanks

2

My recommendation here would be to look at using Linq2Couchbase. It will probably make your job a lot easier by writing the query for you.

Another option might be to use query parameters rather than string concatenation. This is more secure as it projects against injection attacks and can allow you to prepare the query for improved performance on repeat queries. You can add named parameters such as $Item in your query text and then use .AddNamedParameter("Item", items) to add the parameter to your QueryOptions.

3

Thanks Do we have an example for the query option thing ? Alos everything inside IN[“”] is an array or JSON ?

If it is json then can i create a JSON structure for rid and Custid and put it inside

Please let me know your thoughts .

Thanks;

4

I tried something like this .still no luck

SELECT A.Rid, B.LocNm, r.Custid FROM TestA A UNNEST A.SLoc AS B JOIN
[{“Custid”:“1248499”,“Rid”:“16CY”},{“Custid”:“1253899”,“Rid”:“16C4”},
{“Custid”:“1254438”,“Rid”:“16CE”},{“Custid”:“1257508”,“Rid”:“16C4”},
{“Custid”:“1260087”,“Rid”:“16C4”},{“Custid”:“1260131”,“Rid”:“13DX”},
{“Custid”:“1260316”,“Rid”:“16CE”},{“Custid”:“1264289”,“Rid”:“16LX”},
{“Custid”:“1265368”,“Rid”:“15IT”},{“Custid”:“1272178”,“Rid”:“16DY”}] AS
r ON A.Rid = r.Rid WHERE (A.$Type = “Route” AND B.LCdv !=“05”)

5

Per the docs that should work, assuming everything else is right with the query. Here’s an example from the Couchbase docs;

SELECT DISTINCT expression.destinationairport
FROM airport JOIN [
  {"destinationairport": "KEF", "sourceairport": "SFO", "type": "route"},
  {"destinationairport": "KEF", "sourceairport": "LHR", "type": "route"}
] AS expression
ON airport.faa = expression.sourceairport
WHERE airport.city = "San Francisco";

What version of Couchbase Server are you using? It’s possible that this is a newer query feature. The other possibility is there’s a problem in your unnest or predicates.

6

the version is 7.1.4 .
I removed the unnest but still no luck . What other options we have to fix this.

7

We you say it doesn’t work, can you explain that? Does it give an error? An empty dataset? A dataset but without the Custid?

8

empty data set . no error message. Empty data set with unnest and without unnest .

Does the mentioned CB version support this kind of query ?

9

In your first query, the resulting fields are “ID”, and “CustID”.

In your second query you are trying to join ON “A.RteId = r.RteID”; “RteID” isn’t defined in the results from the first query.

You could alias b.ID AS RteID in the first query.

For your later example, as plain SQL++:

cbq> WITH A AS ([{"$Type":"Route","SLoc":[{"LocNm":"locnm","LCdv":"00"}],"Rid":"16CY"}]) 
   2 SELECT A.Rid, B.LocNm, r.Custid
   3 FROM A UNNEST A.SLoc AS B
   4 JOIN
   5   [{"Custid":"1248499","Rid":"16CY"},{"Custid":"1253899","Rid":"16C4"},
   6   {"Custid":"1254438","Rid":"16CE"},{"Custid":"1257508","Rid":"16C4"},
   7   {"Custid":"1260087","Rid":"16C4"},{"Custid":"1260131","Rid":"13DX"},
   8   {"Custid":"1260316","Rid":"16CE"},{"Custid":"1264289","Rid":"16LX"},
   9   {"Custid":"1265368","Rid":"15IT"},{"Custid":"1272178","Rid":"16DY"}] AS r
  10 ON A.Rid = r.Rid
  11 WHERE A.`$Type` = "Route"
  12 AND B.LCdv !="05";
{
    "requestID": "4d5240b4-bd58-4489-a6f8-387fe0b75f9d",
    "signature": {
        "Rid": "json",
        "LocNm": "json",
        "Custid": "json"
    },
    "results": [
    {
        "Rid": "16CY",
        "LocNm": "locnm",
        "Custid": "1248499"
    }
    ],
    "status": "success",

works. Could there be a field name (case sensitive) or value issue in the filtering/joining ?

(Note I quoted $Type as I’ve assumed this the field name not a substitution.)

HTH.

1 Like
10

in my earlier query i removed few of the org specific keywords (fieldname)

You may refer the below one .

SELECT A.RteId, B.LocNm, r.GtmuCustId FROM Test A UNNEST A.SrvcngLoc AS B JOIN [{“GtmuCustId”:“1448499”,“RteId”:“16CY”},{“GtmuCustId”:“1453899”,“RteId”:“16C4”},{“GtmuCustId”:“1454438”,“RteId”:“16CE”},{“GtmuCustId”:“1457508”,“RteId”:“USF6C4”},{“GtmuCustId”:“1460087”,“RteId”:“16C4”},{“GtmuCustId”:“1460131”,“RteId”:“13DX”},{“GtmuCustId”:“1460316”,“RteId”:“USF6CE”},{“GtmuCustId”:“1464289”,“RteId”:“16LX”},{“GtmuCustId”:“1465368”,“RteId”:“15IT”},{“GtmuCustId”:“2003472178”,“RteId”:“16DY”}] AS r ON A.RteId = r.RteID WHERE (A.$Type = “Route” AND B.LocTypCdv !=“05”)

11

Your ON clause refers to “r.RteID” yet the data for ‘r’ has “RteId”; field names are case-sensitive.

1 Like
12

thank you very much .

13

You’re very welcome.

14

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