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

Postgresql – How to search a text efficient way in a text field? Spring Boot + JPA + PostgresSQL

public class Post {
    private int id;
    private String title;
    private String content;
    private Date createdAt;
}

content field is the text field. This field value actually is a Json object, I converted it to text.

How can search efficient way a text pattern match or not?

I want to implement using spring boot, JPA and postgresSQL

Tags:

3

Answers


  1. 0

    You can use FTS on postgres.
    https://www.postgresql.org/docs/current/textsearch.html

    fts will provide searching similar to search engines but you need to configure it as per your need. it will be much faster than like/ilike ‘%query%’.

    Login or Signup to reply.
  2. 0

    If I understand your question correctly you have a JPA repository already.
    All you need to do is add a method and add the JPQL as below.

    public interface PostRepository extends JpaRepository<Post, Integer> {

    @Query("SELECT p FROM Post p WHERE p.content LIKE %:keyword%")
    List<Post> searchByContent(String keyword);
}

    Then in your service:

    @Service
public class PostService {

    @Autowired
    private PostRepository postRepository;

    public List<Post> searchByContent(String keyword) {
        return postRepository.searchByContent(keyword);
   
    }
}
    Login or Signup to reply.
  3. 0

    If content is JSON, why not use native Postgres json (or jsonb) type for that field, and then search inside it using native JSON operators.

    This should allow you to request on the actual value, instead of doing clunky text search on a String (and potentially even selecting unexpected data).

    e.g.: if you want to request an ID in a jsonb field, you would do something like:

    -- using JSON native operator, clean
select p.* from post p where p.content ->> 'id' = <ID>;
select p.* from post p where p.content ->> 'id' like '%PARTIAL_ID%';

-- using text field, clunky
select p.* from post p where p.content like '%"id": "<ID>"%';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search