Jak pobrać dane z JSON w oracle przy JSON_TABLE

0

Witajcie mam taki fragment JSON'a

"principalsAndManagement":{
"currentPrincipal":[
{
"principalName":{
"fullName":"xxx xxx"
},
"politicallyExposedPersons":[
{
"primaryName":"Andrzej Jankowski",
"entityMatchScore":92,
"supplierRiskScore":90,
"personDetail":{
"entityTypeText":"Person",
"eventDetail":[
{
"eventTypeText":"PEP",
"eventSubTypeText":"ASC",
"eventText":[
"On List [PEP Connect - Poland]"
],
"eventDate":"2017-06-21"
}
],
"name":{
"primaryName":"xxxxxx",
"knownByName":[ ];
},

Próbuję wyciągnąć dane takim zapytaniem:

SELECT DISTINCT  bmb_id,
fullNameP,
eventTypeText,
listagg('type: ' || eventTypeText || ' subtype: ' || eventSubTypeText || ' date: ' ||eventDate || ' text: ' || eventText,' | ') WITHIN GROUP( ORDER BY eventTypeText) OVER(PARTITION BY bmb_id,fullNameP,pepPrimaryName,eventDate) AS eventDetail,
pepPrimaryName,
entityMatchScore,
supplierRiskScore,
knownByName0,
listagg (parameterDescription || ': ' || parameterValue,' | ') WITHIN GROUP( ORDER BY parameterDescription) OVER(PARTITION BY bmb_id, fullNameP,pepPrimaryName) AS other_parameters
FROM tabela t
       , JSON_TABLE (T.bmb_json,
                    '$' COLUMNS(
                    NESTED PATH '$.principalsAndManagement.currentPrincipal[*]'
                          COLUMNS("fullNameP" VARCHAR2(500) PATH '$.principalName.fullName' NULL ON ERROR,
                          NESTED PATH '$.politicallyExposedPersons[*]'
                                  COLUMNS("pepPrimaryName" VARCHAR2(500)  PATH '$.primaryName' NULL ON ERROR,
                                          "entityMatchScore" VARCHAR2(500) path '$.entityMatchScore' NULL ON ERROR,
                                          "supplierRiskScore" VARCHAR2( 500) path '$.supplierRiskScore' NULL ON ERROR,
                                          "knownByName0" varchar2(500) path '$.personDetail.entityTypeText' ERROR ON ERROR,
                                         -- "knownByName0" VARCHAR2(500) path '$.personDetail.name.primaryName' ERROR ON ERROR,
                                 NESTED PATH '$.personDetail.eventDetail[*]' COLUMNS(
                                          "eventTypeText" VARCHAR2(500) PATH '$.eventTypeText' NULL ON ERROR
                                          ,"eventSubTypeText" VARCHAR2(500) PATH '$.eventSubTypeText' NULL ON ERROR
                                          ,"eventDate"  VARCHAR2(500) PATH '$.eventDate' NULL ON ERROR
                                          ,"eventText"  VARCHAR2(500)  PATH '$.eventText[0]' NULL ON ERROR
                                        ) ,   
                                 NESTED PATH '$.personDetail.nonspecificParameterDetail[*]' COLUMNS(
                                          "parameterDescription"  VARCHAR2(500) PATH '$.parameterDescription' NULL ON ERROR
                                          ,"parameterValue"  VARCHAR2(500)  PATH '$.parameterValue' NULL ON ERROR)                                            
                                 ))))
 where bmb_id = xxx

I to zapytanie działa. Problem pojawia się gdy to: "knownByName0" varchar2(500) path '$.personDetail.entityTypeText' ERROR ON ERROR,
zamienię na to:
"knownByName0" VARCHAR2(500) path '$.personDetail.name.primaryName' ERROR ON ERROR

Dostaję wyjątek ORA-40462 ... jakieś pomysły bo utknąłem?

1

Myslałem ze problem jest JSON_TABLE, ale jest poprawne po takiej zmianie działa;

SELECT
"fullNameP",
"eventTypeText",
listagg('type: ' || "eventTypeText" || ' subtype: ' || "eventSubTypeText" || ' date: ' || "eventDate" || ' text: ' || "eventText",' | ') WITHIN GROUP( ORDER BY "eventTypeText") OVER(PARTITION BY bmb_id,"fullNameP","pepPrimaryName","eventDate") AS eventDetail,
"pepPrimaryName",
"entityMatchScore",
"supplierRiskScore",
"knownByName0",
listagg ("parameterDescription" || ': ' || "parameterValue",' | ') WITHIN GROUP( ORDER BY "parameterDescription") OVER(PARTITION BY bmb_id, "fullNameP","pepPrimaryName") AS other_parameters

FROM dt,
JSON_TABLE(pole_j, 
        '$' COLUMNS(
        NESTED PATH '$.principalsAndManagement.currentPrincipal[*]'
                COLUMNS("fullNameP" VARCHAR2(500) PATH '$.principalName.fullName' NULL ON ERROR,
                NESTED PATH '$.politicallyExposedPersons[*]'
                        COLUMNS("pepPrimaryName" VARCHAR2(500)  PATH '$.primaryName' NULL ON ERROR,
                                "entityMatchScore" VARCHAR2(500) path '$.entityMatchScore' NULL ON ERROR,
                                "supplierRiskScore" VARCHAR2( 500) path '$.supplierRiskScore' NULL ON ERROR,
                                "knownByName1" varchar2(500) path '$.personDetail.entityTypeText' ERROR ON ERROR,
                                "knownByName0" VARCHAR2(500) path '$.personDetail.name.primaryName' ERROR ON ERROR,
         NESTED PATH '$.personDetail.eventDetail[*]' COLUMNS(
                                "eventTypeText" VARCHAR2(500) PATH '$.eventTypeText' NULL ON ERROR
                                ,"eventSubTypeText" VARCHAR2(500) PATH '$.eventSubTypeText' NULL ON ERROR
                                ,"eventDate"  VARCHAR2(500) PATH '$.eventDate' NULL ON ERROR
                                ,"eventText"  VARCHAR2(500)  PATH '$.eventText[0]' NULL ON ERROR
                            ) ,   
                        NESTED PATH '$.personDetail.nonspecificParameterDetail[*]' COLUMNS(
                                "parameterDescription"  VARCHAR2(500) PATH '$.parameterDescription' NULL ON ERROR
                                ,"parameterValue"  VARCHAR2(500)  PATH '$.parameterValue' NULL ON ERROR)                                            
                        ))))

AS jt;

https://dbfiddle.uk/?rdbms=or[...]f3e2c3a6d948a7ab4690cae0bfeb6

1 użytkowników online, w tym zalogowanych: 0, gości: 1, botów: 0