Aggregation Query not rendering transformer object for MongoDB

[Mark_Simpson]

I am trying to build a dynamic Mongodb query resource that will add filters to the query when items are selected from various multiselect boxes.

• Steps: I have build an aggregation resource query in mongo, I have built transformers that check to see if the value of a selectbox has been selected and if so will return a mongoDB object. I have tried to JSON.stringify() but nothing seems to work.

termsTransformer

const tagsSelectValues = {{ tagsSelect.value }}
const query = {
    terms: { $in: tagsSelectValues }
};

return query;

It seems to render [object Object]

If I add the transformer to a find query instead of an aggregation it will work

[jg80]

When you examine termsTransformer is it an object? You said you tried JSON.stringify() and it didn't work, but where did you add it? Like this:

const tagsSelectValues = {{ tagsSelect.value }}
const query = {
    terms: { $in: tagsSelectValues }
};

return JSON.stringify(query);

I'm guessing termsTransformer.value needs to be a string already, so adding JSON.stringify() within the query might not work?

Clearly shots in the dark, but I don't see why what you are doing shouldn't work...

[Mark_Simpson]

Thank you for you reply.
Yep definitely an object.

I've tried json.stringify() as well, then it complains that I'm supplying a string and NOT an object:

It only seems to not parse on the aggregation action type, seems fine if I just use it in find:

I have a feeling this could be a bug

[jg80]

Ugh. I don't use MongoDB, so I don't know how all the parts work together and what they all expect in terms of format of the input.

If you came to my house and asked me to fix it in return for a beer on you, I would try:
(1) JSON.stringify the termsTransformer result so that it is a string passed to the Aggregation instead of an object
(2) leave termsTransformer as an object and add JSON.stringify(termsTransformer.value) to the Aggregation
(3) write the whole aggregation in a JS query that returns the fully formed JSON as an object and try passing that to the Aggregation section of the query
(4) Do (3) but JSON.stringify() it before passing it
(5) Do (3) but JSON.stringify() it in the Aggregation section of the query
(6) Give up and buy you the beer instead...

I put the bug tag on the post for you to get a few more eyes on it - maybe someone who uses MongoDB in Retool all the time is chuckling contentedly for the two free beers they are about to get by answering this super easy question

post-facto edit to note that option (3) worked as the solution

[Mark_Simpson]

Number 3 seems to work if you return the object, no stringifying needed.
I must say it's a faff, but it works:
JS Transformer:

// Access the Retool components directly
const siteSelectValue = {{ siteSelect.value }};
const dateRangeStart = {{ dateRange.value.start }};
const dateRangeEnd = {{dateRange.value.end}};
const tagsSelectValue = {{tagsSelect.value}};
// Add additional $match requirements in contact_details
const viewerProfileValue = {{ viewerProfile.value}};
const viewerCountryValue = {{ viewerCountry.value}};
const viewerJobTitleValue = {{viewerJobTitle.value}};

// Define the initial $match object
let matchStage = {
  site_id: { $in: siteSelectValue },
    view_date: {
    "$gte": { 
      "$date" : new Date(`${dateRangeStart}T00:00:00.904+00:00`),
    },
      
    "$lte": {
      "$date" : new Date(`${dateRangeEnd}T23:59:00.904+00:00`)
      }
    }
};

// Check if tagsSelect.value is set and has elements
if (Array.isArray(tagsSelectValue) && tagsSelectValue.length > 0) {
  matchStage.terms = { $in: tagsSelectValue };
}

// Define the rest of the pipeline
const pipeline = [
  { $match: matchStage },
  {
    $lookup: {
      from: "contacts",
      localField: "user_id",
      foreignField: "_id",
      as: "contact_details"
    }
  },
  { $unwind: "$contact_details" },
  {
    $group: {
      _id: {
        object_id: "$object_id",
        object_title: "$object_title"
      },
      total_views: { $sum: 1 }
    }
  },
  {
    $project: {
      video_title: "$_id.object_title",
      total_views: 1,
      _id: 0
    }
  },
  { $sort: { total_views: -1 } }
];

// Return the pipeline
return { pipeline };

Then add the transformer to the aggregation

{{ buildMongoQuery.value.pipeline }}

Thanks so much for you help. This means my meeting with marketing heads on Monday will require a little less "Use your imagination for now"

[jg80]

@Mark_Simpson

faff

My new favorite word - good luck Monday!