How to parse a JSON array into multiple events using a functional window

2 Likes

A functional window can create multiple output events from a single input event. An event loop parses JSON or XML from an input event to create multiple output events. This parsing can be very powerful for transforming input JSON into events.

Let's look at an example for parsing a simple JSON message, along with a detailed explanation of how the parsing works. See the attachments for the complete XML model and input CSV file.

This is the input JSON to parse:

{  'Id': 'Id123', 
   'readings': 
     [  {  'name':  'sensor1',   
            'value':  -1    
         },
        {      
            'name':  'sensor2',
            'value':  23    
        }    ]
 }

For each entry in the readings array, an event should be created. From this input JSON, the functional window should create the following events:

<event opcode='insert' window='json_parse/cq1/Functional1'>
    <value name='Id'>Id123</value>
    <value name='counter'>0</value>
    <value name='eventNumber'>0</value>
    <value name='sensor_name'>sensor1</value>

    <value name='sensor_value'>-1</value>
</event>

<event opcode='insert' window='json_parse/cq1/Functional1'>
   <value name='Id'>Id123</value>
   <value name='counter'>0</value>
   <value name='eventNumber'>1</value>
   <value name='sensor_name'>sensor2</value>

   <value name='sensor_value'>23</value>
</event>

Functional Window XML

The single input JSON string caused two ESP events to be created. One event for the reading from sensor 1 and one event for the reading from sensor 2. Let's look at the pieces of the functional window which make this happen:

<window-functional pubsub="true" name="Functional1">
  <schema>
    <fields>
      <field name="Id" type="string" key="true"/>
      <field name="counter" type="int64" key="true"/>
      <field name="eventNumber" type="int64" key="true"/>
      <field name="sensor_name" type="string"/>
      <field name="sensor_value" type="int64"/>
    </fields>
  </schema>

First, the schema defines the fields for the output event. Since the input event only has one ID field, I added two additional keys, counter and eventNumber, to uniquely identify the event. None of the field names match fields from the input event, so there must be a function defined for each field to generate a value.

Now, I will define how to parse the data:

<function-context>
  <properties>
    <property-json name="jsonData"><![CDATA[$input_json]]></property-json>
  </properties>
  <functions>
    <function name="Id"><![CDATA[json(#jsonData,'Id')]]></function>
    <function name="counter"><![CDATA[eventCounter()]]></function>
  </functions>
</function-context>
<generate>0</generate>

This function-context will run once for each input event. The first step is to map the input field data as containing JSON. I set the property-json, named jsonData, to the value of input_json, $input_json, from the input event. I can now reference jsonData to retrieve values by the JSON key.

This is what the first function named Id is using. Id is set to the value of the JSON key Id from the JSON data using the json() function. By using # in front of jsonData, I'm referencing the property-json set up previously. The function named counter uses the built in function eventCounter() to obtain the current number of events processed by this functional window. This will help make the event key unique in the output events.

Finally, the <generate> tag is used. The functional window looks for a generate function to determine if an output event is created. By default, the functional window would try to create an output event from this functional context if no generate function is found. I don't want an output event yet because this context is just setting up some fields to use later. Coding a generate tag with a function of 0 causes no event to be generated from this functional context.

Event Loop

Now that the setup work is complete, an event loop is used to generate one or more events:

<event-loops>
  <event-loop-json name="jsonParse" data="json_readings">
    <use-json><![CDATA[#jsonData]]></use-json>
    <json>readings</json>

Since the input data is in JSON format, I use an event-loop-json tag. The name attribute is used to identify the event loop. The data attribute identifies a variable, json_readings, that is used to hold the JSON data for this loop. How that JSON data is found is controlled by the next two statements.

First, the use-json tag tells the event loop to find the JSON to parse in the property-json called jsonData. This references the property set up earlier, using the # notation.

Next, the json tag instructs the loop to find the JSON key readings and loop on each value found. Since "readings" is a JSON array, this causes the loop to execute one or more times, based on the number of entries in the JSON array. Each time the loop executes, the json_readings variable contains the JSON from the current object from the array.

Now, the functional window is ready to parse the individual entries in the JSON array into output events. This is done with another function-context:

 <function-context>
     <functions>
       <function name="eventNumber"><![CDATA[eventNumber()]]></function>
       <function name="Id"><![CDATA[$Id]]></function>
       <function name="sensor_name"><![CDATA[json($json_readings,'name')]]></function>
       <function name="sensor_value"><![CDATA[json($json_readings,'value')]]></function>
     </functions>
   </function-context>
 </event-loop-json>

</event-loops>

First, eventNumber uses the eventNumber() function to obtain the current number of output events generated from the input event. This is reset to 0 for each input event. Next, the Id function uses the value of Id parsed previously. The $ uses the value of the Id field. Then, the final two functions parse JSON keys from the JSON of this iteration of the loop using the json() function. Because json_readings is a field name and not a property, the $ notation is used instead of a #. Now, all of the fields defined in the schema for the event are filled in and an output event is created. The loop will run a second time, based on the input JSON containing two entries in the readings array. This causes the two output events to be created by the functional window.

Debugging

During the development of a model with a functional window, sometimes the output isn't quite what you expect. When this happens, use the window.functional logger set to level trace for additional information on processing the JSON. This is enabled by starting the dfesp_xml_server with option -loglevel window.functional=trace. A trace entry for each iteration of the event-loop-json is written, containing the JSON processed. Here is a trace record generated on the first iteration of the event-loop-json:

  json event loop 'jsonParse', setting data 'json_readings' to
{"name":sensor1,"value":-1}

This can help you understand what JSON keys and values are available for each iteration of the loop.

Additional Resources

This isn't all the functional window can do. See Creating Functional Windows for more information.

This example uses functions json, eventNumber and eventCounter. See the Programming Reference for a complete list of available functions.