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?