JSON Query Expression Functions¶

As many Integration Operation parameters take as input JSON expressions, such as those for filtering MongoDB Documents in Collection Find, we have included a set of Expression Functions to help create these JSON Query expressions without having to construct the strings yourself.

_images/image32.png

In addition to generating the special JSON structures MongoDB expects, these functions make creating MongoDB’s non-standard JSON alterations much easier in Appian. The functions also handle converting Appian primitive types to their necessary JSON representations. For example, this expression:

Would produce this JSON:

{
  "createdOn": {
    "$eq": ISODate("2020-07-01T20:32:20.900Z")
  }
}

Note the MongoDB-specific JSON ISODate() function call. Also note the field names that begin with $. It is not easy (and, in some cases such as this one, not possible) to generate JSON in this manner by constructing Dictionaries and using Appian’s built-in a!toJson() function.

More MongoDB query functions may be added in later versions of this plugin. If there are any you would like to see added, please contact the project authors or on the Community App Market entry.

Top-Level Functions¶

`M_query()`¶

This function begins a MongoDB query. It should be the top-level function call whose output is sent to one of the JSON filter parameters. Essentially this wraps the contents provided in braces { ... } to ensure a complete query JSON string.

queryClauses (List of Text String): The list of expressions (often created with M_field()) you wish to evaluate

For example, this expression:

Would produce this JSON:

{
  "createdOn": {
    "$eq": ISODate("2019-04-26T10:28:57.000Z")
  }
}

`M_field()`¶

This function begins a query expression on a field, in the form of fieldName: ... where the passed in queryClauses are joined to complete the expression. To be used within M_query() or one of the other Expression Functions that take in a complete field, such as M_and().

field (Text): The name of the field in the MongoDB Document you wish to filter on

queryClauses (List of Text String): The list of expressions (often created with other m_* functions) you wish to evaluate

For example, this expression:

Would produce this portion of JSON:

"createdOn": { "$eq": ISODate("2019-04-26T10:28:57.000Z") }

Comparison Query Operators¶

These functions correspond directly to the Comparison Query Operators provided by the MongoDB Query language.

These functions handle converting Appian primitive types to their necessary JSON representations. For example, this expression:

Would produce this JSON:

{
  "createdOn": {
    "$eq": ISODate("2020-07-01T20:32:20.900Z")
  }
}

Note the MongoDB-specific ISODate() function call. It is not possible to generate JSON in this manner using Appian’s built-in a!toJson() function.

`M_eq()`¶

Implements the $eq operator. Specifies equality condition. The $eq operator matches documents where the value of a field equals the specified value.

value (Any Type): The value to evaluate against.

`M_gt()`¶

Implements the $gt operator. Selects those documents where the value of the field is greater than (i.e. >) the specified value.

value (Any Type): The value to evaluate against.

`M_gte()`¶

Implements the $gte operator. Selects the documents where the value of the field is greater than or equal to (i.e. >=) a specified value (e.g. value.)

value (Any Type): The value to evaluate against.

`M_in()`¶

Implements the $in operator. Selects the documents where the value of a field equals any value in the specified array.

array (List of Variant): The array of values to evaluate against.

`M_lt()`¶

Implements the $lt operator. Selects the documents where the value of the field is less than (i.e. <) the specified value.

value (Any Type): The value to evaluate against.

`M_lte()`¶

Implements the $lte operator. Selects the documents where the value of the field is less than or equal to (i.e. <=) the specified value.

value (Any Type): The value to evaluate against.

`M_ne()`¶

Implements the $ne operator. Selects the documents where the value of the field is not equal to the specified value. This includes documents that do not contain the field.

value (Any Type): The value to evaluate against.

`M_nin()`¶

Implements the $nin operator. Selects the documents where the field value is not in the specified array or the field does not exist.

array (List of Variant): The array of values to evaluate against.

Logical Query Operators¶

These functions correspond directly to the Logical Query Operators provided by the MongoDB Query language.

For example, this expression:

Would produce this JSON:

{
  "$and": [
    {
      "lastName": {
        "$in": [
          "McIlraith",
          "Orcott",
          "Toulch",
          "Jemmett"
        ]
      }
    },
    {
      "disabled": {
        "$ne": true
      }
    }
  ]
}

`M_and()`¶

Implements the $and operator. Performs a logical AND operation on an array of one or more expressions (e.g. expression1, expression2, etc.) and selects the documents that satisfy all the expressions in the array. The $and operator uses short-circuit evaluation. If the first expression (e.g. expression1) evaluates to false, MongoDB will not evaluate the remaining expressions.

queryExpressions (List of Text String): The list of expressions (often created with other M_* functions) you wish to evaluate against.

`M_nor()`¶

Implements the $nor operator. Performs a logical NOR operation on an array of one or more query expressions and selects the documents that fail all the query expressions in the array.

queryExpressions (List of Text String): The list of expressions (often created with other M_* functions) you wish to evaluate against.

`M_not()`¶

Implements the $not operator. Performs a logical NOT operation on the specified operator-expression and selects the documents that do not match the operator-expression. This includes documents that do not contain the field.

queryExpression (Text): The expression (often created with other M_* functions) you wish to evaluate against.

`M_or()`¶

Implements the $or operator. Performs a logical OR operation on an array of two or more expressions and selects the documents that satisfy at least one of the expressions.

queryExpressions (List of Text String): The list of expressions (often created with other M_* functions) you wish to evaluate against.

Element Query Operators¶

These functions correspond directly to the Element Query Operators provided by the MongoDB Query language.

`M_exists()`¶

Implements the $exists operator. When value is true, $exists matches the documents that contain the field, including documents where the field value is null. If value is false, the query returns only the documents that do not contain the field.

value (Boolean): Whether it should exist or not.

`M_type()`¶

Implements the $type operator. Selects documents where the value of the field is an instance of the specified BSON type(s). Querying by data type is useful when dealing with highly unstructured data where data types are not predictable.

types (List of Variant): Either the BSON type numbers (integer) or aliases (string).

Evaluation Query Operators¶

These functions correspond directly to the Evaluation Query Operators provided by the MongoDB Query language.

`M_expr()`¶

Implements the $expr operator. Allows the use of aggregation expressions within the query language.

queryExpression (Dictionary or Text): The expression (often created with other M_* functions) you wish to evaluate against.

`M_jsonSchema()`¶

Implements the $jsonSchema operator. Matches documents that satisfy the specified JSON Schema.

jsonSchema (Dictionary or Text): The JSON Schema object as an Appian Dictionary or a JSON string, formatted according to draft 4 of the JSON Schema standard.

`M_mod()`¶

Implements the $mod operator. Select documents where the value of a field divided by a divisor has the specified remainder (i.e. perform a modulo operation to select documents).

divisor (Number (Integer)): The divisor value.

remainder (Number (Integer)): The remainder value.

`M_regex()`¶

Implements the $regex operator. Provides regular expression capabilities for pattern matching strings in queries. MongoDB uses Perl compatible regular expressions (i.e. “PCRE”) version 8.42 with UTF-8 support.

regex (Text): The regular expression (without enclosing slashes), e.g. "^foo.\*bar$"

options (Text): The regular expression options modifiers ("i", "m", "s", and/or "x"), e.g. "im" for ‘ignore case’ and ‘multiline’ searches

This example would match all MongoDB Documents where the last name begins with “St”:

Which would produce this JSON:

{
  "lastName": {
    "$regex": /^St/i
  }
}

`M_text()`¶

Implements the $text operator. Performs a text search on the content of the fields indexed with a text index.

search (Text): A string of terms that MongoDB parses and uses to query the text index. MongoDB performs a logical OR search of the terms unless specified as a phrase.

language (Text): Optional (use null to omit). The language that determines the list of stop words for the search and the rules for the stemmer and tokenizer. If not specified, the search uses the default language of the index.

caseSensitive (Boolean): Optional (use null to omit). A boolean flag to enable or disable case sensitive search. Defaults to false; i.e. the search defers to the case insensitivity of the text index.

diacriticSensitive (Boolean): Optional (use null to omit). A boolean flag to enable or disable diacritic sensitive search against version 3 text indexes. Defaults to false; i.e. the search defers to the diacritic insensitivity of the text index.

`M_where()`¶

Implements the $where operator. Use the $where operator to pass either a string containing a JavaScript expression or a full JavaScript function to the query system. The $where provides greater flexibility but requires that the database processes the JavaScript expression or function for each document in the collection. Reference the document in the JavaScript expression or function using either this or obj. Please see full documentation for caveats and performance topics.

javaScript (Text): A JavaScript expression or a full JavaScript function.

This example would match all MongoDB Documents where the last name equals “Gudgen”:

Which would produce this JSON:

{
  "$where": "function() { return (hex_md5(this.lastName) == \'9af26c4c8b156852e86d49566d96a0d1\') }"
}

Array Query Operators¶

These functions correspond directly to the Array Query Operators provided by the MongoDB Query language.

`M_all()`¶

Implements the $all operator. Selects the documents where the value of a field is an array that contains all of the specified elements.

array (List of Variant): The array of values to evaluate against.

`M_elemMatch()`¶

Implements the $elemMatch operator. Matches documents that contain an array field with at least one element that matches all the specified query criteria.

queryExpressions (List of Text String): The list of expressions (often created with other M_* functions) you wish to evaluate against.

`M_size()`¶

Implements the $size operator. Matches any array with the number of elements specified by the argument.

value (Number (Integer)): The number of elements to evaluate for.

Geospatial Query Operators¶

These functions correspond directly to the Geospatial Query Operators provided by the MongoDB Query language.

_images/image33.png

Note

Many of the following geospatial query operators require that a geospatial index be added to the fields you wish to query. See MongoDB documentation for more information.

`M_toPoint()`¶

Returns a Point CDT instance with the given coordinates. Used any time a [longitude, latitude] pair is required in the following Expression Functions.

longitude (Number (Decimal)): The longitude of the point.

latitude (Number (Decimal)): The latitude of the point.

`M_geoIntersects()`¶

Implements the $geoIntersects operator. Selects documents whose geospatial data intersects with a specified GeoJSON object; i.e. where the intersection of the data and the specified object is non-empty.

geoJson (Dictionary or Text): A GeoJSON object, as an Appian Dictionary or a JSON string.

`M_geoWithin()`¶

Implements the $geoWithin operator. Selects documents with geospatial data that exists entirely within a specified shape.

geoJson (Dictionary or Text): A GeoJSON object, as an Appian Dictionary or a JSON string.

`M_geoWithinBox()`¶

Implements the $geoWithin operator, using a $box to search within. Selects documents with geospatial data that exists entirely the bounds of the given rectangle, according to their point-based location data.

bottomLeft (Point): Point with the coordinates of the bottom-left corner of the box.

topRight (Point): Point with the coordinates of the top-right corner of the box.

`M_geoWithinCircle()`¶

Implements the $geoWithin operator, using a $center (circle) to search within. Selects documents with geospatial data that exists entirely the bounds of the given circle, defined by a center point and radius.

centerPoint (Point): Point with the coordinates of the center of the circle.

radius (Number (Decimal)): The radius of the circle, as measured in the units used by the coordinate system.

`M_geoWithinSphere()`¶

Implements the $geoWithin operator, using a $centerSphere to search within. Selects documents with geospatial data that exists entirely the bounds of the given sphere, defined by a center point and radius.

centerPoint (Point): Point with the coordinates of the center of the sphere.

radius (Number (Decimal)): The radius of the sphere, as measured in the units used by the coordinate system.

`M_geoWithinPolygon()`¶

Implements the $geoWithin operator, using a $polygon to search within. Selects documents with geospatial data that exists entirely the bounds of the given polygon, defined by an array of GeoPoints.

This example would match all MongoDB Documents where the address’s loc field (a Point) is within an area roughly outlining McLean, VA:

Which would produce this JSON:

 {
  "address.loc": {
    "$geoWithin": {
      "$polygon": [
        [ -77.210903, 38.970481 ],
        [ -77.1451, 38.964342 ],
        [ -77.118225, 38.935857 ],
        [ -77.17236, 38.893213 ],
        [ -77.189069, 38.896396 ],
        [ -77.198767, 38.92649 ],
        [ -77.233222, 38.933877 ],
        [ -77.226622, 38.962158 ]
      ]
    }
  }
}

polygonPoints (List of Point): Array of GeoPoints representing the polygon to search within. Be sure to pass an actual Appian Array here inside of brackets ({}).

`M_near()`¶

Implements the $near operator. Specifies a point for which a geospatial query returns the documents from nearest to farthest.

This example would match all MongoDB Documents where the address’s loc field (a Point) is within 1 km of Appian HQ:

Which would produce this JSON:

{
  "address.loc": {
    "$near": {
      "$geometry": {
        "type": "Point",
        "coordinates": [ -77.219049, 38.932308 ]
      },
      "$minDistance": 0.0,
      "$maxDistance": 1000.0
    }
  }
}

geoJson (Dictionary or Text): A GeoJSON object, as an Appian Dictionary or a JSON string.

minDistance (Number (Decimal)): Minimum distance in meters.

maxDistance (Number (Decimal)): Maximum distance in meters.

`M_nearSphere()`¶

Implements the $nearSphere operator. Specifies a point for which a geospatial query returns the documents from nearest to farthest. MongoDB calculates distances for $nearSphere using spherical geometry.

geoJson (Dictionary or Text): A GeoJSON object, as an Appian Dictionary or a JSON string.

minDistance (Number (Decimal)): Minimum distance in meters.

maxDistance (Number (Decimal)): Maximum distance in meters.

Query Operator Examples¶

Date and Time Examples¶

Query for Dates in a Range¶

This expression will produce a query for finding MongoDB Documents with createdOn in the month of December 2019:

Which produces this JSON:

{
  "createdOn": {
    "$gte": ISODate("2019-12-01T00:00:00.000Z"),
    "$lt": ISODate("2020-01-01T00:00:00.000Z")
  }
}

Querying by Date Without Time¶

As mentioned earlier, MongoDB stores all dates in UTC and does not have a date without time. Similar to above, in order to find all MongoDB Documents with createdOn on a single day, use $gte of that day:

Which produces this JSON:

{
  "createdOn": {
    "$gte": ISODate("2019-12-01T00:00:00.000Z"),
    "$lt": ISODate("2020-01-01T00:00:00.000Z")
  }
}

More examples will be added in newer versions of this plugin.

Read the Docs v: 1.1

Versions: latest; 1.2; 1.1

Downloads: html

On Read the Docs: Project Home; Builds

Free document hosting provided by Read the Docs.