QueryUtils FIELD_ALIAS_PATTERN not able to complex field aliases in native query [DATAJPA-1815]

[spring-projects-issues]

Nihar opened DATAJPA-1815 and commented

FIELD_ALIAS_PATTERN in QueryUtils.java has pattern value 

\s+[^\s\(\)]+\s+[as|AS]+\s+(([\w\.]+))

to match the field aliases but this regex is not able to match against complex queries like

with missed_ops as (
    select ls.lead_id,
           ls.lot_sale_id,
           ls.bid_dtls,
           JSON_UNQUOTE(REPLACE(JSON_SEARCH(ls.bid_dtls, 'one', '100003', NULL, '$.bids[*].companyId'), '.companyId',
                                '.bidAmount')) as bidAmtPath
    from lot_sales ls
    WHERE JSON_SEARCH(ls.bid_dtls, 'one', '100003', NULL, '$.bids[*].companyId') is not null
      and ls.mbr_cmp_id != 100003
)
select l.make_cd                                                as makeCode,
       l.model_cd                                               as modelCode,
       l.year_of_manuf                                          as yearOfManufacture,
       l.lead_id                                                as leadId,
       l.odo_dtls                                               as odometer,
       l.title_cd                                               as titleCode,
       l.odo_metric                                             as odometerUnit,
       l.eng_trans                                              as engineAndTransmission,
       l.run_cond_cd                                            as runConditionCode,
       l.dmg_cd                                                 as damageCode,
       lpl.city                                                 as city,
       lpl.state_cd                                             as stateCode,
       l.crt_dt                                                 as createdDate,
       missed_ops.bid_dtls                                      as bidDetails,
       JSON_EXTRACT(missed_ops.bid_dtls, missed_ops.bidAmtPath) as yourBid
from missed_ops
         join lots l on l.lot_sale_id = missed_ops.lot_sale_id
         join lot_pickup_location lpl on l.pickup_location_id = lpl.pickup_location_id
    and l.crt_dt > DATE_ADD(Now(), INTERVAL - 30 DAY)
    and l.status = 'A'
order by createdDate desc;

where the Set returned by getFieldAliases Method is missing the item "yourBid". Using Annotated Query with nativeQuery=true

 

---

Affects: 2.4 GA (2020.0.0)

[gregturn]

This situation appears too complicated for QueryUtils to handle, and we aren't likely to approve a change there that would meet your needs. Your best best moving forward may be to implement a custom implementation. Check out https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.custom-implementations for more details on how to hook such a solution into your repository.