How can search field jsonb nextjsx from refine?

5 Replies

Hi @tingadev. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave: ---------------------- To search a JSONB field in a PostgreSQL database using Refine, you can use the useTable hook and specify the field, operator, and value in the onSearch method. Below is an example of how you can achieve this:

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

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

In this example, data is a JSONB field in PostgreSQL containing a key named spanish. The field property uses the PostgreSQL JSONB path syntax to specify the nested field you want to search. The operator is set to contains to perform a search operation, and value is the search term provided by the user. For more details, you can refer to the GitHub issue discussing deep filtering on nested JSON fields (https://github.com/refinedev/refine/issues/5809). If you need further customization, you can swizzle the Supabase data provider and modify it as needed. You can find more information on how to do this in the Refine documentation (https://refine.dev/docs/packages/cli/#commands). ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•4d ago

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

wise-white•4d ago

How can search field jsonb array string nextjsx from refine? @kapa.ai

kapa.ai•4d ago

Let me check the knowledge base for you. One moment! :books: ---------------------- To search a JSONB array of strings in a PostgreSQL database using Refine, you can use the useTable hook and specify the field, operator, and value in the onSearch method. Below is an example of how you can achieve this:

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

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

In this example, data is a JSONB field in PostgreSQL containing an array of strings under the key genres. The field property uses the PostgreSQL JSONB path syntax to specify the nested field you want to search. The operator is set to contains to perform a search operation, and value is the search term provided by the user. For more advanced filtering, such as filtering an array column with an array, you might need to use specific operators like ina or ina_overlap. Here is an example of how you can use the ina operator:

const { tableProps, searchFormProps } = useTable({
  onSearch: (values) => {
    return [
      {
        field: "data->>'genres'",
        operator: "ina",
        value: values.genresArray, // This should be an array of strings
      },
    ];
  },
});

const { tableProps, searchFormProps } = useTable({
  onSearch: (values) => {
    return [
      {
        field: "data->>'genres'",
        operator: "ina",
        value: values.genresArray, // This should be an array of strings
      },
    ];
  },
});

In this case, values.genresArray should be an array of strings that you want to filter by. The ina operator will match any record where the genres array contains any of the strings in values.genresArray. For more details, you can refer to the GitHub issue discussing filtering array columns with arrays (https://github.com/refinedev/refine/issues/5902). If you need further customization, you can swizzle the Supabase data provider and modify it as needed. You can find more information on how to do this in the Refine documentation (https://refine.dev/docs/packages/cli/#commands). ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•4d ago

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