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

Postgresql – JPA repository query that returns all rows with a nth-to-nth relation

I have a table that looks like this:

id A B
1 A1 B1
2 A1 B2
3 A2 B2
4 A2 B3

Then I have a list of items for which I want to find the corresponding database rows. For instance, i might have (in the form of JSON)

[
 {
  "A": "A1",
  "B": "B4",
 },
 {
  "A": "A1",
  "B": "B2",
 }
]

This should now return the rows with Id 2. These items for which I want to query the database can be thousands.

Naively, I came up with

findAllByAinAndBIn(List<A> aValues, List<B> bValues)

but that doesn’t work since this doesn’t find the rows with a nth element to nth element comparison. What should i do instead?

Tags:

2

Answers


  1. 0

    The simplest approach would be to create a concatenated list of ids and then compare them against the database using a JPQL query.

    1. Create a concatenated list of the ids

     List<String> matches = jsonArrayList.parallelStream().map(e -> e.getA() + ":" + movie.getB()).toList();

    2. Create a JPQL query to compare the concatenated ids against the database

    @Query("select m from Entity m where CONCAT(m.a, ':', m.b) IN (:matches)")
List<Entity> findAllMatchingRows(@Param("matches") List<String> matches);
    Login or Signup to reply.
  2. 0

    The following query demonstrates an approach using a JSON array of search terms:

    WITH
  entity(id, a, b) AS (
    VALUES
      (1, 'A1', 'B1'),
      (2, 'A1', 'B2'),
      (3, 'A2', 'B2'),
      (4, 'A2', 'B3')
  )
SELECT
  entity.*
FROM
  entity JOIN JSONB_ARRAY_ELEMENTS('[{"A": "A1", "B": "B4"}, {"A": "A1", "B": "B2"}]'::JSONB) j(criteria)
  ON (entity.a, entity.b) = (j.criteria ->> 'A', j.criteria ->> 'B');

    The following is an example using a JPA native query:

    @Query(value = "SELECT entity.* FROM entity JOIN JSONB_ARRAY_ELEMENTS(:criteria) j(criteria) ON (entity.a, entity.b) = (j.criteria ->> 'A', j.criteria ->> 'B')", nativeQuery = true)
Entity findEntityByAAndB(@Param("criteria") String "[{"A": "A1", "B": "B4"}, {"A": "A1", "B": "B2"}]");
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search