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.

Answer 1

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

Answer 2

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,

Answer 3

Try this query:

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

Answer 4

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);