• Preparation: Create database
• Create the Channel
• Tutorial Instructions
• Complete Sample Code
• More Information

In this article we will copy a JSON message and save it to a database. For simplicity we will only map a few fields, but as you will see extra fields can easily be added. We placed the mapping code in a To Translator component, which is (usually) the most logical place when you are mapping to a database.

You can use exactly the same method to copy data between any of the different types of node trees.

These are the steps:

1. Preparation: Use the From Translator component to import and queue the JSON message data
2. Parse the message into a source JSON tree structure
   • Note: Unlike HL7, XML etc a JSON tree structure is a Lua table not a node tree
3. Create a database table node tree for the target message
4. Map the data from the source JSON tree to the target node tree

This tutorial only addresses the last three steps, the preparatory step is simulated by using sample data. To run this channel you would need to complete the first step, see the tutorial Reading JSON data files in this section.

We recommend that you type in the code for each step, but we also include complete Sample Code if you prefer to paste it in and follow along.

Preparation: Create the “test” database [top]

If you do not already have a SQLite database called “test” in the Iguana install directory, then you can create it by following the instructions in the Create a SQLite DB to match your VMD tutorial.

Create the Channel [top]

1. Create a Channel with the the following settings:
   • Source = From Translator
   • Destination = To Translator
   • Channel name = Mapping JSON to Database
2. Click the Add Channel button to create the channel.
   Ignore the red warning messages, see resolving the milestone configuration error.
3. Open the Translator by clicking the Edit Script link at the bottom of the Destination tab.
4. Download and import the Mapping_JSON_to_Database_To_Translator.zip project file.
   This file contains a skeleton project and six sample JSON messages.
5. Iguana will load the project and data into the Translator, your screen should look like this:
   

Tutorial Instructions [top]

1. Pass the message data to the script.
   • Iguana automatically passes the message data to the main() function
   • The message can be accessed using the Data parameter
   • No action is needed
2. Parse the JSON message into a Lua table.
   Add the following line of code to your script:
   
3. Compare the text message and the parsed message.
   Click on the Data parameter (red text) and the table icon:
   
   The messages should look like this (we clicked on the patients element in the Msg table to expand it):
   
4. Choose the Message Definition name from your VMD that includes the tables you wish to use in its Table Grammar.
   In this case we will use the ADT message definition as it includes the patient (and other) tables:
   
   Note: For instructions on viewing a VMD, see the tutorial Viewing a VMD file in the general tutorials section.
5. Create the database tables target node tree.
   Add the following line of code to your script:
   
6. Map the JSON table node patients>patient-id to the databases table node patient.Id.
   Enter this line of mapping code, auto-completion will help you to find the fields:
   
7. Complete the patient mapping.
   Add these three lines of code:
   
8. Compare the incoming and outgoing messages.
   Add a trace() command and click on the Msg and Out (ADT) annotations to view them:
   
   As you can see the mapping worked correctly, the patient data was mapped from the incoming to the outgoing message.
9. Write the data to the database.
   Add this code to your script:
   
   Note: This is not part of the mapping process, but it is the needed to save the data to the database.

Complete Sample Code [top]

Here is the completed mapping code that you can cut and paste into your script:

function main(Data)
   -- (1) Parse the JSON message
   local Msg = json.parse(Data)
   
   -- (2) Create the database tables target node tree
   local Out = db.tables{vmd = 'example/demo.vmd', name = 'ADT'}
   
   -- (3) Map (part of) the message
   Out.patient[1].Id        = Msg.patients["patient-id"]
   Out.patient[1].LastName  = Msg.patients["last-name"]
   Out.patient[1].GivenName = Msg.patients["first-name"]
   Out.patient[1].Ssn       = Msg.patients["social-security-no"]
   
   -- (4) Save data to database
   
   -- connect to the database
   if not Conn or not Conn:check() then
      Conn = db.connect{
         api=db.SQLITE,
         name='test',
         user='',
         password='',
         live=true
      }
   end
   
   -- merge the data into the database
   Conn:merge{data=Out, live = true}
end

More Information [top]

• See our other interface tutorials in this section
• See our general tutorials section
• See our code samples section