Segmentation by Profile structure

Description

You can create queries to MongoDB for complex segmentation of profiles by JSON fields.To create a query, set the following conditions for your segment:

• Selection by — Profile Structure

• Selection condition — EJSON Request

Next, describe the request in the EJSON format.

Operators

Various operators are used for an extended JSON database query. You can read more about EJSON for MongoDB in the documentation. Below we will show you how to work with the main operators.

For example, the database has a profile with an additional JSON field — list:

This field contains the following array:

[
  {
    "contract_id": 10,
    "category": "A",
    "payment": 1000
  }
]

The tables show queries that will allow you to select a given profile from the database:

Comparison operators

Operator	Description	Example
$eq	Matches values that are equal to a specified value.	{    "list.category": {       "$eq": "A"    } }
$ne	Matches all values that are not equal to a specified value.	{    "list.category": {       "$ne": "B"    } }
$gt	Matches values that are greater than a specified value.	<br/>   "list.payment": {       "$gt": 500    } }
$gte	Matches values that are greater than or equal to a specified value.	{    "list.payment": {       "$gte": 1000    } }
$lt	Matches values that are less than a specified value.	{    "list.payment": {       "$lt": 1500    } }
$lte	Matches values that are less than or equal to a specified value.	{    "list.payment": {       "$lte": 1000    } }
$in	Matches any of the values specified in an array.	{    "list.contract_id": {       "$in": [          10,          20,          30       ]    } }
$nin	Matches none of the values specified in an array.	{    "list.contract_id": {       "$nin": [          20,          30,          40       ]    } }

Logical operators

Operator	Description	Example
$and	Joins query clauses with a logical AND returns all documents that match the conditions of both clauses.	{    "$and": [       {          "list.payment": {             "$gte": 1000          }       },       {       "_city":      {             "$eq": "Barcelona"          }       }    ] }
$not	Inverts the effect of a query expression and returns documents that do not match the query expression.	{    "list.category": {       "$not": {          "$eq": "D"       }    } }
$or	Joins query clauses with a logical OR returns all documents that match the conditions of either clause.	{    "$or": [       {          "list.payment": {             "$gte": 2000          }       },       {          "_city": {             "$eq": "Barcelona"          }       }    ] }
$nor	Joins query clauses with a logical NOR returns all documents that fail to match both clauses.	{    "$nor": [       {          "list.payment": {             "$gte": 1500          }       },       {          "_city": {             "$eq": "Barcelona"          }       }    ] }

$elemMatch

The $elemMatch operator allows you to select profiles whose array stored in a JSON field contains at least one element that matches all conditions.

Profile#1	Profile#2
"list":    [       {       "category":"A",       "payment": 1000    },    {       "category":"B",       "payment": 2000    } ]	"list":    [       {       "category":"B",       "payment": 1500    },    {       "category":"C",       "payment": 2000    } ]

The query will select Profile#1 and Profile#2:

{
  "list.category": "B",
  "list.payment": 2000
}

The query will only select Profile#1:

{
  "list": {
    "$elemMatch": {
      "category": "B",
      "payment": 2000
    }
  }
}

caution

Only simple data types can be compared. Complex types (objects, arrays) cannot be compared because the preservation of field order in the request and the profile database is not guaranteed.

Example of invalid comparison:

// Comparison of profile JSON field "list"
{
  "list": {
    "$eq": {
      "contract_id": 10,
      "category": "A",
      "payment": 1000
    }
  }
}

Instead of comparing entire objects, it is better to compare by keys and their values. For example:

{
  "list.category": {
    "$eq": "A"
  }
}

Date

Use the {"$dateNowFunc": "0"} argument to specify a date offset in days.

The query will select profiles updated no later than yesterday:

{
  "_updatetime": {
    "$gte": {
      "$dateNowFunc": "-1"
    }
  }
}

You can also use this function to work with dates stored in JSON fields. For example, the following array is written in the additional profile field "list":

"list":
	[
		{
			"contract_id":10,
			"category": "A",
			"start_date":"2022-07-01",
 			"expiration_date":"2022-08-01"
		},
		{
			"contract_id":20,
 			"category": "B",
			"start_date":"2022-06-15",
			"expiration_date":"2022-09-15"
		}
	]

The query will select profiles with which the contract of category "A" was concluded no earlier than a week ago:

{
  "list": {
    "$elemMatch": {
      "category": "A",
      "start_date.r": {
        "$gte": {
          "$dateNowFunc": "-7"
        }
      }
    }
  }
}

The query will select profiles with which a category "B" contract has been concluded. In addition, this contract will expire in less than a month:

{
  "list": {
    "$elemMatch": {
      "category": "B",
      "expiration_date.r": {
        "$lte": {
          "$dateNowFunc": "30"
        }
      }
    }
  }
}

info

Please note that birthdays, registration dates, as well as dates recorded in additional fields are stored in the platform in the following form:

"date" : {
                "r" : ISODate("2020-09-30T07:42:30.501Z"),
                "y" : 2020,
                "m" : 9,
                "d" : 30,
                "md" : 930,
                "ymd" : 20200930
            }

When you write a path to such a field, add an additional key - r. For example date.r.