How to filter a table

hi, i am new to mongodb and node red so I need help.
let's suggest we have a full table (e.g: name, age, subject) in mongodb which is installed to pi4 and according to columns I made dropdowns that has all choices (name:Rox,Marie...,age: 12,13,..) and according to what I choose in dropdown the table will be filtered and display the rows in another table in dashboard. Wish I made it clear so I don't know what to write in function nodes and all so it works correctly as I want .Can someone help me.

I'm not familiar with Mongo, but I'm quite sure the flow would be similar to other DBs.

What I would do is to use the output msg from the dropdown to generate a filtered query, then get the returned array, and display i with the table node.

I've done it with MySQL databases and works like a charm. Here, I'm using buttons instead of a dropdown, but it should work in the exact same way:

1 Like

Thank u @OriolFM I appreciate it . Will you please pass me the flow so I can see what you use in all nodes? thank u.

I'm sorry but this is a production flow for my job... I pasted the general blocks so you get a head start, but I am not able to just paste the whole thing.

Why don't you give it a try, post what you can do, and we continue from there?

If you have specific questions on how to proceed, I can also answer those.

OK I am sorry I thought it's just those nodes. So I made this table in dashboard and I want to search for some rows according to what I select in dropdown and it shows in another table in dashboard . This is the flow I draw but I know it's a whole mess and function nodes are empty I don't know how to make it work.


Okay, I'd say you're in the right direction.

Filter selection

When you select an option with dropdown, it generates a msg with the value of your choice as the payload string. If you want to be able to filter by more than one field, you'll need context.

To set up the context, place an inject node that injects the following shortly after start:

{
    "Name": "",
    "Age": "",
    "Subjects": ""
}

Then use a change node to push the object to global context.
image

The initialization would look similar to this:
image

After initialising, you prepare the following:
image

Where the change nodes are used to update each filter, like the following:
image

The other two fields will be similar. This prepares all the filters.

Query Generation
Once we have the filters prepared, it's time to use them to generate a query for the MongoDB.
According to the MongoDB node, you need to set the query string into the payload. As I said, I'm not familiar with MongoDB (I usually work with MySQL), but the basics will be the same.

Prepare the button like this:


This button will send a message with the global.filters object as the payload, with all the current values.

Next, you need a function node that takes that message and prepares the query string using the filter values contained in the payload. This is pretty basic, just concatenating strings.

To keep things neat, you can prepare some conditional blocks that will add the string for each filter only if it's not empty, for instance.

Make sure other requirements for the MongoDB node are met by initializing them in that function node (msg.topic, and so on)

Then feed it into the MongoDB node, and send the exit to the Table node (you might need to arrange something, I'm not sure if you can feed it directly). The Table node will need to be configurated properly.

Bonus Track: Status
If you check my diagram, you'll see the "Requested logs" and "Retrieved logs" function nodes.

Requested logs takes the query message, and generates a status string saying "Request sent to the DB, please wait..." (it also sets some other parameters, but for the main purpose, you could do with a change node). If you are using any filters, you can list them in the status line as well.

That string goes into a Text output node to display the status of the DB.

Retrieved logs generates another status message, saying if the logs have been retrieved, and process some additional feedback by the DB (how many rows, time elapsed, and so on).

This is very useful, since the end user can visualize the query status and see the feedback. In the event the query is slow, it helps knowing that it's being processed, instead of wondering if the query has even been sent, and prevents further button-mashing (which is what happens when someone -not you- is using the app.

Hope this helps.

1 Like

Thank u for taking your time explaining to me sir . It will surelly help me completing my project. I will follow your steps . I appreciate it.

You're welcome! Let me know if you manage to do it :smiley: