Question 114198 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – fuzzy finding through database – prisma

I am trying to build a storage manager where users can store their lab samples/data. Unfortunately, this means that the tables will end up being quite dynamic, as each sample might have different data associated with it. I will still require users to define a schema, so I can display the data properly, however, I think this schema will have to be represented as a JSON field in the underlying database.

I was wondering, in Prisma, is there a way to fuzzy search through collections. Could I type something like help and then return all rows that match this expression ANYWHERE in their columns? (including the JSON fields). Could i do something like this at all with posgresql? Or with MongoDB?

thank you

Tags:

2

Answers


  1. 0

    You can easily do that with jsonb in PostgreSQL.

    If you have a table defined like

    CREATE TABLE userdata (
   id bigint PRIMARY KEY,
   important_col1 text,
   important_col2 integer,
   other_cols jsonb
);

    You can create an index like this

    CREATE INDEX ON userdata USING gin (other_cols);

    and search efficiently with

    SELECT id FROM userdata WHERE other_cols @> '{"attribute": "value"}';

    Here, @> is the JSON containment operator in PostgreSQL.

    Login or Signup to reply.
  2. 0

    Yes, in PostgreSQL you surely can do this. It’s quite straightforward. Here is an example.
    Let your table be called the_table aliased as tht. Cast an entire table row as text tht::text and use case insensitive regular expression match operator ~* to find rows that contain help in this text. You can use more elaborate and powerful regular expression for searching too.
    Please note that since the ~* operator will defeat any index, this query will result in a sequential scan.

    select * -- or whatever list of expressions you need
from the_table as tht
where tht::text ~* 'help';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search