0

I need to un-parse JSON with lateral flatten. But I would like to have 2 values - one for club and one for free delivery, for club I am ok, but I can not get also free delivery This is code I have

select  LPAD(PURCHASE_ID,10, '0')as PURCHASE_ID, DELIVERY_ID, PARTNER_ID,HAS_LABEL_FDEL, f3.value:commonLabels[0]::varchar as CLUB --,ORDER_ID as ue
from (select distinct PURCHASE_ID, DELIVERY_ID, PARTNER_ID,HAS_LABEL_FDEL, PARSE_JSON(PR_TRANSPORT) as PR_TRANSPORT from "DWHM_MALLGROUP"."SRC_ORACLEIWP"."PURCHASE_ANALYSIS_RULES" 
   ) as PAR,
lateral flatten(input => PAR.PR_TRANSPORT) f3
where PR_TRANSPORT is not null 
and f3.value:type='CLUB' ---????or f3.value:type='FREE_DELIVERY'

and here is parse JSON looks like this for free delivery, which i would like to have :

[{"ruleId":244419,"type":"FREE_DELIVERY"},{"ruleId":9942304,"type":"FREE_DELIVERY"}] This is how it looks for club : [{"commonLabels":["CZ_B2B_CLUB"],"ruleId":20228738,"type":"CLUB"}]

So one field for CLUBS, one for Free delivery

saska
  • 73
  • 1
  • 9
  • Please edit the question, add how the input looks, and how the output should look like – Felipe Hoffa Feb 05 '21 at 02:54
  • You mention club, but the data has no mention of club. In to assist with this, I'd need full details of the data and the expected output. – Mike Walton Feb 07 '21 at 18:48
  • Whole format looks like this : - here is data for club [{"commonLabels":["MALL_VIP_CLUB"],"ruleId":1961780,"type":"CLUB"}] , here are data for Free delivery : {"ruleId":244419,"type":"FREE_DELIVERY"},{"ruleId":9942304,"type":"FREE_DELIVERY"}] Expected OUTPUT should be : ID, club - if yes, Free delivery - if yes. Club, or free delivery is not with each order – saska Feb 08 '21 at 08:21

0 Answers0