JSON_TABLE starting with an array

[paolinosal]

Hi all,

I need some help to parse a json than start with square brackets, something like this:

[
{
"LEI": {
"$": "894800UN8CML2Q7Y4V11"
},
"Entity": {
"LegalName": {
"$": "PAOLINO PAPERINO"
},
"LegalAddress": {
"@xml:lang": "",
"FirstAddressLine": {
"$": "VIA ROMA 16"
},
"City": {
"$": "TORINO"
},
"Region": {
"$": "IT-21"
},
"Country": {
"$": "IT"
},
"PostalCode": {
"$": "10100"
}
},
"HeadquartersAddress": {
"@xml:lang": "",
"FirstAddressLine": {
"$": "VIA ROMA 16"
},
"City": {
"$": "TORINO"
},
"Region": {
"$": "IT-21"
},
"Country": {
"$": "IT"
},
"PostalCode": {
"$": "10100"
}
},
"RegistrationAuthority": {
"RegistrationAuthorityID": {
"$": "RA000407"
},
"RegistrationAuthorityEntityID": {
"$": "TO-1070547"
}
},
"LegalJurisdiction": {
"$": "IT"
},
"LegalForm": {
"EntityLegalFormCode": {
"$": "8888"
},
"OtherLegalForm": {
"$": "srl"
}
},
"EntityStatus": {
"$": "ACTIVE"
}
},
"Registration": {
"InitialRegistrationDate": {
"$": "2018-06-19T09:24:09+02:00"
},
"LastUpdateDate": {
"$": "2019-06-20T02:30:09+02:00"
},
"RegistrationStatus": {
"$": "LAPSED"
},
"NextRenewalDate": {
"$": "2019-06-19T09:24:10+02:00"
},
"ManagingLOU": {
"$": "529900F6BNUR3RJ2WH29"
},
"ValidationSources": {
"$": "FULLY_CORROBORATED"
},
"ValidationAuthority": {
"ValidationAuthorityID": {
"$": "RA000407"
},
"ValidationAuthorityEntityID": {
"$": "TO-1070547"
}
}
}
}
]

I have it in a clob columns, and I try to parse it with json_table:

Select LEI, outputclob
  From Uhttpbch0f, 
        Json_Table (OutPutClob format json, 'lax $' 
            Columns (Scadenza Varchar(30) Path 'lax $.LEI."$"')) As Lei
  Where Rowid = 9826;

If I remove the first square brackets it works, but how can I do the parse with the square brackets?
Many thanks.
Bye

[paolinosal]

There's a mistake on the sql:

the is the corret sql, but it down't work too:

 
 Select LEI, outputclob   From Uhttpbch0f,          Json_Table (OutPutClob format json, 'lax $'              Columns (Lei Varchar(30) Path 'lax $.LEI."$"')) As Lei   Where Rowid = 9826;

[B.Hauser]

Unfortunately JSON_TABLE cannot schred a JSON document starting with an array...
But there is a work arround:
Just embedd the JSON document in an Object and voila you can shred it.

select Lei.*, OutputClob
from Uhttpbch0f,
     JSON_TABLE('{"root": ' concat OutPutClob concat '}' Format JSON,
                '$.root[*]'
                Columns (Scadenza Varchar(256) Path 'lax $.LEI."$"')) As Lei;

[paolinosal]

Hi Birgitta,

many thanks for your help, but I'm still have a problem,

If I update my column outputclob with the value: outputclob = '{"root": ' concat OutPutClob concat '}'
it works.

But if I try as you said me I'm getting the error:

Bad ByteLen passed to GETSPACE: 00016773504.

My outputclob column is about 2GB.

Any other workaround?
Many thanks.

[B.Hauser]

Put a RTRIM around OutPutClob

Birgitta