JSON_TABLE问题

Question

我有一个这样的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“

有什么想法吗？

Answer 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;