☰

JSON_TABLE问题

tag-icon • tag-icon mysql json
JSON_TABLE问题

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

相关内容