| Author |
Message
|
| ghoshly |
 Posted: Wed Oct 02, 2019 11:03 am Post subject: XML to JSON conversion - Array creation with repetitive obj |
 |
|
Partisan
Joined: 10 Jan 2008
Posts: 333
|
Hello,
My problem is related to some previous discussed topic of XML to JSON conversion - Array creation with repetitive objects present in incoming data. I have looked through the forum and is using the generic recursive code provided by Kimbert, but causing some issue.
My incoming data looks like below, under each Batch section I have multiple fields and some repetitive objects as well.
| Code: |
<Batchs>
<Batch>
<Batch>
<Batch>
<Batch>
......
</Batchs> |
If I code as below, I am getting double brackets in the output as [[ ]]
| Code: |
CREATE LASTCHILD OF OutputRoot.JSON.Data IDENTITY(JSON.Array)Batch;
SET RF_Output.Batch.Item[] = SELECT I FROM InputRoot.XMLNSC.Batchs.Batch[] AS I;
CALL createJSONArrays(OutputRoot.JSON.Data);
|
JSON Output -
| Code: |
"Batch": [[{
"Owner": {
"OwnerID": "5"
}
..........
}]]
|
If I don't define the field type of Batch as JSON Array, we are getting additional name Item in the output, which the target application is not able to accept. Is there a way I can code so that the double braces of array can be replaced with single? Or please help to fix my coding mistake.
| Code: |
"Batch": {
"Item": [{
"Owner": {
"OwnerID": "5"
}, |
|
|
| Back to top |
|
 |
| ghoshly |
| Posted: Wed Oct 02, 2019 12:15 pm Post subject: Trace output |
|
|
Partisan
Joined: 10 Jan 2008
Posts: 333
|
Trace
| Code: |
+++++++++++++++++++++++++++++++++++++++++++++
( ['json' : 0x133904eb0]
(0x01000000:Object):Data = (
(0x01000000:Object):Header = (
(0x03000000:NameValue):ClientApplication = 'XXXX ESB' (CHARACTER)
(0x03000000:NameValue):ClientVersion = '10.0.0.10' (CHARACTER)
(0x03000000:NameValue):ApiVersion = '16.4.33.8' (CHARACTER)
(0x03000000:NameValue):MessageID = '11e9-b4a6-ac1f28820000' (CHARACTER)
)
(0x01000000:Object):Batch = ( ['json' : 0x1339051f0]
(0x01001000:Array):Item = (
(0x01000000:Object):Item = (
(0x01000000:Object ):Owner = (
(0x03000000:NameValue):OwnerID = '5' (CHARACTER)
) |
Last edited by ghoshly on Thu Oct 03, 2019 7:45 am; edited 1 time in total |
|
| Back to top |
|
|
| timber |
| Posted: Wed Oct 02, 2019 2:13 pm Post subject: |
|
|
Grand Master
Joined: 25 Aug 2015
Posts: 1292
|
Thanks for posting the Trace node output - I would have asked for it 
It does not look like a well-structured message tree, though. I would expect to see
| Code: |
OutputRoot
JSON
Data
Batch (JSON.Array)
Item (JSON.Object)
Item (JSON.Object)
...
Item (JSON.Object) |
|
|
| Back to top |
|
|
| ghoshly |
| Posted: Thu Oct 03, 2019 5:56 am Post subject: Required JSON message |
|
|
Partisan
Joined: 10 Jan 2008
Posts: 333
|
Hi Kimbert,
Thanks for looking at my post and responding. Mentioned below is the required JSON message structure which has a header object before the JSON Array. If I comment out the Recursive procedure call to make JSON array for repetitive objects, I am getting the high level array properly. I tried to pass the reference of child element to the procedure instead of the Root, but getting the double array in all these cases.
Mentioned below is the expected structure by the third party API
| Code: |
{
"Header": {
"ClientApplication ": "testing",
"ClientVersion": "1.0.0.0",
"ApiVersion": "15.7.4.0",
"MessageID": "3acb9289-532a-4e2b-bdf9-0c8f845cd623"
},
"Batch": [{
JSON Elements.
JSON Objects...
...............
},
{
JSON Elements.
JSON Objects.
...............
}
]
}
|
Last edited by ghoshly on Thu Oct 03, 2019 6:12 am; edited 1 time in total |
|
| Back to top |
|
|
| timber |
| Posted: Thu Oct 03, 2019 6:05 am Post subject: |
|
|
Grand Master
Joined: 25 Aug 2015
Posts: 1292
|
| Is that Trace node output, or did you paste it from the debugger window? Either way, the field types look wrong/inconsistent. |
|
| Back to top |
|
|
| ghoshly |
| Posted: Thu Oct 03, 2019 6:23 am Post subject: Code Vs Trace |
|
|
Partisan
Joined: 10 Jan 2008
Posts: 333
|
Code -
| Code: |
CREATE LASTCHILD OF OutputRoot.JSON.Data DOMAIN 'JSON' IDENTITY(JSON.Array)Batch;
SET RF_Output.Batch.Item[] = SELECT I FROM InputRoot.XMLNSC.Batchs.Batch[] AS I;
ATTACH RF_Header TO RF_Output AS FIRSTCHILD;
|
Trace -
| Code: |
+++++++++++++++++++++++++++++++++++++++++++++
( ['json' : 0x1338f8970]
(0x01000000:Object):Data = (
(0x01000000:Object):Header = (
(0x03000000:NameValue):ClientApplication = 'Sodexo - ESB' (CHARACTER)
(0x03000000:NameValue):ClientVersion = '10.0.0.10' (CHARACTER)
(0x03000000:NameValue):ApiVersion = '16.4.33.8' (CHARACTER)
(0x03000000:NameValue):MessageID = 'd6bad0de-e5e8-11e9-b4a6-ac1f28820000' (CHARACTER)
)
(0x01001000:Array ):Batch = ( ['json' : 0x133904eb0]
(0x01000000:Object):Item = (
(0x01000000:Object ):Owner = (
(0x03000000:NameValue):OwnerID = '5' (CHARACTER)
)
(0x03000000:NameValue):IsExcluded = 'false' (CHARACTER)
(0x03000000:NameValue):Name = 'ZZ - DELETE SNACK BAR CLIF WHITE CHOC MACADAMIA 1.59 OZ' (CHARACTER)
(0x03000000:NameValue):RawItemID = '212648' (CHARACTER)
(0x03000000:NameValue):TotalRawWeight = '100' (CHARACTER)
(0x01000000:Object ):TotalRawWeightUnit = (
(0x03000000:NameValue):UnitName = 'GM' (CHARACTER)
)
|
Last edited by ghoshly on Thu Oct 03, 2019 7:53 am; edited 1 time in total |
|
| Back to top |
|
|
| timber |
| Posted: Thu Oct 03, 2019 7:34 am Post subject: |
|
|
Grand Master
Joined: 25 Aug 2015
Posts: 1292
|
Two things about that trace:
1. You should use code tags, not quote tags. The indentation has been badly messed up, which makes it hard to see the structure of your message.
2. In your first post the Item node was marked as 'Array'. In today's post it's marked as 'Object'. Which one is correct? |
|
| Back to top |
|
|
| ghoshly |
| Posted: Thu Oct 03, 2019 7:43 am Post subject: Re: Code Vs Trace |
|
|
Partisan
Joined: 10 Jan 2008
Posts: 333
|
Code -
| Code: |
CREATE LASTCHILD OF OutputRoot.JSON.Data DOMAIN 'JSON' IDENTITY(JSON.Array)Batch;
SET RF_Output.Batch.Item[] = SELECT I FROM InputRoot.XMLNSC.Batchs.Batch[] AS I;
ATTACH RF_Header TO RF_Output AS FIRSTCHILD;
|
Trace -
| Code: |
+++++++++++++++++++++++++++++++++++++++++++++
( ['json' : 0x1338f8970]
(0x01000000:Object):Data = (
(0x01000000:Object):Header = (
(0x03000000:NameValue):ClientApplication = 'Sodexo - ESB' (CHARACTER)
(0x03000000:NameValue):ClientVersion = '10.0.0.10' (CHARACTER)
(0x03000000:NameValue):ApiVersion = '16.4.33.8' (CHARACTER)
(0x03000000:NameValue):MessageID = 'd6bad0de-e5e8-11e9-b4a6-ac1f28820000' (CHARACTER)
)
(0x01001000:Array ):Batch = ( ['json' : 0x133904eb0]
(0x01000000:Object):Item = (
(0x01000000:Object ):Owner = (
(0x03000000:NameValue):OwnerID = '5' (CHARACTER)
)
(0x03000000:NameValue):IsExcluded = 'false' (CHARACTER)
(0x03000000:NameValue):Name = 'ZZ - DELETE SNACK BAR CLIF WHITE CHOC MACADAMIA 1.59 OZ' (CHARACTER)
(0x03000000:NameValue):RawItemID = '212648' (CHARACTER)
(0x03000000:NameValue):TotalRawWeight = '100' (CHARACTER)
(0x01000000:Object ):TotalRawWeightUnit = (
(0x03000000:NameValue):UnitName = 'GM' (CHARACTER)
)
|
|
|
| Back to top |
|
|
| ghoshly |
| Posted: Thu Oct 03, 2019 7:52 am Post subject: |
|
|
Partisan
Joined: 10 Jan 2008
Posts: 333
|
In the first post, you could see I have the recursive Procedure call and that is making the double array, incorrect trace accordingly. Even I tried to pass the reference of child level instead of root as below
| Code: |
-- Create JSON Array for repititive elements
CALL createJSONArrays(OutputRoot.JSON.Data.Batch.Item);
|
In the later post I did not make the recursive Procedure call, hence the trace looks proper. Obviously it is not making the repetitive elements or objects into array. |
|
| Back to top |
|
|
| timber |
| Posted: Fri Oct 04, 2019 6:13 am Post subject: |
|
|
Grand Master
Joined: 25 Aug 2015
Posts: 1292
|
Sounds as if you know exactly what the message tree needs to look like. Your SELECT statement is marking 'Batch' as a JSON array, so not sure why you were calling createJSONArrays at all.
Is there some extra requirement that you have not shared with us yet? |
|
| Back to top |
|
|
| ghoshly |
| Posted: Fri Oct 04, 2019 6:49 am Post subject: |
|
|
Partisan
Joined: 10 Jan 2008
Posts: 333
|
Hello,
Under each Item object there are multiple repetitive objects, and in order to make them array, I was calling the recursive procedure createJSONArrays. |
|
| Back to top |
|
|
| timber |
| Posted: Fri Oct 04, 2019 7:27 am Post subject: |
|
|
Grand Master
Joined: 25 Aug 2015
Posts: 1292
|
You have taken some code that assumes it is dealing with a non-JSON message tree, and you are now applying it to a message tree that is already JSON-like.
Specifically, your message tree already contains a repeating node 'Item'. The recursive procedure is probably finding Item and trying to make a JSON array out of it.
You have two choices:
a) Modify your SELECT statement so that it does not create 'Item' nodes. Just copy the nodes across and let createJSONarrays sort out the JSON-ification of the message tree.
b) Modify createJSONarrays to ignore a repeating node if its name is 'Item' |
|
| Back to top |
|
|
|
|
