Join using part of the key

SQL++
1

Hi,

I have two buckets that needs to be joined. For one of them document keys look like part1/part2/part3 and the second bucket records have field matching to the part3. How can I run joins using part of the document key?

select * from bucket2
join bucket1 on keys bucket1.part3

How should I modify my query to succeed ? Is it possible to do so?

Thanks

2

Can you post 4 things: document1, document2, the key of document1, the key of document2.

3

document 1
key
MTPC/2016-11-18/0445SDAW78122
data
{
“name”: “aaaa”,
“seller-id”: “3”,
“total”: “160.3300”
…
}

document 2
key
23245
data
{
“transaction-id”: “0445SDAW78122”
…
}

So I want to join using document2.transaction-id which is the part of the key in the document1.

Thanks

4

Here you go.

SELECT *
FROM bucket2 USE KEYS "23245"
JOIN bucket1 ON KEYS "MTPC/2016-11-18/" || bucket2.`transaction-id`
;
5

The problem with this solution the my prefix can change. It can be “MTPC/2016-11-18/…” or “ABCD/2014-11-12/”…

6

That part can be an expression. It does not have to be a constant.

7

The problem is that I don’t know how to put a expression. Because prefix can have any value following ‘/’ separated pattern. maybe there is a way to put a regex instead of
JOIN bucket1 ON KEYS “MTPC/2016-11-18/” || bucket2.transaction-id
?

8

You can use any expression.

JOIN bucket1 ON KEYS _some_expression_

some_expression can be any N1QL expression. See the documentation on all functions and expressions.

10

Thanks.
I followed docs
http://developer.couchbase.com/documentation/server/4.5/analytics/2_expr.html

As I understand I can use LIKE operator expression to make a join with key? I just don’t know how to put that like into the right place to make it work.

The following doesn’t work
on KEYS like ‘%’ || (TO_STRING(av.mediabuy-id))

Any idea?

11

I also want to try to use STRING functions like POSITION and SUBSTR to get the part of the string that I want. But I don’t know how to apply it to the document key not the field.

12

Use the function META( mybucket ).id to access the document key.

13

Thanks, but it is still not clear how should I use it in join.
ON KEYS statement uses a field not key. I would appreciate any simple example.

14

ON KEYS uses any expression. Please use the N1QL language reference, not any other documentation:

http://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/index.html

Give me some specifics, and I can give you another example.

15

document1

MTPC/2016-11-18/0445SDAW78122
{
“name”: “aaaa”,
“seller-id”: “3”,
“total”: “160.3300”
}
MTPC2/2016-11-19/034DXF45F3456
{
“name”: “bbb”,
“seller-id”: “3”,
“total”: “145.33”
}
MTPK2/2016-11-19/022PDT566D345
{
“name”: “ccc”,
“seller-id”: “7”,
“total”: “66.66”
}
MTPK2/2016-11-19/122989FGD3424
{
“name”: “ddd”,
“seller-id”: “8”,
“total”: “120.1”
}

document 2

23245
{
“transaction-id”: “0445SDAW78122”
…
}
42554
{
“transaction-id”: “034DXF45F3456”
…
}
12252
{
“transaction-id”: “122989FGD3424”
…
}

Where keys are marked as bold. We are interested only in document1’s keys. document2.transaction-id field matches part of the key of the first document. Though I understand that I can apply an expression but in the JOIN statement we use document2.transaction_id and I don’t know how to apply expression on document1’s key itself.

I would appreciate an example.

Thanks for your time.

16

Is the document1 key prefix a wildcard? That is, given a document2, there is no way to know the prefix of the document1 key?

17

That’s right. The prefix is unknown.

18

Then it is not possible to perform the JOIN in this direction, from document2 to document1.

You need to use 4.5.1 EE, and then join from document1 to document2, using an index JOIN.

1 Like
19

We made a change in our bucket structure to make it work. Thanks :slight_smile:

1 Like