次の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;