Cloud database operator

Query Logical operator

and

Query operator, used to express a logical "AND" relationship, indicating that multiple query filter conditions need to be satisfied at the same time

Instructions for use

and has two use cases:

1. Used in the root query condition

At this time, multiple query conditions need to be passed in, which means that multiple complete query conditions need to be satisfied at the same time.

const dbCmd = db.command
let res = await db.collection('todo').where(dbCmd.and([
  {
    progress: dbCmd.gt(50)
  },
  {
    tags: 'cloud'
  }
])).get()

However, the above query conditions composed of and are unnecessary, because the fields of the incoming object implicitly form an "and" relationship. The above conditions are equivalent to the following more concise writing:

const dbCmd = db.command
let res = await db.collection('todo').where({
  progress: dbCmd.gt(50),
  tags: 'cloud'
}).get()

It is usually necessary to explicitly use and when there are cross fields or operations

2. Used in field query conditions

Multiple query operators or constants need to be passed in, indicating that the field must meet or match the given conditions.

As shown in the following preamble, "progress field value is greater than 50 and less than 100"

const dbCmd = db.command
let res = await db.collection('todo').where({
  progress: dbCmd.and(dbCmd.gt(50), dbCmd.lt(100))
}).get()

The same condition can also be expressed in postfix:

const dbCmd = db.command
let res = await db.collection('todo').where({
  progress: dbCmd.gt(50).and(dbCmd.lt(100))
}).get()

Note that Command can also directly call other Command in a chain by default, which by default means the AND operation of multiple Command, so the above code can also be simplified as:

const dbCmd = db.command
let res = await db.collection('todo').where({
  progress: dbCmd.gt(50).lt(100)
}).get()

call style

The method accepts two ways of passing parameters, one is to pass in an array parameter, the other is to pass in multiple parameters, the effect is the same.

// pass in the array
function and(expressions: Expression[]): Command
// pass in multiple parameters
function and(...expressions: Expression[]): Command

or

The query operator is used to express a logical "or" relationship, indicating that multiple query filter conditions need to be satisfied at the same time. The OR instruction has two usages. One is to perform the "OR" operation of field values, and the other is to perform the "OR" operation across fields.

OR operation of field values

The "or" operation of field values refers to specifying a field value as one of multiple values.

For example, to filter out todos with progress greater than 80 or less than 20:

Streaming writing:

const dbCmd = db.command
let res = await db.collection('todo').where({
  progress: dbCmd.gt(80).or(dbCmd.lt(20))
}).get()

Prefix:

const dbCmd = db.command
let res = await db.collection('todo').where({
  progress: dbCmd.or(dbCmd.gt(80), dbCmd.lt(20))
}).get()

Prefix also accepts an array:

const dbCmd = db.command
let res = await db.collection('todo').where({
  progress: dbCmd.or([dbCmd.gt(80), dbCmd.lt(20)])
}).get()

OR operation across fields

The "or" operation across fields refers to the condition "or", which is equivalent to passing in multiple where statements, and only one of them can be satisfied.

To filter out todos with progress greater than 80 or marked as completed:

const dbCmd = db.command
let res = await db.collection('todo').where(dbCmd.or([
  {
    progress: dbCmd.gt(80)
  },
  {
    done: true
  }
])).get()

call style

The method accepts two ways of passing parameters, one is to pass in an array parameter, the other is to pass in multiple parameters, the effect is the same.

// pass in the array
function or(expressions: Expression[]): Command
// pass in multiple parameters
function or(...expressions: Expression[]): Command

not

The query operator is used to express a logical "not" relationship, which means that the specified conditions need not be met.

Example

For example, to filter out todos whose progress is not equal to 100:

const dbCmd = db.command
let res = await db.collection('todo').where({
  progress: dbCmd.not(dbCmd.eq(100))
}).get()

not can also be used with other logical instructions, including and, or, nor, not, such as or:

const dbCmd = db.command
let res = await db.collection('todo').where({
  progress: dbCmd.not(dbCmd.or([dbCmd.lt(50), dbCmd.eq(100)]))
}).get()

nor

The query operator is used to express a logical "Neither" relationship, indicating that all the specified conditions need not be met. If there is no corresponding field in the record, the condition is satisfied by default.

Example 1

Filter out todos whose progress is neither less than 20 nor greater than 80:

const dbCmd = db.command
let res = await db.collection('todo').where({
  progress: dbCmd.nor([dbCmd.lt(20), dbCmd.gt(80)])
}).get()

The above will also filter out the records without the progress field. If you want to require the progress field to exist, you can use the exists command:

const dbCmd = db.command
let res = await db.collection('todo').where({
  progress: dbCmd.exists().nor([dbCmd.lt(20), dbCmd.gt(80)])
  // progress: dbCmd.exists().and(dbCmd.nor([dbCmd.lt(20), dbCmd.gt(80)]))
}).get()

Example 2

Filter out records whose progress is not less than 20 and whose tags array does not contain the miniprogram string:

const dbCmd = db.command
db.collection('todo').where(dbCmd.nor([{
  progress: dbCmd.lt(20),
}, {
  tags: 'miniprogram',
}])).get()

The above will filter records that meet one of the following conditions:

1. progress is not less than 20 and the tags array does not contain the miniprogram string 3. progress is not less than 20 and the tags field does not exist 5. The progress field does not exist and the tags array does not contain miniprogram string 7. progress is not less than 20 and the tags field does not exist If the progress and tags fields are required to exist, the exists directive can be used:

const dbCmd = db.command
let res = await db.collection('todo').where(
  dbCmd.nor([{
    progress: dbCmd.lt(20),
  }, {
    tags: 'miniprogram',
  }])
  .and({
    progress: dbCmd.exists(true),
    tags: dbCmd.exists(true),
  })
).get()

call style

The method accepts two ways of passing parameters, one is to pass in an array parameter, the other is to pass in multiple parameters, the effect is the same.

// pass in the array
function nor(expressions: Expression[]): Command
// pass in multiple parameters
function nor(...expressions: Expression[]): Command

Query and comparison operator

eq

Query filter conditions, indicating that the field is equal to a value. The eq directive accepts a literal, which can be number, boolean, string, object, array, Date.

Instructions for use

For example, to filter out all the articles published by oneself, in addition to the method of passing objects:

const openID = 'xxx'
let res = await db.collection('articles').where({
  _openid: openID
}).get()

You can also use the command:

const dbCmd = db.command
const openID = 'xxx'
let res = await db.collection('articles').where({
  _openid: dbCmd.eq(openid)
}).get()

Note that the eq directive has more flexibility than the object method and can be used to indicate that a field is equal to an object, such as:

// This notation means match stat.publishYear == 2018 and stat.language == 'zh-CN'
let res = await db.collection('articles').where({
  stat: {
    publishYear: 2018,
    language: 'zh-CN'
  }
}).get()
// This way of writing means that the stat object is equal to { publishYear: 2018, language: 'zh-CN' }
const dbCmd = db.command
let res = await db.collection('articles').where({
  stat: dbCmd.eq({
    publishYear: 2018,
    language: 'zh-CN'
  })
}).get()

neq

Query filter conditions, indicating that the field is not equal to a value. The eq directive accepts a literal, which can be number, boolean, string, object, array, Date.

Instructions for use

Indicates that the field is not equal to a value, as opposed to eq

lt

Query filter operator, indicating that it needs to be less than the specified value. A Date object can be passed in for date comparison.

Sample code

Find todos with progress less than 50

const dbCmd = db.command
let res = await db.collection('todos').where({
  progress: dbCmd.lt(50)
})
.get()

lte

Query filter operator, indicating that it must be less than or equal to the specified value. A Date object can be passed in for date comparison.

Sample code

Find todos with progress less than or equal to 50

const dbCmd = db.command
let res = await db.collection('todos').where({
  progress: dbCmd.lte(50)
})
.get()

gt

Query filter operator, indicating that it must be greater than the specified value. A Date object can be passed in for date comparison.

Sample code

Find todos with progress greater than 50

const dbCmd = db.command
let res = await db.collection('todos').where({
  progress: dbCmd.gt(50)
})
.get()

gte

Query filter operator, indicating that it must be greater than or equal to the specified value. A Date object can be passed in for date comparison.

Sample code

Find todos with progress greater than or equal to 50

const dbCmd = db.command
let res = await db.collection('todos').where({
  progress: dbCmd.gte(50)
})
.get()

in

Query filter operator, indicating that the value is required to be in the given array.

Sample code

Find todos with progress of 0 or 100

const dbCmd = db.command
let res = await db.collection('todos').where({
  progress: dbCmd.in([0, 100])
})
.get()

nin

Query filter operator, indicating that the required value is not in the given array.

Sample code

Find todos whose progress is not 0 or 100

const dbCmd = db.command
let res = await db.collection('todos').where({
  progress: dbCmd.nin([0, 100])
})
.get()

Query field operator

exists

Check if field exists

Sample code

Find records where tags field exists

const dbCmd = db.command
let res = await db.collection('todos').where({
  tags: dbCmd.exists(true)
})
.get()

mod

Query filter operator, given the divisor divisor and remainder remainder, when the field is required as the dividend value % divisor = remainder.

Sample code

Find records for fields whose progress is a multiple of 10

const dbCmd = db.command
let res = await db.collection('todos').where({
  progress: dbCmd.mod(10, 0)
})
.get()

Query array operator

all

Array query operator. A query filter for array fields that requires all elements of a given array to be contained in the array field.

Sample Code 1: Normal Array

Find records whose tags array field contains both cloud and database

const dbCmd = db.command
let res = await db.collection('todos').where({
  tags: dbCmd.all(['cloud', 'database'])
})
.get()

Sample Code 2: Array of Objects

If the array element is an object, you can use dbCmd.elemMatch to match part of the field of the object

Suppose there is a field places defined as follows:

{
  "type": string
  "area": number
  "age": number
}

Find out that the array field contains at least one element satisfying "area greater than 100 and age less than 2" and one element satisfying "type is mall and age greater than 5"

const dbCmd = db.command
let res = await db.collection('todos').where({
  places: dbCmd.all([
    dbCmd.elemMatch({
      area: dbCmd.gt(100),
      age: dbCmd.lt(2),
    }),
    dbCmd.elemMatch({
      type: 'mall',
      age: dbCmd.gt(5),
    }),
  ]),
})
.get()

elemMatch

Query filter conditions for array fields that require at least one element in the array that satisfies all conditions given by elemMatch

Example code: the case where the array is an array of objects

Suppose the collection example data is as follows:

{
  "_id": "a0",
  "city": "x0",
  "places": [{
    "type": "garden",
    "area": 300,
    "age": 1
  }, {
    "type": "theatre",
    "area": 50,
    "age": 15
  }]
}

Find at least one element in the places array field that satisfies "area greater than 100 and age less than 2"

const dbCmd = db.command
let res = await db.collection('todos').where({
  places: dbCmd.elemMatch({
    area: dbCmd.gt(100),
    age: dbCmd.lt(2),
  })
})
.get()

Note*: If you do not use elemMatch and specify the condition directly as follows, it means that the area field of at least one element in the places array field is greater than 100 and the places array field has at least one element. The age field is less than 2:

const dbCmd = db.command
let res = await db.collection('todos').where({
  places: {
    area: dbCmd.gt(100),
    age: dbCmd.lt(2),
  }
})
.get()

Sample code: the case where the array elements are all common data types

Suppose the collection example data is as follows:

{
  "_id": "a0",
  "scores": [60, 80, 90]
}

Find at least one element in the scores array field that satisfies "greater than 80 and less than 100" at the same time

const dbCmd = db.command
let res = await db.collection('todos').where({
  scores: dbCmd.elemMatch(dbCmd.gt(80).lt(100))
})
.get()

size

The update operator is used for the query filter condition of the array field, and the length of the array is required to be a given value

Example

Find all records with tags array field length 2

const dbCmd = db.command
let res = await db.collection('todos').where({
  places: dbCmd.size(2)
})
.get()

Query Geolocation operator

geoNear

Find records with field values near a given point in order from nearest to farthest.

Indexing Requirements

Geographical index needs to be established on the query field

Sample code

Find records within 1km to 5km of a given location

const dbCmd = db.command
let res = await db.collection('restaurants').where({
  location: dbCmd.geoNear({
    geometry: new db.Geo.Point(113.323809, 23.097732),
    minDistance: 1000,
    maxDistance: 5000,
  })
}).get()

geoWithin

Finds records whose field value is in the specified range, without sorting. The specified area must be a polygon (Polygon) or a collection of polygons (MultiPolygon).

Indexing Requirements

Geographical index needs to be established on the query field

Example Code 1: Given Polygon

const dbCmd = db.command
const { Point, LineString, Polygon } = db.Geo
let res = await .collection('restaurants').where({
  location: dbCmd.geoWithin({
    geometry: new Polygon([
      new LineString([
        new Point(0, 0),
        new Point(3, 2),
        new Point(2, 3),
        new Point(0, 0)
      ])
    ]),
  })
}).get()

Example code 2: Given a circle

A circle can be constructed with centerSphere instead of geometry.

The definition of the value corresponding to centerSphere is: [ [longitude, latitude], radius]

The radius needs to be measured in radians. For example, if a radius of 10km is required, divide the distance by the earth's radius of 6378.1km.

const dbCmd = db.command
let res = await db.collection('restaurants').where({
  location: dbCmd.geoWithin({
    centerSphere: [
      [-88, 30],
      10 / 6378.1,
    ]
  })
}).get()

geoIntersects

Find the records that intersect the graph for a given geographic location

Indexing Requirements

Geographical index needs to be established on the query field

Sample code: Find records that intersect a polygon

const dbCmd = db.command
const { Point, LineString, Polygon } = db.Geo
let res = await db.collection('restaurants').where({
  location: dbCmd.geoIntersects({
    geometry: new Polygon([
      new LineString([
        new Point(0, 0),
        new Point(3, 2),
        new Point(2, 3),
        new Point(0, 0)
      ])
    ]),
  })
}).get()

Query expression operator

expr

The query operator is used to use aggregate expressions in query statements. The method receives a parameter, which must be an aggregate expression.

Instructions for use

1. expr can be used to introduce aggregate expressions in an aggregatematch pipeline stage 3. If an aggregate[match](cf-database-aggregate#aggregate- match) phase is in the lookup phase, the variables defined by the let parameter in lookup can be used in the expr expression at this time. The specific example See specify multiple join conditions in lookup Example 5. expr can be used to introduce aggregate expressions in ordinary query statements (where)

Example code 1: Compare two fields in the same record

Suppose the data structure of the items collection is as follows:

{
  "_id": string,
  "inStock": number, // 库存量
  "ordered": number  // 被订量
}

Find records where the quantity ordered is greater than the quantity in stock:

const dbCmd = db.command
const $ = dbCmd.aggregate
let res = await db.collection('items').where(dbCmd.expr($.gt(['$ordered', '$inStock']))).get()

Sample Code 2: Combining with Conditional Statements

Suppose the data structure of the items collection is as follows:

{
  "_id": string,
  "price": number
}

Assuming that the price is less than or equal to 10, 20% off, and the price is greater than 10, 50% off, let the database query return the records whose price is less than or equal to 8 after the discount:

const dbCmd = db.command
const $ = dbCmd.aggregate
let res = await db.collection('items').where(dbCmd.expr(
  $.lt([
    $.cond({
      if: $.gte(['$price', 10]),
      then: $.multiply(['$price', '0.5']),
      else: $.multiply(['$price', '0.8']),
    })
    ,
    8
  ])
).get()

Update field operator

set

Update operator, used to set the field equal to the specified value.

Instructions for use

The advantage of this method over passing in a pure JS object is the ability to specify that the field is equal to an object

Example

// The following method will only update style.color to red, not style to { color: 'red' }, that is, it will not affect other fields in style
let res = await db.collection('todos').doc('doc-id').update({
  style: {
    color: 'red'
  }
})

// The following method updates the style to { color: 'red', size: 'large' }
let res = await db.collection('todos').doc('doc-id').update({
  style: dbCmd.set({
    color: 'red',
    size: 'large'
  })
})

remove

The update operator is used to indicate the deletion of a field.

Sample code

Remove the style field:

const dbCmd = db.command
let res = await db.collection('todos').doc('todo-id').update({
  style: dbCmd.remove()
})

inc

Update operator, atomic operation, used to indicate field auto-increment

Atomic increment

When multiple users write at the same time, the fields are automatically incremented for the database, and there will be no situation where the latter will overwrite the former.

Sample code

Increment the progress of a todo by 10:

const dbCmd = db.command
let res = await db.collection('todos').doc('todo-id').update({
  progress: dbCmd.inc(10)
})

mul

Update operator, atomic operation, used to instruct a field to multiply by a value

Atomic Multiplication

When multiple users write at the same time, for the database, the fields are multiplied by themselves, and there will be no situation where the latter will overwrite the former.

Sample code

Multiply the progress of a todo by 10:

const dbCmd = db.command
let res = await db.collection('todos').doc('todo-id').update({
  progress: dbCmd.mul(10)
})

min

Update operator, given a value, update only if the value is less than the current value of the field.

Sample code

If field progress > 50, update to 50

const dbCmd = db.command
let res = await db.collection('todos').doc('doc-id').update({
  progress: dbCmd.min(50)
})

max

The update operator, given a value, updates only if the value is greater than the current value of the field.

Sample code

If field progress < 50, update to 50

const dbCmd = db.command
let res = await db.collection('todos').doc('doc-id').update({
  progress: dbCmd.max(50)
})

rename

Update operator, field rename. If you need to rename deeply nested fields, you need to use dot path notation. Fields of objects nested in arrays cannot be renamed.

Example 1: Renaming top-level fields

const dbCmd = db.command
let res = await db.collection('todos').doc('doc-id').update({
  progress: dbCmd.rename('totalProgress')
})

Example 2: Renaming nested fields

const dbCmd = db.command
let res = await db.collection('todos').doc('doc-id').update({
  someObject: {
    someField: dbCmd.rename('someObject.renamedField')
  }
})

or:

const dbCmd = db.command
let res = await db.collection('todos').doc('doc-id').update({
  'someObject.someField': dbCmd.rename('someObject.renamedField')
})

Update array operator

push

Array update operator. For a field whose value is an array, add one or more values to the array. or the field was originally empty, create the field and set the array as the incoming value.

Parameter Description

position description

The each parameter must also be present.

A non-negative number represents the position counted from the beginning of the array, starting at 0. If the value is greater than or equal to the length of the array, it is considered to be added at the end. Negative numbers represent the position counted from the end of the array, for example -1 means the position of the second-to-last element. If the absolute value of a negative value is greater than or equal to the length of the array, it is considered to be added from the head of the array.

sort instructions

The each parameter must also be present. Give 1 for ascending order and -1 for descending order.

If the array element is a record, the format of { <field>: 1 | -1 } is used to indicate the ascending and descending order according to what field in the record.

slice Description**

Requires that the each parameter also exists

value	description
0	Update field to empty array
Positive numbers	Array retains only first n elements
Negative	Array retains only last n elements

Example 1: Adding elements to the end

const dbCmd = db.command
let res = await db.collection('todos').doc('doc-id').update({
  tags: dbCmd.push(['mini-program', 'cloud'])
})

Example 2: Insert from the second position

const dbCmd = db.command
let res = await db.collection('todos').doc('doc-id').update({
  tags: dbCmd.push({
    each: ['mini-program', 'cloud'],
    position: 1,
  })
})

Example 3: Sorting

Sort the entire array after insertion

const dbCmd = db.command
let res = await db.collection('todos').doc('doc-id').update({
  tags: dbCmd.push({
    each: ['mini-program', 'cloud'],
    sort: 1,
  })
})

Do not insert, just sort the array

const dbCmd = db.command
let res = await db.collection('todos').doc('doc-id').update({
  tags: dbCmd.push({
    each: [],
    sort: 1,
  })
})

If the field is an array of objects, it can be sorted according to the fields in the element object as follows:

const dbCmd = db.command
let res = await db.collection('todos').doc('doc-id').update({
  tags: dbCmd.push({
    each: [
      { name: 'miniprogram', weight: 8 },
      { name: 'cloud', weight: 6 },
    ],
    sort: {
      weight: 1,
    },
  })
})

Example 4: Truncate retain

Only keep the last 2 elements after insertion

const dbCmd = db.command
let res = await db.collection('todos').doc('doc-id').update({
  tags: dbCmd.push({
    each: ['mini-program', 'cloud'],
    slice: -2,
  })
})

Example 5: Insert at the specified position, then sort, and keep only the first 2 elements at the end

const dbCmd = db.command
let res = await db.collection('todos').doc('doc-id').update({
  tags: dbCmd.push({
    each: ['mini-program', 'cloud'],
    position: 1,
    slice: 2,
    sort: 1,
  })
})

pop

Array update operator. For a field whose value is an array, delete the last element of the array. Only the last one can be deleted.

Sample code

const dbCmd = db.command
let res = await db.collection('todos').doc('doc-id').update({
  tags: dbCmd.pop()
})

unshift

The array update operator, for a field whose value is an array, adds one or more values to the head of the array. or the field was originally empty, create the field and set the array as the incoming value.

Sample code

const dbCmd = db.command
let res = await db.collection('todos').doc('doc-id').update({
  tags: dbCmd.unshift(['mini-program', 'cloud'])
})

shift

The array update operator, for a field whose value is an array, deletes the element at the head of the array.

Sample code

const dbCmd = db.command
let res = await db.collection('todos').doc('doc-id').update({
  tags: dbCmd.shift()
})

pull

Array update operator. Given a value or a query condition, removes all elements in the array that match the given value or query condition.

Example code 1: Remove based on constant matching

const dbCmd = db.command
let res = await db.collection('todos').doc('doc-id').update({
  tags: dbCmd.pull('database')
})

Sample code 2: Remove based on query condition matching

const dbCmd = db.command
let res = await db.collection('todos').doc('doc-id').update({
  tags: dbCmd.pull(dbCmd.in(['database', 'cloud']))
})

Example code 3: When the object array is matched, remove it according to the query condition

Suppose there is a field places The elements in the array are structured as follows

{
  "type": string
  "area": number
  "age": number
}

const dbCmd = db.command
let res = await db.collection('todos').doc('doc-id').update({
  places: dbCmd.pull({
    area: dbCmd.gt(100),
    age: dbCmd.lt(2),
  })
})

Example code 4: When there is an object array with nested objects, remove it according to the query condition

Suppose there is a field cities The element structure in the array is as follows

{
  "name": string
  "places": Place[]
}

Place has the following structure:

{
  "type": string
  "area": number
  "age": number
}

elemMatch can be used to match object array fields nested inside object array places

const dbCmd = db.command
let res = await db.collection('todos').doc('doc-id').update({
  cities: dbCmd.pull({
    places: dbCmd.elemMatch({
      area: dbCmd.gt(100),
      age: dbCmd.lt(2),
    })
  })
})

pullAll

Array update operator. Given a value or a query condition, removes all elements from the array that match the given value. The difference from pull is that only constant values can be specified, and an array is passed in.

Example code: remove based on constant matching

Remove all database and cloud strings from tags

const dbCmd = db.command
let res = await db.collection('todos').doc('doc-id').update({
  tags: dbCmd.pullAll(['database', 'cloud'])
})

addToSet

Array update operator. Atomic operation. Given one or more elements, add them to the array unless they already exist in the array.

Example code 1: Adding an element

If the tags array does not contain database, add it

const dbCmd = db.command
let res = await db.collection('todos').doc('doc-id').update({
  tags: dbCmd.addToSet('database')
})

Sample Code 2: Adding Multiple Elements

You need to pass in an object, which has a field each whose value is an array, and each element is the element to be added

const dbCmd = db.command
let res = await db.collection('todos').doc('doc-id').update({
  tags: dbCmd.addToSet({
    $each: ['database', 'cloud']
  })
})