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

Postgresql – comma separated column value to query using like statement with java list as a parameter in jparepository

I am developing job portal similar and I had postgresql table job_seeker with column name skill holds the job seeker skills as comma separated values

table name - job_seeker
columns  -     id   name     skills             
values(data) -1    mahesh   java,spring,mysql
              2    rakesh   html,css,jsp

Used spring boot with rest controller and from UI skills queryparam getting as a list to search job seeker based on multiple skills.

JobSeekerRepository.java

@Query(value="SELECT * FROM job_seeker WHERE skills in (:skills)", nativeQuery = true)
public Page<JobSeeker> getAllJobSeekerProfileBySkills(@Param("skills") List<String> skills, Pageable pageable);

Above query will works only if skills column has a single value but it contains comma seperated values. I need to use like statement for each value in the skills column for the same record.

Example:-
In the above example, if input list of skills has css and spring then query should return two records and if input list has only html then second record should fetch.

Can you please suggest me how to write the query to get work as per requirement.

Tags:

2

Answers


  1. 0

    You could use recursive cte to transpose your skills list into rows and then join cte to your table selecting just persons having any skill from list of skills provided (asked for):

    Create Table job_seeker (ID Int, NAME Varchar(32), SKILLS Text);
--    S a m p l e    D a t a :
Insert Into job_seeker Values
  (1, 'mahesh', 'java,spring,mysql'),
  (2, 'rakesh', 'html,css,jsp'),
  (3, 'jane', 'sql'), 
  (4, 'john', Null);

    … here is recursive cte named skills …

    WITH
    RECURSIVE skills AS
        ( Select ID as ID, 
                 1 as SKILL_NO,
                 Case When Position(',' In SKILLS) = 0 Then SKILLS 
                 Else SubStr(SKILLS, 1, Position(',' In SKILLS) - 1) End as SKILL,
              --
                 Length(SKILLS) - Length(Replace(SKILLS, ',', '')) + 1 as TOTAL_ELEMENTS, 
              --
                 Case When Position(',' In SKILLS) = 0 Then Null 
                 Else SubStr(SKILLS, Position(',' In SKILLS) + 1) End as REMAININGS
          From   job_seeker
          Where  SKILLS Is Not Null
         UNION ALL
          Select ID, 
                 SKILL_NO + 1,
                 Case When Position(',' In REMAININGS) = 0 Then REMAININGS 
                 Else SubStr(REMAININGS, 1, Position(',' In REMAININGS) - 1) End,
              --
                 TOTAL_ELEMENTS, 
              --
                 Case When Position(',' In REMAININGS) = 0 Then Null 
                 Else SubStr(REMAININGS, Position(',' In REMAININGS) + 1) End
          From   skills
          Where  REMAININGS Is Not Null
        )

    … next is just testing the cte resultset …

    Select ID, SKILL_NO, SKILL
From skills 
Order By ID, SKILL_NO;
/*    R e s u l t : 
id  skill_no    skill
--  ----------  ----------
 1           1  java
 1           2  spring
 1           3  mysql
 2           1  html
 2           2  css
 2           3  jsp
 3           1  sql       */

    This is the main Select that combines your original table data with recursive cte skills data from the test above…

    --    M a i n    S Q L :
Select      Distinct js.*
From        job_seeker js
Inner Join  skills s ON(s.ID = js.ID And Position(',' || s.SKILL || ',' In ',' || js.SKILLS || ',') > 0 )
Where       s.SKILL In('spring', 'css')
Order By    js.ID;

    … result for (‘spring’, ‘css’) – you could ask for any list of skills …

    /*      R e s u l t :
id  name      skills
--  --------  ------------------------------
 1  mahesh    java,spring,mysql
 2  rakesh    html,css,jsp                   */
    Login or Signup to reply.
  2. 0

    You may convert both the search and target comma separated values into arrays using string_to_array and then check if these arrays overlap using the && operator.

    select * from job_seeker
where string_to_array(skills, ',') && 
      string_to_array(:skills, ',');

    However as already noted this is not a good data design. Having skills as text[] or having a normalized data design (the best choice) would be far better.
    See demo.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search