Spring Boot + Postgres: Native Query/Projection Not Populating Array Attribute from Function/View

I’m working on a Spring Boot service with a Postgres database. I want to fetch data using a native query that leverages a view and a function, and map the results to an entity with a projected array/collection attribute.

However, in some cases (of code changes), the attribute (displayNameGermanOverwrites) is not being populated, even though the direct SQL call returns the expected array.

I provided:

• the new code
• example of results
• the old code for reference

I tried a lot of things... I'm now i bit lost in all that i tried. What could be the issue here ?

Edit: If i change the return type of the repo method then the correct array is found. So the problem would only be about populating the attribute {"id" : 359, "name" : "Remark", "included_in_model" : "True", "data_type" : "String", "searchable" : "False", "sort_index" : 1, "sub_category_id" : 19, "company_id" : 5, "display_name_german" : "test3", "display_name_english" : "Pre Test Remark", "display_name_german_overwrites" : [29111,29116], "display_name_english_overwrites" : null, "value_list" : ""}

Entity:

@Data
@Entity
@Table(name = "datamodelling_attribute")
public class Attribute {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @Column(name = "display_name_german")
    private String displayNameGerman;

    @Column(name = "display_name_german_overwrites", columnDefinition = "bigint[]")
    @JdbcTypeCode(SqlTypes.ARRAY)
    private List<Long> displayNameGermanOverwrites; // Also tried Long[]
    // ...
}

Repository:

@Query(value = """
    WITH overwrites AS (
        SELECT 
            a.id as attr_id,
            (
                SELECT array_agg(CAST(t.id AS bigint))
                FROM get_attribute_overwrites_table(
                    CAST(a.id AS integer),
                    'display_name_german',
                    a.display_name_german,
                    :companyName,
                    :componentTypeName,
                    :curveName
                ) t
            ) as display_name_german_overwrites
        FROM datamodelling_attribute a
        WHERE a.sub_category_id IN (
            SELECT id FROM datamodelling_subcategory WHERE name = :subCategoryName
        )
    )
    SELECT 
        a.id,
        a.name,
        o.display_name_german_overwrites
    FROM datamodelling_attribute a
    JOIN overwrites o ON a.id = o.attr_id
    ORDER BY a.id
    """, nativeQuery = true)
List<Attribute> findAttributesBySubCategory(
    @Param("subCategoryName") String subCategoryName,
    @Param("companyName") String companyName,
    @Param("componentTypeName") String componentTypeName,
    @Param("curveName") String curveName
);

View:

CREATE OR REPLACE VIEW view_attribute_overwrites AS
SELECT a.id AS attribute_id, ov.id AS overwrite_id, c.company_name, t.name AS component_type_name, cu.name AS curve_name
FROM datamodelling_attribute a
JOIN datamodelling_overwrite ov ON a.id = ov.object_id
JOIN datamodelling_company c ON ov.company_id = c.id
JOIN datamodelling_componenttype t ON ov.component_type_id = t.id
JOIN datamodelling_curve cu ON ov.curve_id = cu.id
WHERE ov.content_type_id = 1
ORDER BY c.id, t.id, cu.id;

Function

CREATE OR REPLACE FUNCTION public.get_attribute_overwrites_table(
    attr_id integer,
    attr_field_name text,
    attr_original_value text,
    scope_company_name text,
    scope_component_name text,
    scope_curve_name text
)
RETURNS TABLE (id bigint)
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN QUERY
SELECT ov.id
FROM datamodelling_overwrite ov
JOIN view_attribute_overwrites v ON ov.id = v.overwrite_id
WHERE ov.object_id = attr_id
  AND ov.field_name = attr_field_name
  AND v.company_name = scope_company_name
  AND (
    v.component_type_name = scope_component_name
    OR v.component_type_name = 'All'
  )
  AND v.curve_name = scope_curve_name
ORDER BY ov.id;
END;
$function$;

Direct call gives the expected result:

docker exec db-1 psql -U postgres -d postgres -c "SELECT * FROM get_attribute_overwrites_table(359, 'display_name_german', 'SOME_GERMAN_NAME', 'COMPANY_A', 'COMPONENT_TYPE_A', 'All')" | cat
  id   
-------
 29111
 29116
(2 rows)

docker exec db-1 psql -U postgres -d postgres -c "WITH overwrites AS (SELECT a.id as attr_id, (SELECT array_agg(CAST(t.id AS bigint)) FROM get_attribute_overwrites_table(CAST(a.id AS integer), 'display_name_german', a.display_name_german, 'COMPANY_A', 'COMPONENT_TYPE_A', 'All') t) as display_name_german_overwrites FROM datamodelling_attribute a WHERE a.id = 359) SELECT CAST(o.display_name_german_overwrites AS bigint[]) as display_name_german_overwrites, array_length(o.display_name_german_overwrites, 1) FROM overwrites o;" | cat
 display_name_german_overwrites | array_length 
--------------------------------+--------------
 {29111,29116}                  |            2
(1 row)

docker exec db-1 psql -U postgres -d postgres -c "WITH overwrites AS (SELECT a.id as attr_id, (SELECT array_agg(CAST(t.id AS bigint)) FROM get_attribute_overwrites_table(CAST(a.id AS integer), 'display_name_german', a.display_name_german, 'COMPANY_A', 'COMPONENT_TYPE_A', 'All') t) as display_name_german_overwrites, (SELECT array_agg(CAST(t.id AS bigint)) FROM get_attribute_overwrites_table(CAST(a.id AS integer), 'display_name_english', a.display_name_english, 'COMPANY_A', 'COMPONENT_TYPE_A', 'All') t) as display_name_english_overwrites FROM datamodelling_attribute a WHERE a.id = 359) SELECT a.id, a.name, a.included_in_model, a.data_type, a.searchable, CAST(COALESCE(NULLIF(a.sort_index, ''), '0') AS integer) as sort_index, a.sub_category_id, a.company_id, COALESCE((SELECT o.new_value FROM datamodelling_overwrite o WHERE o.id = (SELECT o.display_name_german_overwrites[array_length(o.display_name_german_overwrites, 1)] FROM overwrites o WHERE o.attr_id = a.id)), a.display_name_german) as display_name_german, a.display_name_english, CAST(o.display_name_german_overwrites AS bigint[]) as display_name_german_overwrites, CAST(o.display_name_english_overwrites AS bigint[]) as display_name_english_overwrites, a.value_list FROM datamodelling_attribute a JOIN overwrites o ON a.id = o.attr_id WHERE a.id = 359;" | cat
 id  |  name  | included_in_model | data_type | searchable | sort_index | sub_category_id | company_id | display_name_german | display_name_english | display_name_german_overwrites | display_name_english_overwrites | value_list 
-----+--------+-------------------+-----------+------------+------------+-----------------+------------+---------------------+----------------------+--------------------------------+---------------------------------+------------
 359 | Remark | True              | String    | False      |          1 |              19 |          5 | test3               | Pre Test Remark      | {29111,29116}                  |                                 | 
(1 row)

For reference the old code was working and populating the attribute (projection) display_name_german_overwrites fine :

@Data
@Entity
@Table(name = "datamodelling_attribute")
public class Attribute {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private String name;

    @Column(name = "display_name_german_overwrites", columnDefinition = "bigint[]")
    @JdbcTypeCode(SqlTypes.ARRAY)
    private Long[] displayNameGermanOverwrites;
...
} 

@Repository
public interface AttributeRepository extends JpaRepository<Attribute, Long> {
    
    @Query(value = """
            WITH overwrites AS (
                SELECT 
                    a.id as attr_id,
                    (
                        SELECT array_agg(CAST(t.id AS bigint))
                        FROM get_attribute_overwrites_table(
                            CAST(a.id AS integer),
                            'display_name_german',
                            a.display_name_german,
                            :companyName,
                            :componentTypeName,
                            :curveName
                        ) t
                    ) as display_name_german_overwrites,

                FROM datamodelling_attribute a
                WHERE a.sub_category_id IN (
                    SELECT id FROM datamodelling_subcategory WHERE name = :subCategoryName
                )
            )
            SELECT 
                a.id,
                a.name,
                COALESCE(
                    (SELECT o.new_value 
                     FROM datamodelling_overwrite o 
                     WHERE o.id = (SELECT o.display_name_german_overwrites[array_length(o.display_name_german_overwrites, 1)]
                                  FROM overwrites o 
                                  WHERE o.attr_id = a.id)),
                    a.display_name_german
                ) as display_name_german,
            FROM datamodelling_attribute a
            JOIN overwrites o ON a.id = o.attr_id
            ORDER BY a.id
            """, nativeQuery = true)
    List<Attribute> findAttributesBySubCategory(
        @Param("subCategoryName") String subCategoryName,
        @Param("companyName") String companyName,
        @Param("componentTypeName") String componentTypeName,
        @Param("curveName") String curveName
    );

Usage in the service:

    // For each sub-category, fetch its attributes
    for (String subCategoryName : subCategories) {
        List<Attribute> attributes = attributeRepository.findAttributesBySubCategory(
            subCategoryName,
            companyName != null ? companyName : "",
            componentTypeName != null ? componentTypeName : "",
            curveName != null ? curveName : ""
        );

        ...
    }

The old database function:

    CREATE OR REPLACE FUNCTION public.get_attribute_overwrites_table(
        attr_id integer,
        attr_field_name text,
        attr_original_value text,
        scope_company_name text,
        scope_component_name text,
        scope_curve_name text
    )
    RETURNS TABLE(id bigint)
    LANGUAGE plpgsql
    AS $function$
    DECLARE
        found_rows integer;
        scope_company_id bigint;
        scope_component_type_id bigint;
        scope_curve_id bigint;
    BEGIN
        -- Get IDs for the scope parameters if they are not 'all'
        IF scope_company_name != 'all' THEN
            SELECT company.id INTO scope_company_id 
            FROM datamodelling_company company 
            WHERE company.company_name = scope_company_name;
        END IF;
        
        IF scope_component_name != 'all' THEN
            SELECT component_type.id INTO scope_component_type_id 
            FROM datamodelling_componenttype component_type 
            WHERE component_type.name = scope_component_name;
        END IF;
        
        IF scope_curve_name != 'all' THEN
            SELECT curve.id INTO scope_curve_id 
            FROM datamodelling_curve curve 
            WHERE curve.name = scope_curve_name;
        END IF;
        
        -- Perform the query with the hierarchy rule
        CREATE TEMP TABLE temp_results AS
        SELECT
            o.id,
            o.new_value,
            o.company_id,
            o.component_type_id,
            o.curve_id
        FROM datamodelling_overwrite o
        JOIN datamodelling_company company ON o.company_id = company.id
        JOIN datamodelling_componenttype component_type ON o.component_type_id = component_type.id
        JOIN datamodelling_curve curve ON o.curve_id = curve.id
        WHERE
            o.object_id = attr_id AND
            o.field_name = attr_field_name AND
            o.content_type_id = 1
        ORDER BY
            o.company_id,
            o.component_type_id,
            o.curve_id;
    
        -- Get the number of rows from the query
        GET DIAGNOSTICS found_rows = ROW_COUNT;
    
        -- If rows are found, return them from the temporary table
        RETURN QUERY
        SELECT
            temp_results.id
        FROM temp_results;
    
        -- Clean up the temporary table
        DROP TABLE IF EXISTS temp_results;
    END;
    $function$;