Itgensql144: The JSON has an invalid format

Questions
,
1

It seems that ABN and Exact are now rolling out on a PSD2 banklink type (2.0) (JSON format) instead of the old one 1.0 (RABOBANK format).

We are parsing the bank files with Invantive with such statement:

use  xxx@eol 

Create or replace table temp@inmemorystorage 
as
select *
from   exactonlinerest..documentsBulk@eol dct 
join   exactonlinerest..documentattachmentfilesBulk@eol dae
on     dct.id = dae.document
where  dct.type = 40 
AND    dct.Created > dateadd('day','-5',sysdateutc) 
AND    dct.subject like '%11111111%' -- account number in the subject
		
select * 
from   temp@InMemoryStorage 
ORDER 
by     Created DESC

local export documents in AttachmentFromUrl to "c:\temp" filename automatic

Then using the @Bankfile RABO driver we parse the content and process the data.
We do this rather querying the Bankentries@eol to get the real source file from the bank. Inside Exact Online Bankentries could be amended by human action (add, modify, suppress lines).

But since we have a PSD2 2.0 bank link with ABN with one of our banks, we now get a JSON file from ABN that is stored in Exact Online, thus downloaded by the statement above. We try to parse with jsontable like this:

select *
from   jsontable
       ( '[*]' 
         passing file 'c:\temp\1.bin'
         columns c varchar2 path 'BankAccountId'
       )

but we get an itgensql144 error:

The JSON has an invalid format.
Bad JSON escape sequence: . Path ‘Transactions[0].RawData’, line 1, position 536.

Indeed the json contains escape sequences in the Rawdata node:

Capture d’écran 2023-03-10 à 19.03.52
Capture d’écran 2023-03-10 à 19.03.521486×913 137 KB

Testing the json file on other json validator seems fine.

2

Please note that the PSD2 bank statements are in UTF16 encoding instead of UTF-8. That might explain the error. Can you try logic such as:

select ...
from   transactionlinesbulk@${EOL_ROTATOR} tle
on     tle.accountcode   = ive.eol_invoice_to_code
and    tle.amountdc      != 0
and    tle.journalcode   = '20'
and    tle.invoicenumber is not null
and    tle.glaccount 
       in 
       ( select /*+ low_cost */ id 
         from   exactonlinerest..glaccountsincremental@${EOL_ROTATOR} 
         where  code ='1100' 
       )
join   ExactOnlineREST..DocumentAttachmentFiles@${EOL_ROTATOR} dae
on     dae.division = tle.division
and    dae.document = tle.document
and    dae.filename like 'Statement %.txt'
join   AttachmentByUrl@${EOL_ROTATOR}(dae.url || '&Download=1') dct
join   jsontable
       ( 'Transactions[*]'
         passing blob_to_text(dct.DocumentContents, 'Unicode')
         columns TransactionDateTimeUtc            datetime path 'TransactionDateTimeUtc'
         ,       Description                       varchar2 path 'Description'
         ,       CounterPartyName                  varchar2 path 'CounterParty.Name'
         ,       CounterPartyAccountIdentification varchar2 path 'CounterParty.BankEntity.AccountIdentification'
       ) jte

The use of blob_to_text(dct.DocumentContents, 'Unicode') ensures the conversion to text.

Please upgrade to a more recent 22.0 or BETA 22.1 version when the blob_to_text does not work.

3

I confirm the blob_to_text approach works well.

Below my first staging script to retrieve and parse bank file provided by Exact in PSD2 format (json)

select dct.division
,      fle.* 
from   exactonlinerest..documents@eol dct 
join   exactonlinerest..documentattachmentfiles@eol dae on dct.id = dae.document
join   AttachmentByUrl@eol (dae.url || '&Download=1') dcta
join   jsontable
       ( 'Transactions.[*]'
        passing blob_to_text(dcta.DocumentContents, 'Unicode')
        columns
           --bankAccountID varchar2 path '::BankAccountId'
          transactiondate varchar2 path '.TransactionDate'
          ,Description varchar2 path 'Description'
          --,rawData varchar2 path 'RawData'
          ,ownAccount varchar2 path 'Owner.BankEntity.AccountIdentification'
          ,TransactionNumber varchar2 path 'TransactionNumber'
          --,TransactionIdentifiers varchar2 path 'TransactionIdentifiers'
          ,Type varchar2 path 'Type'
          ,amount varchar2 path 'Amounts.[*].Value'
          ,CounterpartyName varchar2 path 'CounterParty.Name'
          ,CounterpartyAccount varchar2 path 'CounterParty.BankEntity.AccountIdentification'
      ) fle
where  dct.type = 40 
AND    dct.Created > dateadd('day','-10',sysdateutc) 
AND    dct.Division = your_division
1 like
4

I would suggest to add a supplementary filter to only get PSD2 json files from Exact by using the SendMethod filter
I found out that this SendMethod gets:

  • 0 when in Classic Banklink configuration
  • 2 when in PSD2 banklink configuration

the script becomes

select dct.division
,      fle.* 
from   exactonlinerest..documents@eol dct 
join   exactonlinerest..documentattachmentfiles@eol dae on dct.id = dae.document
join   AttachmentByUrl@eol (dae.url || '&Download=1') dcta
join   jsontable
       ( 'Transactions.[*]'
        passing blob_to_text(dcta.DocumentContents, 'Unicode')
        columns
           --bankAccountID varchar2 path '::BankAccountId'
          transactiondate varchar2 path '.TransactionDate'
          ,Description varchar2 path 'Description'
          --,rawData varchar2 path 'RawData'
          ,ownAccount varchar2 path 'Owner.BankEntity.AccountIdentification'
          ,TransactionNumber varchar2 path 'TransactionNumber'
          --,TransactionIdentifiers varchar2 path 'TransactionIdentifiers'
          ,Type varchar2 path 'Type'
          ,amount varchar2 path 'Amounts.[*].Value'
          ,CounterpartyName varchar2 path 'CounterParty.Name'
          ,CounterpartyAccount varchar2 path 'CounterParty.BankEntity.AccountIdentification'
      ) fle
where  dct.type = 40 
AND    dct.Created > dateadd('day','-10',sysdateutc) 
AND dct.SendMethod = 2 -- get only PSD2 json files
AND    dct.Division = your_division
5

Thanks for the tip on SendMethod for PSD2!

6

Pay attention to remarks on the SequenceNumber described here : join-attachmentbyurl-eol-and-join-jsontables-creates-duplicate-entries

the good statement is now:

select distinct dct.division, fle.* from  exactonlinerest..documents@eol dct 
join   exactonlinerest..documentattachmentfiles@eol dae on dct.id = dae.document
join AttachmentByUrl@eol (dae.url || '&Download=1') dcta 
join jsontable
      ('Transactions.[*]'
      passing blob_to_text(dcta.DocumentContents, 'Unicode')
      columns
           --bankAccountID varchar2 path '::BankAccountId'
           transactiondate datetime path '.TransactionDate'
          ,Description varchar2 path 'Description'
          --,rawData varchar2 path 'RawData'
          ,ownAccount varchar2 path 'Owner.BankEntity.AccountIdentification'
          --,SequenceNumber varchar2 path 'SequenceNumber' -> removed since the file is downloaded 4 times/day by Eol, the same transaction can be present in the 4 json files but with different Sequence number.
          ,TransactionNumber varchar2 path 'TransactionNumber'
          --,TransactionIdentifiers varchar2 path 'TransactionIdentifiers'
          ,Type varchar2 path 'Type'
          ,amount varchar2 path 'Amounts.[*].Value'
          ,CounterpartyName varchar2 path 'CounterParty.Name'
          ,CounterpartyAccount varchar2 path 'CounterParty.BankEntity.AccountIdentification'
      ) fle
where dct.type = 40 AND dct.Created > dateadd('day','-10',sysdateutc) AND dct.SendMethod = 2