Using N1QL with document keys

Question

I'm fairly new to couchbase and have tried to find the answer to a particular query I'm trying to create with not much success so far.

I've debated between using a view or N1QL for this particular case and settled with N1QL but haven't managed to get it to work so maybe a view is better after all.

Basically I have the document key (Group_1) for the following document:

Group_1
{
  "cbType": "group",
  "ID": 1,
  "Name": "Group Atlas 3",
  "StoreList": [
    2,
    4,
    6
  ]
}

I also have 'store' documents, their keys are listed in this document's storelist. (Store_2, Store_4, Store_6 and they have a storeID value that is 2, 4 and 6) I basically want to obtain all 3 documents listed.

What I do have that works is I obtain this document with its id by doing:

var result = CouchbaseManager.Bucket.Get<dynamic>(couchbaseKey);
mygroup = JsonConvert.DeserializeObject<Group> (result.ToString());

I can then loop through it's storelist and obtain all it's stores in the same manner, but i don't need anything else from the group, all i want are the stores and would have prefered to do this in a single operation.

Does anyone know how to do a N1QL directly unto a specified document value? Something like (and this is total imaginary non working code I'm just trying to clearly illustrate what I'm trying to get at):

SELECT * FROM mycouchbase WHERE documentkey IN Group_1.StoreList

Thanks

UPDATE: So Nic's solution does not work;

This is the closest I get to what I need atm:

SELECT b from DataBoard c USE KEYS ["Group_X"] UNNEST c.StoreList b;

"results":[{"b":2},{"b":4},{"b":6}]

Which returns the list of IDs of the Stores I want for any given group (Group_X) - I haven't found a way to get the full Stores instead of just the ID in the same statement yet.

Once I have, I'll post the full solution as well as all the speed bumps I've encountered in the process.

so after having trouble with log4net. I just ended up removing it entirely to be able to continue testing and allthough @Nic Raboy's answer looks exactly like what I'm trying to do, it returns an empty(null) object — Spacemonkey, Jul 09 '15 at 21:08

score 5 · Answer 1 · answered Jul 08 '15 at 17:33

I apologize if I have a misunderstanding of your question, but I'm going to give it my best shot. If I misunderstood, please let me know and we'll work from there.

Let's use the following scenario:

group_1

{
    "cbType": "group",
    "ID": 1,
    "Name": "Group Atlas 3",
    "StoreList": [
        2,
        4,
        6
    ]
}

store_2

{
    "cbType": "store",
    "ID": 2,
    "name": "some store name"
}

store_4

{
    "cbType": "store",
    "ID": 4,
    "name": "another store name"
}

store_6

{
    "cbType": "store",
    "ID": 6,
    "name": "last store name"
}

Now lets say you wan't to query the stores from a particular group (group_1), but include no other information about the group. You essentially want to use N1QL's UNNEST and JOIN operators.

This might leave you with a query like so:

SELECT 
    stores.name 
FROM `bucket-name-here` AS groups 
UNNEST groups.StoreList AS groupstore
JOIN `bucket-name-here` AS stores ON KEYS ("store_" || groupstore.ID) 
WHERE 
    META(groups).id = 'group_1';

A few assumptions are made in this. Both your documents exist in the same bucket and you only want to select from group_1. Of course you could use a LIKE and switch the group id to a percent wildcard.

Let me know if something doesn't make sense.

Best,

That actually looks a lot like what I'm trying to do. I didn't know about the 'ON KEYS' or the META keywords - I'm trying it out now — Spacemonkey, Jul 08 '15 at 17:41
Will do, I'm just having trouble with conflicting json dlls at the moment but once that's figured out I'll make sure to update on here as to whether it works or what does. — Spacemonkey, Jul 08 '15 at 20:48
The ON KEYS that concatenates the UNNEST doesn't work, I have to admit I'm not sure why it would anyway, there are no .IDs in the UNNEST result ... just an array of numbers, looks like: [{"2"},{"4"},{"6"}] — Spacemonkey, Jul 14 '15 at 20:33

score 0 · Answer 2 · edited Aug 09 '15 at 08:42

0

Try this query:

select Name 
from buketname a join bucketname b ON KEYS a.StoreList 
where Name="Group Atlas 3"

edited Aug 09 '15 at 08:42

Calcolat

878
2
11
22

answered Aug 09 '15 at 07:29

arihant rk

101
5

score 0 · Answer 3 · answered Aug 09 '15 at 15:27

0

Based on your update, you can do the following:

SELECT b, s
FROM DataBoard c USE KEYS ["Group_X"]
UNNEST c.StoreList b
JOIN store_bucket s ON KEYS "Store_" || TO_STRING(b);

answered Aug 09 '15 at 15:27

geraldss

2,415
1
11
12

score 0 · Answer 4 · answered Mar 14 '16 at 20:39

0

I have a similar requirement and I got what I needed with a query like this:

SELECT store
FROM `bucket-name-here` group
JOIN `bucket-name-here` store ON KEYS group.StoreList
WHERE group.cbType = 'group'
AND group.ID = 1

answered Mar 14 '16 at 20:39

mmastrangelo

21
5

Using N1QL with document keys

4 Answers4

Linked