select t.ce_data

from #tmpTable t

cross apply openjson(t.ce_data) with (applicationSubmittedDate date, applicationType varchar(50)) c

where c.applicationSubmittedDate='2021-05-17'

and c.applicationType='Personal'

Not Sure whether its right approach or not. But got required results with below query

;WITH promoData(display_plan_id,promo_id,product_id,promogroup,barcode,product_name)

AS

(

SELECT DPA.display_plan_id,

DPA.promo_id,

PH.product_id,

PH.promogroup,

PM.barcode,

PM.product_name

FROM dbo.Display_Product_Allocation DPA

LEFT JOIN dbo.Product_Helper PH ON DPA.promo_id = PH.promo_id

LEFT JOIN dbo.Product_Main PM ON...