Compare two columns with a JSON

Question

I have 2 columns(Date1 and Date2) in a JSON document that are DateTime. How can I compare? I tried this:

 

Query q1 = QueryBuilder.Build("User", this.UserName, Operator.EQUALS);

 Query q2 = QueryBuilder.Build("Date1", "Date2", Operator.LESS_THAN);

Query query = QueryBuilder.CompoundOperator(q1, Operator.AND, q2);

this.FindDocsWithQueryPagingOrderBy(query, Max, Maxr, "Date2", OrderByType.DESCENDING);

Answer 1

Hi, 

If I understand you requirement correctly, you want to find out the doc where value of date1 field is less than value of date2 field. This type of internal comparison and sub query feature is directly not available however this can be done quickly by adding another field in the JSON doc which will have difference of time between these two values. If that field is saved in the JSON doc, you can then easily query on that field to fetch the target results. 

You have to just add this field at the time of saving the doc and will have time difference value in it. You can than fire the query as shown below.

Query q1= QueryBuilder.Build("timeDiff", 0, Operator.LESS_THAN); //Here timeDiff field stores difference between two date field.

Query q2= QueryBuilder.Build("User", this.UserName, Operator.EQUALS);

Query q3 = QueryBuilder.CompoundOperator(q1, Operator.AND, q2);

this.FindDocsWithQueryPagingOrderBy(q3, Max, Maxr, "Date2", OrderByType.DESCENDING);

 

Let me know if it helps.