Reading JSON messages with SAS ESP

1 Like

If REST is the new SOAP, then JSON is definitely the new XML. While XML has its strong points, JSON is preferred by most people I meet for simple messaging. JSON strengths include its lighter-weight nature and that it's purpose-built for messaging.

The Basics

As with XML, ESP expects specific formatting on input JSON messages. Specifically, it expects two "bracket" ([[) characters at the start of the message and two closing bracket (]]) characters at the end. Inside of these, each event is surrounded by "brace" ({}) characters as you would normally expect. So a simple input json message might look like this:

[
 [{
 "opcode": "i",
 "key": "1",
 "first": "steve",
 "last": "foerster",
 }, {
 "opcode": "i",
 "key": "2",
 "first": "david",
 "last": "robinson",
 }]
]

In SAS ESP, the opcode metadata field is not required and will be defaulted to 'I' (Insert) if omitted. Additionally a unique key can be generated by the source window with the window option, autogen-key="true". So that is not required either. The connector/adapter message type (fstype, rmqtype, etc.) must also be set to 'JSON.' Finally, the source window schema must match the incoming fields by name. So the schema for this message would look like this in ESP Studio

Nesting

Like XML, JSON supports information nesting. For example, a customer might have multiple phone numbers, multiple addresses, and/or email addresses and both XML and JSON support this by nesting. Nesting some pets into the above JSON message (and removing the opcode and id fields to emphasize that we don't need them) would look something like this:

[
 [{
 "first": "steve",
 "last": "foerster",
 "pets": [{
 "petID": "1",
 "name": "sparky"
 }, {
 "petID": "2",
 "name": "spot"
 }]
 }, {
 "first": "david",
 "last": "robinson",
 "pets": [{
 "petID": "1",
 "name": "mittens"
 }, {
 "petID": "2",
 "name": "fluffy"
 }]
}]
]

SAS ESP can resolve JSON nesting automatically in the source window. To enable this functionality, simply add field names that include the nested field names separated by underscores (_). To capture the nested pet information from the above example, you would name the source window fields, pets_petId and pets_name. To understand how the source window renders the nested data, see the image below.

The model

The ESP model used in this example is extremely simple and shows just how easy it can be to read JSON messages with SAS ESP.

<project name="proj" pubsub="none" threads="1">
  <contqueries>
    <contquery name="cq">
      <windows>
        <window-source name="src" insert-only="true" collapse-updates="true" autogen-key="true">
          <schema>
            <fields>
              <field name="key" type="int64" key="true"/>
              <field name="first" type="string"/>
              <field name="last" type="string"/>
              <field name="pets_petID" type="string"/>
              <field name="pets_name" type="string"/>
            </fields>
          </schema>
          <connectors>
            <connector name="conn" class="fs">
              <properties>
                <property name="type"><![CDATA[pub]]></property>
                <property name="fsname"><![CDATA[test.json]]></property>
                <property name="fstype"><![CDATA[json]]></property>
              </properties>
            </connector>
          </connectors>
        </window-source>
      </windows>
    </contquery>
  </contqueries>
</project>

For more information on SAS Event Stream Processing, see the software page linked below: https://www.sas.com/en_us/software/data-management/event-stream-processing.html