How to write Cross Apply OPENJSON in Criteria JPA

Question

I have this entity

@Entity
public class ItemAdditionalInfo extends BaseEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long itemAdditionalInfoId;

- - other columns 

@Column(name="events",columnDefinition="nvarchar")
@Convert(converter = EventConverterJson.class)
private List<EventInfo> events;

The EventInfo JSON column would look like:

[
    {
        "end_date": "2022-09-30",
        "event_name": "BTX Instock Only (Test)",
        "event_status": "Present",
        "start_date": "2022-07-09"
    }
  {
        "end_date": "2022-09-30",
        "event_name": "BTX Instock Only (Test)",
        "event_status": “Upcoming”,
        "start_date": "2022-07-09"
    }
  {
        "end_date": "2022-09-30",
        "event_name": "BTX Instock Only (Test)",
        "event_status": “Past”,
        "start_date": "2022-07-09"
    }
]

Here is the query I want to run:

select * FROM
( 
SELECT t.item_nbr iNbr,t.created_by createdBy,t.vendor_nbr_full vNbrFull,A.event_status eStatus,A.eventName eName
FROM [Allocation].[item_additional_info] t
CROSS APPLY OPENJSON(t.events) 
WITH
(
    endDate varchar(1000) '$.end_date',
    eventname  varchar(1000) '$.event_name', 
    event_status VARCHAR(1000) '$.event_status',
    startDate varchar(1000) '$.start_date'
) A
where t.item_nbr In (980329651,980397170,980329606,72773,980389846,385177)
) tJ where tJ.eStatus not in ('Past');

I want to implement in Criteria JPA (Not the Hibernate one) ? We are using MS-SQL.

I came accross how to convert JSON_Value function as shown bellow:

public class HibernateInlineExpression extends LiteralExpression<String> {

    public HibernateInlineExpression(CriteriaBuilder criteriaBuilder, String literal) {
        super((CriteriaBuilderImpl) criteriaBuilder, literal);
    }

    @Override
    public String render(RenderingContext renderingContext) {
        return getLiteral();
    }
}

Here is the link: http://www.istvandevai.com/2018/02/using-oracle-json-functions-via-jpa-and.html

But not sure how to do it for CROSS APPLY OPENJSON

How to write Cross Apply OPENJSON in Criteria JPA

0 Answers0