A view with compound key

#1

Hi,

I have a view that returns compound keys
From the console I manage to query by a compound key.
In .Net I’m having difficulties expressing the key.

The key is basically:

["0054466140146","2014","33"]

However, to write this in C# I write:

sKey = "[\"0054466140146\",\"2014\",\"33\"]";

This translate to

%22%5B%5C%220054466140146%5C%22%2C%5C%222014%5C%22%2C%5C%2233%5C%22%5D%22

in query.RawUri().OriginalString but not to

%5B%220054466140146%22%2C%222014%22%2C%2233%22%5D

as expected.

Is there a way to remove all the escape codes from the query ?

Thanks.

#2

If you are passing this to a query on a view, you need to pass object arrays. Here is an example.

  object[] compoundkey = { obj.objectType, obj.id };

In this case, obj.objectType is a string type, and obj.id is an integer. So you can mix and match types.

 var query = bucket.CreateQuery("history", "object-versions") .StartKey(compoundkey) .EndKey(compoundkey) .InclusiveEnd(true) .Reduce(true);
 var result = bucket.Query<dynamic>(query);
2 Likes
#3

It works !!

I read the docs many times and somehow I missed this non-intuitive solution. Can you please point me to where I can find it online ?

How can I return all the results where only the first param is specified: [“0054466140146”] and the others are don’t care ? I’ve tried setting null or not specifying at all but no docs returned.

BTW, why startkey() and endkey() and includsiveEnd() and not just Key() ?

#4

Believe it or not I found it in the previous version documentation:

1.2 .Net SDK Documentation on Query Interface

I use the startkey endkey and inclusiveend for the given situation. I think I found that by trial and error. I am to new couchbase too. I started with this tech maybe a month or two ago.

1 Like
#5

Very nice catch and thanks @envitraux
It also works with just .Key(object[])

I hope the docs will stay updated as there are many versions and at least as much different docs.

Do you also happen to know how can I return all the results where only the first param is specified: [“0054466140146”] and the others are don’t care ? I’ve tried setting null or not specifying at all but no docs returned.

Good luck for the two of us in this tech

#6

@itay, @envitraux -

Were in the process and reworking the documentation, note that a lot of the older documentation (concepts and server specific stuff) is still relevant. The client’s themselves are new mostly in syntax, but in some places behavior as well. You may need to reference an older set of documentation to figure something out on the new clients. This should improve over time, though.

This is our “new” forums (since Oct 2014) which we have added to encourage engagement with community, so far it seems to be working much better than our previous Q&A UI. Feedback is certainly welcome!

-Jeff

#7

hi @jmorris

I must say that this forum is very handy and friendly :stuck_out_tongue_closed_eyes:

On the other hand, I read the docs so many times and yet, I have a lot of difficulties, from all kinds, even though I’ve been programming for the last 30 years.

The docs sometimes miss consistency, lack examples and are not easy to navigate and yes, I understand the immense work that were put in building them.

CouchBase has its advantages but the learning curve is endless and very frustrating.

At least I have this forum for support so that after many hours of reading, trial and error, launching and destroying servers and playing with all sorts of configurations, I can at least post here and wait for my savior :innocent:

Itay

#8

Thanks for the kind words and honest criticism @itay. One update put on the docs just earlier this week is a link in the lower right to post feedback on the specific doc. We tried to make it as easy as possible so if you encounter a doc that’s missing something or incorrect, it shouldn’t be more than two clicks and typing a quick sentence.

#9

@ingenthr, I saw the button and will try to use it.

Can anyone relate to this:

#10

I solved this problem by specifying a Startkey and Endkey. The 2nd part of the start compound key would be null or a min value, the 2nd part of the end compound key would be a max value.

I have created a helper class with the following helper functions to deal with this when you have a date field in your compound key.

 public static object[] nullDateArray
    {
        get
        {
            return new object[] { null, null, null, null, null, null };
        }
    }
    public static object[] maxDateArray
    {
        get
        {
            return new object[] { DateTime.MaxValue.Year, DateTime.MaxValue.Month, DateTime.MaxValue.Day, DateTime.MaxValue.Hour, DateTime.MaxValue.Minute, DateTime.MaxValue.Second };
        }
    }
    public static object[] ToDateArray(DateTime date)
    {
        return new object[] { date.Year, date.Month, date.Day, date.Hour, date.Minute, date.Second };
    }
1 Like
#11

Very interesting approach. I’ll give it a try !

#12

@envitraux

You approach works for datetime but assuming that the second key type is string and I don’t care about its value, What should I specify for it ?

For example I have a view of user’s names: FirstName, FamilyName.
I want to find all the users with FirstName = “John”.

startkey = [“John”, null]
endkey = [“John”, ???]

Should endkey be “z”, “Z” ?
We know it cannot be null.
If I specify “Z” then “Zaa” will not be returned because it is after “Z”.

Itay

#13

Not sure, but depends on data and sort order. Maybe pass the equivalent of hex FFFFFFFF for the end key.

#14

Keep in mind that the ordering is based on unicode collation, 0xffffef is what had been suggested in the past, but arguably the most correct way is to look at the collation table and find something after the character you’re seeking.

#15

@ingenthr, @envitraux

This sounds more complicated than it should be :confused:
How can I specify 0xffffffffffff if the key is made out of string ?
What is the next letter after “z” ?

A better way would be just to enter a null value, or empty, or “” to specify don’t care and let the database do what is expected.

What should I do for strings ?

#16

Unicode collation is not strictly a value comparison, so the database does what’s expected in that it orders things according to unicode, but when specifying a query you’ll need to give the end of the range as appropriate for your app. It looks like either z(0xff5a) or Ꝣ (0xa762) depending on what you’re expecting according to the Latin Unicode Collation Table. Keep in mind, there’s also inclusive_end.

I know dealing with encoding and collation in unicode seems complicated compared to ASCII’s simple value comparisons, but it’s not too bad once you get how it works and it is the standard way to approach I18n.

#17

Complication leads to bugs.
“Don’t case” state should not involve values at all. It is just a matter of good practice.

Still,

  1. How do I correctly enter a unicode value to object[] in .net to build the endkey ?
  2. Can “null” be used in the start key as envitraux showed above ?
  3. Wouldn’t it be better for me to use unicode 0x0000 (or null) for start and unicode 0xFFFF for end regardless of possible values ?
  4. What if the value of the key part is an integer and not unicode ?

Thanks.

#18

I’m not sure I understand what you mean by the “Don’t case” state. There is a don’t state case that exists and is totally valid. When querying a view, you can use a start key without an end key and you can have your end key be the next ‘shape’ of data in your compound key.

To answer your questions…

  1. I’m not sure but I’m sure it’s possible. @jmorris how does one encode an empty array as an argument in a view query?
  2. Yes, null may be used as a start key. There’s an order documented for precedence.
  3. This is why I was pointing to Unicode collation tables. That is the most appropriate standard to use here, even though it’s not what you’re necessarily used to. They’re not in memcmp() byte value order. Unicode defines 0xffff as invalid. Note that unicode Latin collation is different than ASCII. Unicode is aAbBcCdD where ASCII is abcd...ABCD.
  4. An integer is definitely valid, as it’s a number and the documentation defines the order that will be used.

The dry standard is here. After reading a couple chapters and you’re bored, then look at the posts from the Fake Unicode Consortium on g+.

#19

That’s great !
How can I use this “Don’t care” state if the compound key has 3 parts and I want the first one to be “John”, the second, let’s say, family name, is a don’t care as I am looking for all the Johns, regardless of the family name, and the third part, the age, should be more than 20 (integer).

Can I use:
startkey= [“John”, null, 20]
endkey = [“John”, null, null] ?

the map emits [doc.FirstName, doc.FamilyName, doc.Age]

#20

My experience is that you can’t have a “do care” column that is right of a “don’t care column”. It seems the only solution is to have multiple views. I would love to hear what other solutions there might be.