JSON and XLATE

[Ab Lugtenburg]

Is there anybody who was able to make a xlate with a json input and a vrl or db output ?

The JSON is a file what starts with 2 fields as integer , then a object tuple with a repeating part. als teh repeating fields are string.

I made a json config and the test is ok al the fields repeat

The xlate contains a iterate  on the repeat in the json but the xlate reads the first field (integer) and then nothing more

Anybody experience with this issue ?

Topic

[Jim Kosloskey]

Make sure your basis is correct on the ITERATE Action (you can use the variant test of the JSON input as a validation) and you have the correct ITERATE Type specified in the Action (group, segment, etc.).

If that is correct, make sure you place the ITERATE Variable appropriately in the COPY (or other Action referencing the IB (again the variant test can be of help here as a sanity check).

Lastly, make sure you have an appropriate place for the repetition to go in the OB.

Some screen shots would help. If you would like to discuss this directly off-line, email me and I will try to help although I have never done JSON before but if the isue is Xlate structural that should not make any difference.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Ab Lugtenburg]

Hi Jim ,

All the named actions controls i already did and over and over. Thats why i’m now on the forum ;->>

I put an  json config.doc with screenshots in the attachment

Did by the way also this with an vrl / db / hl7  but the xlate doen’t start an iterate

Our cloverleaf 19.1 is running on a windows 2016 enviroment

I just made a solution with a tcl converion script to a CSV and use this for update my db but it is kind of strange an would rather use the direct json to sql with a xlt translation

 

Attachments:

You must be logged in to view attached files.

[Jim Kosloskey]

Ab,

Well with the Xlate you show,  you would only get the last repetition in the OB.

So please provide a screen shot of the JSON tester showing a sample JSON message (hopefully a small set so we can see the entire repetition set).

You can also either use the Xlate Debugger to actually see what is happening in  the Xlate or place Tcl code echoing out the ITERATE variable and the IB value from within the ITERATE.

The above will tell you for certain if the ITERATE is actually working through the repetitions.

Sorry for the late reply, yesterday was a holiday here and I got very busy.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Ab Lugtenburg]

Hi Jim and  all,

I ‘m sorry for my late reply but i was rather busy .

I think i know what the cause is , the only thing i do not know the solution

The Json is read as one long string and not parsed at al in the xlate, you can see this in the attachment. It is a very strange thing

Attachments:

You must be logged in to view attached files.

[Jim Kosloskey]

Please echo out an element value from inside the ITERATE for each repetition. If you get a value, then the ITERATE is working its way through the repetitions.

The Xlate you presented will never give you all of the repetitions because the VRL cannot repeat unless it is part of an HRL.

Before we address the VRL issue though we need to assure the ITERATE is functioning – the echoes will tell you that.

Do you know how to echo elements out inside an Xlate?

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Charlie Bursell]

If we had a copy of your Json Package perhaps we could test locally.

[Jim Kosloskey]

Ab,

So were you able to echo out anything from inside your ITERATE?

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Ab Lugtenburg]

Hi all,

Just to busy but i’am today working on this problem so first i am going to find check the vrl an hrl, i will keep you informed

[Ab Lugtenburg]

Oke , i think i am missing some kind of overview or something like that but i made a HRL with 2 vrl ,one vrl s not repeating and the other one is repeating .

I put in the attachment a zip file containing the hrl, vrl, xlate, json and the json test file

I really don’t understand why the json is not repeated in the xlate , also tried debug but after the first record there is no other the xlate debug say.

 

Attachments:

You must be logged in to view attached files.

[Robert Kersemakers]

Ah! Have been doing my forst json xlates and I can see what is going wrong here. I don’t know all the nuances of json, just reporting what I am seeing.

Your testab2.json contains only one instance of the array (veld3,veld4,veld5). Therefore CL can only see the first (instance 0) of the fields.
If you had ‘loaded’ your test json into de Json Configurator, it would have created a json with 6 instances of (veld3,veld4,veld5). See attached testab3.json for the resulting json to be used in an xlate. With this you can process up to 6 instances of the repetition. I have also attached some printscreens.

I am not sure how this works if you don’t know how many repetitions there are going to be: say hundreds/thousands. Normally you will know approximately how many there will be, so make a format according to this I guess.
Is this something others have encountered already?

• This reply was modified 3 years, 10 months ago by Robert Kersemakers.

Attachments:

You must be logged in to view attached files.

Zuyderland Medisch Centrum; Heerlen/Sittard; The Netherlands

[Ab Lugtenburg]

Hi al and thanks for your thoughts, remarks and workouts etc etc.

After 2 months we discoverded the problem is a bug (infor confirmed this by my supplier)

it seems that the regional settings is the cause our settings set a , instead of a . in the decimal numbers thats is the problem and the workaround is to change that at your cloverleaf server . From version 20.1.0.0 ths bug is fixed

It also explains why the most of you guys no problems discovered. I think you uses the american region and i  live and work in the netherlands ;->>

 

 

[Thirumagal]

I am facing similar issue from JSON to CSV translation. I am new to Cloverleaf. Could you please help me on this?

Requirement is to translate from JSON to CSV. On testing, it writes only the last record from JSON to CSV.

Attached zip file holds the JSON Input and JSON/FRL/XLT Configurations

Attachments:

You must be logged in to view attached files.

[Thirumagal]

On adding CONTINUE it worked. Thanks all

[Jim Kosloskey]

You might need a SUPRESS as well – otherwise your last OB message could be repeated.

Just place the SUPRESS to the very beginning of your Xlate.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Thirumagal]

Thank you so much

[Author]

Replies