Video Tutorial - Logging Dynamic JSON Data to SQL or MES

JSON (JavaScript Object Notation) is a file format that has become fairly common in the industrial automation industry for storing data or information (including such things as configuration files). But also as one of the most common data formats used by RESTful APIs and web services, which are commonly used for providing a range of useful information including energy prices and weather data.

Sometimes, of course, depending on the application, some or all components of JSON packet might be variable or dynamic in nature, which can make it difficult for off-the-shelf industrial software solutions to properly parse the information from those packets.

In this post, I will cover step-by-step how to leverage the scripting capabilities of the OPC Router to access and parse JSON packets either from a JSON file saved to a specific file directory or from a RESTful web service after which that data can be used with any of the other connectors that OPC Router supports. For this example, we log the data to a SQL database.

Watch this video to see how straightforward it can be to configure OPC Router to parse JSON data from either a web service/API or from a JSON file and then log that data to MS-SQL or use it with any of the other supported OPC Router connectors.

In this video, we've shown you:

1. The format of a sample JSON payload format (this could be coming either as streamed from a web service or stored in a .json file), including what part of the packet might be variable or dynamic.
2. How to create a new connection in OPC Router.
3. How to create a JSON Transfer object for creating the JSON file when the connection is triggered (you will need a sample of the JSON payload to properly configure this object).
4. How to create a Script Transfer object for handling the dynamic nature of one or more of the variables in the JSON payload.
5. How to create a Database Transfer object for logging the parsed JSON data into a SQL database (including configuring the parameters for connecting to the SQL database and the setup of the table/columns that will be logged).
6. How to connect all of the transfer objects in the OPC Router visual workflow

While this how-to video specifically demonstrates logging the parsed JSON data to an MS-SQL database, the OPC Router supports many other interfaces and can take any data accessible by the OPC Router (such as the parsed JSON data) and share it with those other interfaces.

Don't forget to subscribe to our blog to find out about the latest updates regarding OPC Router and for other how-to videos on using OPC Router.  Try OPC Router on your own connectivity and integration projects with the fully-functional free trial.