Prisma Orm Json string_contains method without base path

Question

I am trying to do a filter on a json type field with the string contains because I want to search for all the json content

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

but this filter is not working for me and I cannot specify a path because it must be filtered from the root

The json structure is that

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

How to filter from the root by any json content or an alternative to be able to filter

Answer 1

If you want to perform a text search across all values in the JSON field, you'd typically need to use a database-specific function or operator. For PostgreSQL, you could use the jsonb_to_tsvector function to convert the JSONB data to a tsvector, and then use the full-text search capabilities of PostgreSQL.

Here's an example of how you can use the prisma.$queryRaw function to write a raw SQL query that uses 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})
`;