다음과 같은 JSON 구조가 있습니다 ...
{
"market_order_line_details__c":[
{
"PriceItem__r.Catalog_Item__r.Parent_Catalog_Item__r.Parent_Catalog_Item__r.Calendar__r":{
"CalendarTypeID__c":4,
"CalendarTypeName__c":"Example"
},
"PriceItem__r.Catalog_Item__r.Parent_Catalog_Item__r":{
"EstimatedDailyAvails__c":"YYYNNYN",
"Catalog_Display_Name_2__c":"Program Shortname"
},
"PriceItem__r.Catalog_Item__r":{
"ProductionID_Formula__c":123,
"FormatID__c":456,
"Catalog_Display_Name_2__c":"Format Shortname",
"Channel_ID_Formula__c":472,
"ProgramID_Formula__c":351,
"DaypartID_Formula__c":901,
"Customized__c":"Y",
"Media_Types__c":"PayTV"
},
"PriceItem__r.Price_List__r":{
"External_ID__c":"Example",
"Currency__c":"Example",
"Version__c":"1"
},
"PriceItem__r.":{
"Short_Name__c":"Example",
"isBonificado__c":"Y",
"isBonificadoExtra__c":"Y"
}
}
]
}
이 코드는 잘 작동합니다...
SELECT PriceItem__r.*
FROM t3,
JSON_TABLE(json_col, '$.market_order__c.market_order_line__c[*].market_order_line_details__c[*].PriceItem__r' COLUMNS (
Short_Name__c Char(20) PATH '$.Short_Name__c',
isBonificado__c Char(20) PATH '$.isBonificado__c',
isBonificadoExtra__c Char(20) PATH '$.isBonificadoExtra__c')
) PriceItem__r;
...
그런데 이건 살 수 없어요... "PriceItem__r.Price_List__r"
어떤 아이디어가 있나요?
답변1
해결되었습니다! 복잡한 프로젝트에 큰따옴표를 붙이는 것만으로도...
SELECT Price_List__r.*
FROM t3,
JSON_TABLE(json_col, '$.market_order__c.market_order_line__c[*].market_order_line_details__c[*]."PriceItem__r.Price_List__r"' COLUMNS (
External_ID__c Char(20) PATH '$.External_ID__c',
Currency__c Char(20) PATH '$.Currency__c',
Version__c Char(20) PATH '$.Version__c')
) Price_List__r;