Expression: deduplicate records of nested property

[Q&A] General
1

I have an expression that needs deduplication of a nested property. We have a working solution right now, but we’d like to optimise this as much as possible because of the enormous size of the answers table.

What I need
A list of answers of unique questions from activity (forms), but these forms have an overlap in questions (e.g. some questions are answered in mulitple forms, but always have the same answer).

The following expression performs fine, but is not deduplicated on the question.

{
 answers,
 this,
 boolean:and([
  this.activity.in({
   systemversions,
   this.acceptance_activity,
   this.owner.equals(parent.parent.activity.contact)
  }),
  this.question.reference.in(["acceptance-statement","cyber-acceptance-statement"]),
  this.answer_multiple_choice.equals(responsescaleoptions:load(13))
 ])
}

Is there an Expression Hero that can push me in the right direction? Thanks a lot!

2

@David, hello! Don’t you use a QuestionConnection data model? It is the linked table between Questions and Answers, so the question and answer records could be the same but attached to the different question connections.

3

@k.molokova As far I’ve checked doesn’t this apply to answers in a activity (type form). The answer only is linked to a question and an activity.

4

I do not know the exact data model, but I would suggest the following approach.

Write an expression to get all (deduplicated) questions first.

{
questions, 
this, 
boolean:and([
this.reference.in(["acceptance-statement","cyber-acceptance-statement"])
])
}

But obviously you do not want to have a list of questions but their answers.
So replace the “this” with a answers:first({}) that gets the first relevant answer for the parent question, like so:

{
questions, 
answers:first({
  answers, 
  this,
  boolean:and([
    this.question.equals(parent),
    this.activity.in({
     systemversions, 
     this.acceptance_activity,
     this.owner.equals(parent.parent.parent.activity.contact),
     this.answer_multiple_choice.equals(responsescaleoptions:load(13))
  }),
  id.desc
}), 
boolean:and([
 this.reference.in([ "acceptance-statement","cyber-acceptance-statement"
])
])
}

The result should be a list of relevant answers.
I added the sorting id.desc so you always get the newest answer.

I am not sure about the this.activity.in([]). Please double-check it. I added another “parent”, but feel free to adjust the filtering of the answers:first({}).

I hope this helps :slight_smile:

5

Hi David,

My default way of deduplication is as follows:

  1. Write the expression that return the non deduplicated list
  2. Add an extra boolean:not(exists)-filter to the list of filters where the list-parameter is exactly the same as the list expression (ofcourse add an extra parent if needed) of the first step and expand the filter with id.isgreater(parent.id)

So in your example:

The result of step 1 is the provided expression:

{
 answers,
 this,
 boolean:and([
  this.activity.in({
   systemversions,
   this.acceptance_activity,
   this.owner.equals(parent.parent)
  }),
  this.question.reference.in(["acceptance-statement","cyber-acceptance-statement"]),
  this.answer_multiple_choice.equals(responsescaleoptions:load(13))
 ])
}

For step 2 we expand the filter of the first expression with boolean:not(answers:exists({}). In this list {} we copy the expression of step 1, add an extra parent if needed, and extend the filter with id.isgreater(parent.id). Result is as follows:

{
 answers,
 this,
 boolean:and([
  this.activity.in({
   systemversions,
   this.acceptance_activity,
   boolean:and([
    this.owner.equals(parent.parent),
    acceptance_activity.isnotnull()
   ])
  }),
  this.question.reference.in(["acceptance-statement","cyber-acceptance-statement"]),
  this.answer_multiple_choice.equals(responsescaleoptions:load(13)),
  boolean:not(
   answers:exists({
    answers,
    this,
    boolean:and([
     this.activity.in({
      systemversions,
      this.acceptance_activity,
      boolean:and([
       this.owner.equals(parent.parent.parent),
       acceptance_activity.isnotnull()
      ])
     }),
     this.question.reference.in(["acceptance-statement","cyber-acceptance-statement"]),
     this.answer_multiple_choice.equals(responsescaleoptions:load(13)),
     id.isgreater(parent.id)
    ])
   })
  )
 ])
}