| Author |
Message
|
| wmbv7newbie |
 Posted: Fri Mar 27, 2015 1:51 am Post subject: ESQL - JSON Array Issue - WMB V8 |
 |
|
Centurion
Joined: 13 May 2014
Posts: 121
|
Hi,
I am trying to create a JSON message which has one array object. I have some scalar fields before the array so am mapping them and then creating the array as below -
| Code: |
SET OutputRoot.JSON.Data.FirstName = refLrnr.com:FirstName;
SET OutputRoot.JSON.Data.MaidenName = refLrnr.com:MaidenName;
SET OutputRoot.JSON.Data.Gender = SUBSTRING(refLrnr.com:Gender FROM 1 FOR 1);
SET OutputRoot.JSON.Data.MaritalStatus = refLrnr.com:MaritalStatus;
CREATE FIELD OutputRoot.JSON.Data IDENTITY(JSON.Array)Data;
SET OutputRoot.JSON.Data.AdditionaNumbers[1].NumberType = 'val1';
SET OutputRoot.JSON.Data.AdditionaNumbers[1].Number = refAppl.lnr:BusinessAccountId;
|
I am writing this message to a queue which works good.
| Code: |
FirstName="ABC"
MaidenName="token"
Gender="F"
MaritalStatus="Single" |
The message flow 1 ends here.
I am reading this message from the queue in message flow 2. The problem here is all the field names after JSON.Data are now converted as Item with the values intact.
The tree looks like -
| Code: |
Item="ABC"
Item="token"
Item="F"
Item="Single" |
I tried commenting the array part of the code and the tree is as expected in the message flow 2.
Any idea what's wrong with the way I am creating the array object? |
|
| Back to top |
|
 |
| wmbv7newbie |
| Posted: Fri Mar 27, 2015 2:36 am Post subject: |
|
|
Centurion
Joined: 13 May 2014
Posts: 121
|
Hi,
Searched through a few other posts and got this working.
The previous code was converting the whole JSON object to an array and parser was looking for NameValue pairs. Since the root was not supposed to be array, it was defaulting the field names to Item which is something in-built of the JSON parser.
I created a new object(not array) of the AdditionalNumbers type and gave explicit Name-Value pairs. This worked!
| Code: |
CREATE FIELD OutputRoot.JSON.Data.AdditionaNumbers IDENTITY (JSON.Object)AdditionaNumbers;
CREATE LASTCHILD OF OutputRoot.JSON.Data.AdditionaNumbers[1] TYPE NameValue NAME 'NumberType' VALUE 1;
CREATE LASTCHILD OF OutputRoot.JSON.Data.AdditionaNumbers[1] TYPE NameValue NAME 'Number' VALUE refAppl.lnr:BusinessAccountId;
|
Friday looks good now  |
|
| Back to top |
|
|
| wmbv7newbie |
| Posted: Fri Mar 27, 2015 4:41 am Post subject: |
|
|
Centurion
Joined: 13 May 2014
Posts: 121
|
Well, looks like the target team is not okay with the name-value pairs. They want this to be an Array. Sample -
| Code: |
"AdditionalNumbers": [
{
"NumberType": "sample string 1",
"Number": "sample string 2"
},
],
"DateModified": "2015-01-16T07:49:34.3995369+02:00"
|
I have the below code -
| Code: |
SET OutputRoot.JSON.Data.AdditionalNumbers TYPE = JSON.Array;
CREATE LASTCHILD OF OutputRoot.JSON.Data.AdditionalNumbers TYPE NameValue NAME 'NumberType' VALUE '';
CREATE LASTCHILD OF OutputRoot.JSON.Data.AdditionalNumbers TYPE NameValue NAME 'Number' VALUE refAppl.lnr:BusinessAccountId;
SET OutputRoot.JSON.Data.DateModified = refLrnr.(XMLNSC.Attribute)LastModifiedDate;
|
But I am getting below output -
| Code: |
| {"AdditionalNumbers":["","1234567"],"DateModified":"2015-3-26T12:00:00"} |
Any hints on how to get the field names as well?
Last edited by wmbv7newbie on Fri Mar 27, 2015 4:59 am; edited 1 time in total |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Mar 27, 2015 4:43 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| I suspect you have to create an array of children of Additional Numbers, not siblings of Additional Numbers. |
|
| Back to top |
|
|
| wmbv7newbie |
| Posted: Fri Mar 27, 2015 5:00 am Post subject: |
|
|
Centurion
Joined: 13 May 2014
Posts: 121
|
Sorry i edited my previous post just when you were replying I think.
Yes, I modified the code to create the array but stuck at a different problem now. The field names in the array are not getting populated. |
|
| Back to top |
|
|
| kimbert |
| Posted: Fri Mar 27, 2015 7:51 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
Stating the obvious, but it's all about the message tree structure. The debugger is not great at showing that.
You should put a Trace node into your message flow, set the Pattern to ${Root} and inspect the output. Once you see how the message tree relates to the output it should be easy to get the output that you want.
And if not...you can post the message tree structure in a [c o d e] block so that we can give better advice.
_________________
Before you criticize someone, walk a mile in their shoes. That way you're a mile away, and you have their shoes too. |
|
| Back to top |
|
|
| wmbv7newbie |
| Posted: Tue Apr 07, 2015 4:06 am Post subject: |
|
|
Centurion
Joined: 13 May 2014
Posts: 121
|
Hi,
I goit it working using the below code -
| Code: |
CREATE FIELD OutputRoot.JSON.Data.AdditionalNumbers IDENTITY(JSON.Array)AdditionalNumbers;
SET OutputRoot.JSON.Data.AdditionalNumbers.Item[1].NumberType = 'sample string 1';
SET OutputRoot.JSON.Data.AdditionalNumbers.Item[1].Number = 'sample string 2'; |
Output :-
{"AdditionalNumbers":[{"NumberType":"sample string 1","Number":"sample string 2"}]} |
|
| Back to top |
|
|
|
|
