How to ignore quotes for nested ARRAY in a PostgreSQL query in a Spring Data @Query

Question

The question may seems stupid but I am stuck on executing that kind of query:

UPDATE user SET productArr = ARRAY[ARRAY[2,2],ARRAY[3,2],ARRAY[1,3]] WHERE id = 1;

within a @Query Spring Data.

I defined my repository method like:

    @Modifying
    @Query(value = """
            UPDATE user
            SET productArr = :productArr WHERE id = :userId
            """, nativeQuery = true)
    void updateProductArr (@Param("productArr") String productArr, @Param("userId") Long userId);

I set String productArr="ARRAY[ARRAY[2,2],ARRAY[3,2],ARRAY[1,3]]" The error is:

org.postgresql.util.PSQLException: ERROR: column "productArr" is of type text[] but expression is of type text

I tried to change the type of productArr from String to String[] or List<String> without success.

I also tried this solution: UPDATE user SET productArr = REPLACE(:productArr,'"','') WHERE id = :userId but same result.

Please note that when I run that query on a database console it works well. Thank you in advance for your help.

score 0 · Accepted Answer · answered Jul 08 '23 at 14:51

When you update your data using this query:

UPDATE user SET productArr = ARRAY[ARRAY[2,2],ARRAY[3,2],ARRAY[1,3]] WHERE id = 1;

Postgres automatically converts you integer array to string array, because as I understand the productArr field is type of text[]

You can filter this field using this correct SQL queries:

select * from users
where productArr = ARRAY[ARRAY['2','2'],ARRAY['3','2'],ARRAY['1','3']]

or

select * from users
where productArr = '{{2,2},{3,2},{1,3}}'::text[]

In your spring project you have two ways do this.

Use array type for input parameters, don't use string type
You can use string type for input parameters, but then you should cast type inside the native query.

For example:

@Modifying
@Query(value = """
        UPDATE user
        SET productArr = (:productArr)::text[] WHERE id = :userId
        """, nativeQuery = true)
void updateProductArr (@Param("productArr") String productArr, @Param("userId") Long userId);

And use:

String productArr = "{{2,2},{3,2},{1,3}}"

I went for the 2nd proposition which seems more readable. What is the best type to use for that strange type in an entity if I want to avoid nativeQuery? A String, or a String[] or List, or a List? — akuma8, Jul 08 '23 at 21:57
Usually used list> or String[][]. For detail information how to map a 2d arrays in Java Hibernate visit this url. https://stackoverflow.com/questions/4099237/how-to-map-a-2-d-matrix-in-java-to-hibernate-jpa - Maybe help you. — Ramin Faracov, Jul 11 '23 at 15:36

score 0 · Answer 2 · answered Jul 08 '23 at 21:19

Your problem is brought on by a type mismatch between the PostgreSQL array type and the productArr argument of your Spring Data query. To fix this, modify the type of the productArr parameter in your method signature to String[] and explicitly define the parameter type using the ::text[] cast in your query. This guarantees for the update query to run effectively.

How to ignore quotes for nested ARRAY in a PostgreSQL query in a Spring Data @Query

2 Answers2