Hi ForestAdmin team,
is there a way to be able to filter JSON payloads thru the Forest Admin GUI ?
In this example, i’d like to be able to filter by “payload - includes - “4. Communication””. But i can’t select the “payload” column in the filter, because i guess JSON i by default forbidden in your GUI
Thanks a lot for your help !
Hello @Aarbel,
It is not supported by default as you mentioned. However I can suggest you to add a smart segment on your collection using the query mode to code the logic you want to filter on. 
Thanks @anon16419211 for the tip,
This would be really interesting to provide (Json to string) to be able to search also on json content thru the list view.
Did you have any updates since 2 years to provide this filter / search on json ?
Basically having something like "[JSON_KEY] includes [SOME_STRING] would be really nice, just treating the JSON field as a string.
Hello @Aarbel,
Sadly you would have to migrate to the @forestadmin/agent to benefit from the Flattener which would answer your needs out of the box.
Supposing that you would stay on forest-express-sequelize, I could suggest that you create a Smart Field defined as a string, you could populate the field by extracting the desired properties from the JSON payload to then be able to filter/sort.
Best regards,
Thanks @dogan i added a smart field, but this field is no provided in the filter. How to be able to filter by the content of the custom field ?
First I tried the “search” method
fields: [
{
field: "payloadstring",
get: (payload) => {
return JSON.stringify(payload);
},
search: function (query, search) {
// Create a condition to check if the search string exists within the payloadstring
const searchCondition = {
payloadstring: { [Op.like]: `%${search}%` }, // Wildcard search
};
// Ensure the query's `where` clause exists and add the condition
if (!query.where) {
query.where = {};
}
if (!query.where[Op.and]) {
query.where[Op.and] = [];
}
query.where[Op.and].push(searchCondition);
return query;
},
type: "String",
},
],
But it doesn’t work in this case searching “d1533f47-afc9-4f98-aa74-f83db1bee9e8” in this stringified payload:
{"createdAt":"2021-10-26T08:40:49.420Z","id":"da33b272-b51e-4490-b75c-c3722ba8edc9","payload":{"file":{"id":"d1533f47-afc9-4f98-aa74-f83db1bee9e8","name":"File1InFolder1Nested"},"oldAssignations":[]},"type":"PROJECT/DOCUMENT/FILE/FILE_ASSIGNATIONS_UPDATED","updatedAt":"2021-10-26T08:40:49.420Z","creatorIdKey":"86c8c162-3405-457c-9724-727aea142580","creator":{"auth0Id":"auth0|60b61eeba278b7006ab1efc3","createdAt":"2021-04-22T17:42:47.265Z","deletedAt":null,"email":"victor@hugo.com","firstName":"victor","id":"86c8c162-3405-457c-9724-727aea142580","isActive":true,"isConnected":false,"isLegacyUser":false,"isLocked":false,"language":"en","lastName":"hugo","phone":"+33102030405","streamUserId":"61824832e0dcb4152bc6a96f","timezone":"Europe/Paris","updatedAt":"2024-12-06T11:52:28.641Z","wopiUserInfo":null}}
Can you give a working example for a “search” function that returns well a result from a stringified json ?
Hello @Aarbel,
I would suggest sticking to the filter function available when defining a field, as it would result in the best performance. If not there is no need to define a Smart Field, you could just override the search on the whole collection.
You were not able to filter on your field because you were missing the isFilterable: true configuration on your field, as specified in the documentation of the Smart Field
In the same mindset of keeping the performance impact low, it would be best to restrict the path on which you search the value in your json, as the condition tree exposed is Sequelize’s. Here is what it would look like:
{
field: "payloadstring",
type: "String",
isFilterable: true,
get: (payload) => {
return JSON.stringify(payload);
},
filter({ condition, where }) {
switch (condition.operator) {
case "equal":
return {
"payload.file.id": condition.value,
};
case "contains":
return {
"payload.file.id": { [Op.like]: `%${condition.value}%` },
};
// ... And so on with the other operators not_equal, starts_with, etc.
default:
return null;
}
},
},
I’ve filled it with the path present in your last message. If you wish to make it work with multiple possible path I would suggest chaining them with an Or operator.
{
[Op.or]: [
{ 'payload.file.id': search },
{ 'payload.another.path': search, },
]
};
After defining your Smart Field, you will have to enable the filtering on your field on the frontend as shown here.
Thanks a lot @dogan, but it’d like to have results on the whole json string “payloadstring”, and not having to hardcode the searched values function of the payload object (payload.file.id for example).
Why ? Because my payloads have different structure, so i want the forest implementation to be generic whatever the payloads are.
I just want that:
Otherwise using a custom field (here “payloadstring”) isn’t useful at all to filter json content…
Here it is:
search: function (query, search) {
var searchCondition = {
[Op.and]: [
literal(`payload::text ilike '%${search}%'`)
],
};
query.where[Op.and][0][Op.or].push(searchCondition);
return query;
},
filter({ condition, where }) {
if (condition.operator === "contains") {
return {
[Op.and]: [
literal(`payload::text ilike '%${condition.value}%'`)
]
};
}
return null;
},
However this might/will have an impact on performance depending on the size of your collection and the length of your payload column.
You can mitigate performance impact when using the global search by removing the search function (as it would be applied on every search you make on the collection). And only filter with contains when you wish to find the matching payload.