How to query a field inside array sub-document using ottoman query builder? How to add Collection name and scope name to query builder?

Node.js SDK
#1

How to query a field inside array sub-document using ottoman query builder? How to add Collection name and scope name to query builder?`

import { Schema, Query, getDefaultInstance } from "ottoman";

const  TestSchema = new Schema({
block1:{
    field1:{type: String, required: true},
    field2:{type: String, required: true}
},
block2:[{
    field3:{type: String, required: true},
    field4:{type: String, required: true}
}]
})

const ottoman = getDefaultInstance();
const query = new Query({}, 'bucketName.scopeName.collectionName');
const where_exp = {block2[{field3}]:{$eq: 'xyz'}};
const result = query
  .select()
  .where(where_exp)
  .build();
console.log(result);

`

#2

Hello @Vigneshwaran_Rengana to add the fully qualified namespace you are correct use the qualifier like you have mentioned, if your keyspace has special characters than escape the name using the ticks

for instance

const query = new Query({}, 'bucketName.scopeName.collectionName');

I have also opened up a Github Issue to track this further. - Query Builder Question · Issue #639 · couchbaselabs/node-ottoman · GitHub

#3

Can you please help for my first question also

#4

Sure @Vigneshwaran_Rengana

To query into array values use this way:

const where_exp = {
$any: {
$expr: [{ b: { $in: ‘block2’ } }],
$satisfies: { ‘b.field3’: {$eq: “xyz”} },
}
};
this will produce this valid N1Ql query
SELECT * FROM travel-sample.inventory.hotel WHERE ANY b IN block2 SATISFIES b.field3=“xyz” END

#5

Hello @Vigneshwaran_Rengana just following up to see if this helped ?

#6

Yeah, That worked.
but I want to use the index to query insted of querying using find.

`
const UserSchema = new Schema ({
name: {type: String, required: true},
emails: [{type: String, required: true}],
})

UserSchema.index.validateOneByEmail = {
by: ‘emails[*]’,
options: { limit:1 },
type: ‘n1ql’
}

UserSchema.statics.validateUser = async function (email:string, password: string) {

const filter = {
$any: {
$expr: [{$in: ‘emails’ }],
$satisfies: { email },
}
};

const result = await this.validateOneByEmail (filter);
}
`
Iam trying something like this but not getting any response, can you please helpon that.
Thanks
Vignesh

#7

Hello @Vigneshwaran_Rengana I am trying to get some help. You are basically setting up an index on an array field (email array) interesting.

#8 
CREATE INDEX ix1 ON  mybucket.myscope.mycollection ( DISTINCT ARRAY e FOR e IN emails END);

SELECT *
FROM  mybucket.myscope.mycollection AS c
WHERE ANY e IN emails SATISFIES e = "xyz@abc.com" END;

Query builder syntax some thing in this lines

const where_exp = {
$any: {
$expr: [{ e: { $in: emails } }],
$satisfies: { e: {$eq: “xyz@abc.com”} },
}
};
#9

:grinning: Thanks. I missed that e, I think there is need for updates in ottoman documentation, which covers all aspectes of couchbase capabilities. thanks for the quick help. this where exp works on my index validateOneByEmail.