quickest-silver•4mo ago

filter on key inside nested JSON

[FEATURE] Deep Filtering on Nested JSON Fields in Supabase I'm currently working on a project using Refine with Supabase as the data provider. I have a use case where I need to filter data based on a nested JSON field. However, it seems that Refine does not support deep filtering on nested JSON fields out of the box. Here's a snippet of my current implementation:

const {{ tableProps, searchFormProps }} = useTable({{
  onSearch: (values: any) => {{
    console.log('values', values);
    return [
      {{
        field: "data->>'spanish'",
        operator: "contains",
        value: values.value,
      }},
    ];
  }},
}});

const {{ tableProps, searchFormProps }} = useTable({{
  onSearch: (values: any) => {{
    console.log('values', values);
    return [
      {{
        field: "data->>'spanish'",
        operator: "contains",
        value: values.value,
      }},
    ];
  }},
}});

where data is a JSONB field in Postgresql containing a key named spanish.

3 Replies

kapa.ai•4mo ago

Hi @LazerJesus. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave: ---------------------- Refine supports deep filtering on a relation's fields. This is done by using the field property in the filters object of the useTable hook. The field property uses dot notation to access inner fields of the categories. Here's an example:

const { tableProps, sorter } = useTable({  
 resource: "posts",  
 filters: {  
 initial: [  
 { field: "categories.title", operator: "eq", value: "Beginning" },  
 ],  
 },  
 meta: {  
 select: "*, categories!inner(title)",  
 },  
});

const { tableProps, sorter } = useTable({  
 resource: "posts",  
 filters: {  
 initial: [  
 { field: "categories.title", operator: "eq", value: "Beginning" },  
 ],  
 },  
 meta: {  
 select: "*, categories!inner(title)",  
 },  
});

In this example, it gets the posts where the title of the categories is "Beginning". The inner fields of the categories can be reached with dot notation. However, please note that if you filter based on a table from an inner join, you will need to use .select('*, mytable!inner(*)') within Supabase. For more complex or nested filtering, there have been discussions and feature requests in the Refine community. For instance, there was a feature request for nested filters in the Hasura data provider, where the team decided to implement dot notation to allow filtering via nested objects. This feature was included in the @pankod/refine-hasura@4.9.0 release.

{
 field: "category.type",
 operator: "eq",
 value: "value"
}

{
 field: "category.type",
 operator: "eq",
 value: "value"
}

While this feature was implemented for Hasura, it might be worth checking if similar functionality is available or can be implemented for Supabase. Sources: - Deep Filtering (https://refine.dev/docs/data/packages/supabase#deep-filtering) - Nested filters in Hasura data provider (https://github.com/refinedev/refine/issues/2320) ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•4mo ago

<@271865611209015297> kapa.ai is still learning and improving, please let us know how it did by reacting below

quickest-silver•4mo ago

Help needed.