Query inside field arrays

[gustavolzangelo]

Hi! I'm having some trouble trying to query inside field arrays. For example, I have a schema:

const randomSchema= new Schema( { ... dates: {type: [Date]}, ... }

And I'm trying to do some queries on these dates:

• Given a specific date, return the object which contains a date gte/lte on the dates array
• Given two specific dates, return the object which contains dates in between those dates
• To sort for the last date from the dates array. Something like {sort:{"dates[lastIndex": "ASC"}}

[rhadaway14]

can you give me an example of an object using your schema? { ..., ["2023-05-26T17:38:31.075Z", "2023-05-27T17:38:31.075Z", "2023-05-28T17:38:31.075Z"], .... }

or

{ ..., [{"startDate":'2023-05-20T17:38:31.075Z', "endDate": "2023-05-21T17:38:31.075Z"}, {"startDate": "2023-05-28T17:38:31.075Z", "endDate": "2023-05-29T17:38:31.075Z"], .... }

[gustavolzangelo]

It would be ["2023-05-26T17:38:31.075Z", "2023-05-27T17:38:31.075Z", "2023-05-28T17:38:31.075Z"]

The schema is: dates: { type: [Date] },

Example:

{ "data": { "users": { "data": [ { "dates": [ "2023-05-26T17:38:31.075Z", "2023-05-27T17:38:31.075Z" ] }, { "dates": [ "2023-05-26T17:38:31.075Z", "2023-05-27T17:38:31.075Z" ] } ] } } }

On Mon, Jun 26, 2023 at 5:23 PM Robert Hadaway @.***> wrote:

can you give me an example of an object using your schema? { ..., ["2023-05-26T17:38:31.075Z", "2023-05-27T17:38:31.075Z", "2023-05-28T17:38:31.075Z"], .... }

or

{ ..., [{"startDate":'2023-05-20T17:38:31.075Z', "endDate": "2023-05-21T17:38:31.075Z"}, {"startDate": "2023-05-28T17:38:31.075Z", "endDate": "2023-05-29T17:38:31.075Z"], .... }

— Reply to this email directly, view it on GitHub https://github.com/couchbaselabs/node-ottoman/issues/728#issuecomment-1608192587, or unsubscribe https://github.com/notifications/unsubscribe-auth/AESR5MOEIFKQHZ7VMYU6YGTXNHVVBANCNFSM6AAAAAAZSOMAKU . You are receiving this because you authored the thread.Message ID: @.***>

[rhadaway14]

// package.json { "name": "oncue", "version": "1.0.0", "description": "", "main": "index.js", "scripts": { "test": "echo \"Error: no test specified\" && exit 1" }, "author": "", "license": "ISC", "dependencies": { "@faker-js/faker": "^8.0.1", "couchbase": "^4.2.3", "dotenv": "^16.0.3", "email-validator": "^2.0.4", "nanoid": "^4.0.2", "node": "^20.3.1", "ottoman": "^2.3.3" } }

[rhadaway14]

//index.json

const { Ottoman, Schema, set, addValidators, Query } = require('ottoman'); const { config } = require('dotenv'); const { faker } = require('@faker-js/faker'); const { validate } = require('email-validator'); const { nanoid } = import('nanoid'); // changed this from require

const main = async () => { const { nanoid } = await import('nanoid'); console.log('alright') const connectionObj = { connectionString: 'YOUR CONNECTION STRING', bucketName: 'sample', username: 'Administrator', password: 'couchbase' };

const ottoman = new Ottoman({ collectionName: 'sampleCollection', scopeName: 'sampleScope', timeout: 10000, ottoman: { idKey: 'id' } });

const connection = await ottoman.connect(connectionObj); console.log('connected to couchbase') set('DEBUG', true);

try{ console.log('starting ottoman') await ottoman.start(); console.log('ottoman started') } catch (err) { console.error(err); };

const email = (val) => { console.log(typeof val) console.log(val) if ( !val && !validate(val)) { // email is not correct throw new Error('Value is not a valid email address.'); } return true; };

addValidators({ email }); console.log("validator added")

const UserSchema = new Schema({ firstName: { type: String, required: true }, lastName: { type: String, required: true }, email: { type: String, required: true, validator: 'email' }, altEmail: { type: String, validator: 'email' } // 'undefined' }); console.log("schema created")

const User = ottoman.model('User', UserSchema); console.log("model created")

const dataArraySchema = new Schema({ name: { type: String, required: true }, dates: [{ type: Date }] }) const DateArray = ottoman.model('DateArray', dataArraySchema);

const dateTest= new DateArray({ name: faker.person.firstName(), dates: ["2023-05-26T17:38:31.075Z", Date.now()] })

// await dateTest.save(); // console.log(dateTest)

const letExpr = { inputDate: "1677199113" }; let date1 = {$field: 'shipDate'}

////////////////////////////////////////////////////////// // const equals_where = { // $any: { // $expr: [ // {'date': {$in: 'shipDate'}}, // ], // $satisfies: { // 'inputDate': {$gte: {$field: 'date'}} // } // } // } ////////////////////////////////////////////////////////////////

// const equals_where = {

// 'inputDate': {$gte: {$field: 'shipDate'}, {$field: 'shipDate[-1]'}]}} /////////////////////////////////////////////////////////////////////// // const equals_where = { // 'customerId': {$eq: {$field: 'inputCust'}} // };

// const equals_where = { // 'inputDate': {$in: {$field: 'shipDate'}} // // country: { $in: ['United Kingdom', 'France'] } // };

const equals_where = { $any: { $expr: [ {'date': {$in: 'shipDate'}}, ], $satisfies: { // 'inputDate': {$gte: {$field: 'date'}} 'inputDate': {$gte: {$field: 'date'}} } }, $and: [ {$any: { $expr: [ {'date': {$in: 'shipDate'}}, ], $satisfies: { 'inputDate': {$lte: {$field: 'date'}}} } } ] }

console.log(equals_where)

// '2023-05-26T17:38:31.075Z' : { $in: DateArray.dates } const query = new Query({select: '*' }, 'sample.sampleScope.sampleCollection');

const result = query // .select() .let(letExpr) .where(equals_where) .build()

console.log(result)

const res = await ottoman.query(result) console.log(res.rows)

///////////////////////////////////////////////

//create user with the and the email(correct format) and altEmail(correct format) const userWithAltEmail = new User({ firstName: faker.person.firstName(), lastName: faker.person.lastName(), email: faker.internet.email(), altEmail: faker.internet.email() });

// console.log(userWithAltEmail)

//create user with the and the email(correct format) and no altEmail const userWithOutAltEmail = new User({ firstName: faker.person.firstName(), lastName: faker.person.lastName(), email: faker.internet.email() });

/////////////////////////////save users////////////////////////////////////////////////

// console.log(userWithOutAltEmail)

// try { // console.log('Saving user with alt-email'); // await userWithAltEmail.save(); // console.log('User with alt-email saved successfully:', userWithAltEmail.firstName); // } catch (err) { // console.error(err) // console.error('Error saving user with alt-email'); // }

// try { // console.log('Saving user without alt-email'); // await userWithOutAltEmail.save(); // console.log('User without alt-email saved successfully:', userWithOutAltEmail.firstName); // } catch (err) { // console.error(err) // console.error('Error saving user without alt-email'); // } };

main() console.log('starting index.js');

[rhadaway14]

//order:00111

{ "customerId": "customer:123456", "shipFrom": { "address": "1665 N Kind St", "city": "Saratoga Springs", "st": "UT", "zipCode": "84045" }, "shipTo": { "address": "16 N Peach St", "city": "Atlanta", "st": "GA", "zipCode": "59115" }, "shipDate": [ 1677112713, 1677199113, 1677199114 ], "arrivalDate": [ 1677112715, 1677199116, 1677199117 ], "truckId": "C25", "driverId": "1533", "price": 500.66, "orderDate": 1679355513, "status": "complete" }

[rhadaway14]

//customer:123456

{ "fName": "Rob", "lName": "Hadaway", "phone": "801-441-8548", "email": "rob.hadaway@couchbase.com", "address": "8500 S 1684 W", "city": "Saratoga Springs", "st": "UT", "zipCode": "84045", "business": false, "created": 1656545913 }

[gsi-alejandro]

hi @gustavolzangelo

try this:

const filter = {
      $any: {
        $expr: [{ search: { $in: 'dates' } }],
        $satisfies: { search: { $gt: '<givenDay1>', $lt: '<givenDay2>' } },
      },
    };

Notice: you can combine both $gt and $lt to solve point 2 (dates between) or just 1 of them to build the query for point 1

Point 3: "To sort for the last date from the dates array. Something like {sort:{"dates[lastIndex": "ASC"}}"

const results = await Model.find(filter, { sort: { 'numbers[-1]': 'DESC' });

[-1] -> will point to the last index in the array

[ejscribner]

Released in v2.3.4