Prisma ORM has no Json string_contains method for base path

Question

I am trying to filter a json type field that contains strings because I want to search all json content

this._prismaService.service.findMany({
          ...args,
          where: {
            OR: [
              {
                nameTranslatableJson: {
                  string_contains: filters.search,
                },
              },
            ],
          },
});

But this filter doesn't work for me, I can't specify a path because it has to filter from the root directory

The structure of json is like this

{
  "defaultText": "Prueba???",
  "ES": "What???",
  "EN": "What???"
}

How to filter any json content from the root directory or is there an alternative way to do the filtering

Answer 1

If you want to perform a text search on all values ​​of a JSON field, you typically need to use a database-specific function or operator. For PostgreSQL, you can use the jsonb_to_tsvector function to convert JSONB data to tsvector, and then use PostgreSQL's full-text search functionality.

Here is an example of how to use the prisma.$queryRaw function to write a raw SQL query using the jsonb_to_tsvector function:

const searchResults = await this._prismaService.$queryRaw`
  SELECT *
  FROM "Service"
  WHERE to_tsvector('english', "nameTranslatableJson"::text) @@ plainto_tsquery('english', ${filters.search})
`;