Search for document by partial ID

SQL++
1

Hello. Everyone i didn’t found anything aboute my request so i’ve decided to leave a question here.
I’ve a bucket “settings” with 3 types of documents they id’s starts with prefixes “users…”, “places…” and “prices”. How can i find a latest documet with id “users_22.7.2020” of started with another prefixes.

Thanks.

2

Could you please elaborate more on this. On what bases latest? Do you have field in the document that stores timestamp? If there is date time in ISO860-1 (https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/datefun.html#date-formats) format inside document you can do MAX on that filed.

SELECT MAX([d.timestamp,d])[1].*
FROM default d WHERE ....

If you have document key of this format “users_2020-02-22” part of key is ISO-8601 format

You can do

SELECT META([META(d).id,d])[1].* 
FROM default AS d
WHERE META(d).id LIKE "users_%"

OR

   SELECT d.* 
    FROM default AS d
    WHERE META(d).id LIKE "users_%"
   ORDER BY META(d).id  DESC
  LIMIT 1;
3

Thank you i’ve got what i need. It works.

4

Hello again. This works:

SELECT MAX([d.timestamp,d])[1]
FROM Settings d
WHERE META().id LIKE “users_%”

but in couchbase web interface it says that:
“This query contains the following fields not found in the inferred schema for their bucket,
the field names might be misspelled:
Settings.timestamp”
So the question is the timestamp in document metadata or somewhere else?
It doesn’t appears in metadata, i can’t see it.

5

d.timestamp means timestamp must present in side document.There is no metadata

If you don’t have timestamp inside document it may not gives expected results.
On what field you want latest.
Post sample document , document key

6

I want the newest document from base. But right now i don’t have a field with timestamp. So i see i need to create it first, the couchbase doesn’t store the time document created.
By the way this code does’nt return any document content:

var Users_settings = SettingsBucket.GetDocument<dynamic>("SELECT MAX([d.timestamp,d])[1] FROM Settings d WHERE META().id LIKE ''users_%''");
Console.WriteLine(Users_settings.Content);

It returns empty string. But when i enter this query in web interface it returns document content.

7

you had 2 times single quote around users ‘users_%’

8

Thanks for quick reply i fixed that but it still doesn’t return anything.

9 
var Users_settings = 
SettingsBucket.GetDocument<dynamic>("SELECT MAX([d.timestamp,d])[1].* FROM Settings d WHERE META(d).id LIKE 'users_%'");

  var Users_settings = 
SettingsBucket.GetDocument<dynamic>("SELECT MAX([d.timestamp,META(d).id])[1] AS id FROM Settings d WHERE META(d).id LIKE 'users_%'");

Try above, if still have issue, that might be how GetDocument called. may be u need document id. cc @deniswsrosa

10

Both returns nothing.
I’m trying to get whole document into the variable, and than use it in form like this:

text_fieldx.Text = Found_document.Key

does it possible?

11

It possible , I am not expert on SDK some one has to answer that.

If your document key is of “users_22.7.2020” want latest based on latest date in document key you can also try some thing like this, (LET expression doing date in YYYY-MM-DD format, if you have hours/minute/seconds you need to expand or change document key format)

SELECT MAX([date, META(s).id])[1]
FROM Settings AS s
LET date = CONCAT(SPLIT(SUBSTR(META(s).id,6),".")[2],"-",CASE WHEN LENGTH(SPLIT(SUBSTR(META(s).id,6),".")[1]) = 1 THEN "0" ELSE "" END, SPLIT(SUBSTR(META(s).id,6),".")[1],"-",CASE WHEN LENGTH(SPLIT(SUBSTR(META(s).id,6),".")[0]) = 1 THEN "0" ELSE "" END, SPLIT(SUBSTR(META(s).id,6),".")[0])
WHERE META(s).id LIKE "users_%";