| Author |
Message
|
| coolcurls |
 Posted: Thu Feb 28, 2013 6:46 pm Post subject: Creating a JSON message using ESQL in Broker 7.0.0.3 |
 |
|
Novice
Joined: 03 Aug 2007
Posts: 15
|
Hello,
I am new to JSON format and trying to post a message in JSON format to a webservice. The source is CSV message and have to map it into array of records in JSON format like the below. Tried a few things but not able to achieve the following format using ESQL.
| Code: |
[{"d_code":"AFL","employee_id":"106137","f_code":"M","e_code":"E","imported_dt":"2013-02-19 13:36:16.537"}{"d_code":"AFL","employee_id":"106137","f_code":"M","e_code":"E","imported_dt":"2013-02-19 13:36:16.537"}]
|
Please help.
Thanks in advance. |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Fri Mar 01, 2013 12:49 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
What methods have you tried and what were the results?
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| kimbert |
| Posted: Fri Mar 01, 2013 2:16 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5543
Location: Southampton
|
- Create the JSON message that you would like your message flow to produce
- Create a simple flow that contains an input node and a Trace node.
- Parse the JSON, and trace the resulting message tree using the Trace node ( pattern should be ${Root} )
- Construct your message flow so that it generates a tree that is exactly like the one in the Trace node output. You may need to set the parser-specific field types ( JSON.Object , JSON.Array ) for individual elements in the tree. |
|
| Back to top |
|
|
| coolcurls |
| Posted: Fri Mar 01, 2013 6:24 am Post subject: |
|
|
Novice
Joined: 03 Aug 2007
Posts: 15
|
Hello,
Thanks for the response.
| Code: |
I was able to get the following output:
{"d_code":"AFL","employee_id":"106137","flsa_code":"M","e_code":"E"}
using the following code:
SET OutputRoot.JSON.Data.d_code= OutputLocalEnvironment.MRM.HCount[2].lc;
SET OutputRoot.JSON.Data.employee_id = OutputLocalEnvironment.MRM.HCount[2].EID;
SET OutputRoot.JSON.Data.flsa_code = OutputLocalEnvironment.MRM.Count[2].f_code;
SET OutputRoot.JSON.Data.e_code = OutputLocalEnvironment.MRM.Count[2].Stat;
|
I am trying to repeat the above record so did the following:
| Code: |
SET OutputRoot.JSON.Data TYPE = JSON.Array;
SET OutputRoot.JSON.Data.d_code[1]= OutputLocalEnvironment.MRM.HCount[2].lc;
SET OutputRoot.JSON.Data.employee_id[1] = OutputLocalEnvironment.MRM.HCount[2].EID;
SET OutputRoot.JSON.Data.flsa_code[1] = OutputLocalEnvironment.MRM.Count[2].f_code;
SET OutputRoot.JSON.Data.e_code[1] = OutputLocalEnvironment.MRM.Count[2].Stat;
SET OutputRoot.JSON.Data.d_code[2]= OutputLocalEnvironment.MRM.HCount[3];
SET OutputRoot.JSON.Data.employee_id[2] = OutputLocalEnvironment.MRM.HCount[3].EID;
SET OutputRoot.JSON.Data.flsa_code[2] = OutputLocalEnvironment.MRM.Count[3].f_code;
SET OutputRoot.JSON.Data.e_code[2] = OutputLocalEnvironment.MRM.Count[3].Stat;
Here is the output for the above code:
["AFL","106137","M","E","AFL","102039","M","E"]
|
Last edited by coolcurls on Fri Mar 01, 2013 7:52 am; edited 1 time in total |
|
| Back to top |
|
|
| kimbert |
| Posted: Fri Mar 01, 2013 6:38 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5543
Location: Southampton
|
You have not used the parser-specific field types. You will need them.
Somebody else asked a similar question on this forum very recently. Have you used the Search button yet? |
|
| Back to top |
|
|
| coolcurls |
| Posted: Fri Mar 01, 2013 7:51 am Post subject: |
|
|
Novice
Joined: 03 Aug 2007
Posts: 15
|
I tried the following but still same output.
| Code: |
SET OutputRoot.JSON.Data TYPE = JSON.Array;
CREATE LASTCHILD OF OutputRoot.JSON.Data TYPE NameValue NAME 'd_code' VALUE OutputLocalEnvironment.MRM.HCount[2].lc;
CREATE LASTCHILD OF OutputRoot.JSON.Data TYPE NameValue NAME 'employee_id' VALUE OutputLocalEnvironment.MRM.HCount[2].EID;
CREATE LASTCHILD OF OutputRoot.JSON.Data TYPE NameValue NAME 'flsa_code' VALUE OutputLocalEnvironment.MRM.Count[2].f_code;
CREATE LASTCHILD OF OutputRoot.JSON.Data TYPE NameValue NAME 'e_code' VALUE OutputLocalEnvironment.MRM.Count[2].Stat;
CREATE LASTCHILD OF OutputRoot.JSON.Data TYPE NameValue NAME 'd_code' VALUE OutputLocalEnvironment.MRM.HCount[3];
CREATE LASTCHILD OF OutputRoot.JSON.Data TYPE NameValue NAME 'employee_id' VALUE OutputLocalEnvironment.MRM.HCount[3].EID;
CREATE LASTCHILD OF OutputRoot.JSON.Data TYPE NameValue NAME 'flsa_code' VALUE OutputLocalEnvironment.MRM.Count[3].f_code;
CREATE LASTCHILD OF OutputRoot.JSON.Data TYPE NameValue NAME 'e_code' VALUE OutputLocalEnvironment.MRM.Count[3].Stat;
Output is still the same for the above code:
["AFL","106137","M","E","AFL","102039","M","E"]
|
And yes I did try the search button.
Thanks! |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Mar 01, 2013 8:00 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| The important bit you are missing is "parser specific type". |
|
| Back to top |
|
|
| kimbert |
| Posted: Fri Mar 01, 2013 8:05 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5543
Location: Southampton
|
I assume that you followed these steps:
| Quote: |
- Create the JSON message that you would like your message flow to produce
- Create a simple flow that contains an input node and a Trace node.
- Parse the JSON, and trace the resulting message tree using the Trace node ( pattern should be ${Root} )
- Construct your message flow so that it generates a tree that is exactly like the one in the Trace node output. You may need to set the parser-specific field types ( JSON.Object , JSON.Array ) for individual elements in the tree. |
So now you need to put a Trace node into your flow, and compare your message tree with the one that the JSON parser built. That will show you very clearly what your problem is. |
|
| Back to top |
|
|
| coolcurls |
| Posted: Fri Mar 01, 2013 9:23 am Post subject: |
|
|
Novice
Joined: 03 Aug 2007
Posts: 15
|
Hello kimbert,
oh I misunderstood what you posted earlier!
I did that and followed the steps and I got the desired output I wanted!
Here is my new code which works for me just fine.
Thanks for your help!
| Code: |
SET OutputRoot.JSON.Data TYPE = JSON.Array;
CREATE FIELD OutputRoot.JSON.Data.Item IDENTITY (JSON.Object)Item;
CREATE LASTCHILD OF OutputRoot.JSON.Data.Item[1] TYPE NameValue NAME 'd_code' VALUE OutputLocalEnvironment.MRM.HCount[2].lc;
CREATE LASTCHILD OF OutputRoot.JSON.Data.Item[1] TYPE NameValue NAME 'employee_id' VALUE OutputLocalEnvironment.MRM.HCount[2].EID;
CREATE LASTCHILD OF OutputRoot.JSON.Data.Item[1] TYPE NameValue NAME 'flsa_code' VALUE OutputLocalEnvironment.MRM.Count[2].f_code;
CREATE LASTCHILD OF OutputRoot.JSON.Data.Item[1] TYPE NameValue NAME 'e_code' VALUE OutputLocalEnvironment.MRM.Count[2].Stat;
CREATE LASTCHILD OF OutputRoot.JSON.Data.Item[2] TYPE NameValue NAME 'd_code' VALUE OutputLocalEnvironment.MRM.HCount[3];
CREATE LASTCHILD OF OutputRoot.JSON.Data.Item[2] TYPE NameValue NAME 'employee_id' VALUE OutputLocalEnvironment.MRM.HCount[3].EID;
CREATE LASTCHILD OF OutputRoot.JSON.Data.Item[2] TYPE NameValue NAME 'flsa_code' VALUE OutputLocalEnvironment.MRM.Count[3].f_code;
CREATE LASTCHILD OF OutputRoot.JSON.Data.Item[2] TYPE NameValue NAME 'e_code' VALUE OutputLocalEnvironment.MRM.Count[3].Stat;
|
|
|
| Back to top |
|
|
|
|
