Importing JSON Data From a Web Service in Origin 2019

はじめに

Improvements in Origin 2019 make it easier to request data from a web service and import it into an Origin workbook. The two improvements involve:

  • Improved support for making HTTP requests in Origin C.
  • More versatile support for managing JSON data in Origin C.

In previous versions of Origin, it took a combination of the web2file X-Function to make an HTTP request and save the result to a file, then load the file into a string, then convert JSON data to a struct, and finally extract data from the struct, to achieve the same results that are now easy to do.

In this blog post, we will see a example of making an HTTP GET request for some JSON data and importing it into an Origin worksheet. While Excel is able to do this, we feel that it is much easier to do the same thing in Origin C.

HTTP Requests

The okutil_http_get function is available in Origin 2019 (see below). If it returns  , then the request was made successfully and the results are returned in a pointer to a string.

OC_API int okutil_http_get(LPCSTR lpcszURL, string *pstrResult, int nConnectTimeout = 0, int nResponseTimeout = 30);
JSON Data

Prior to Origin 2019, JSON data could be managed in Origin C using structs. While that is a good mechanism, dealing with structs can be difficult and a bit inflexible. So, starting with Origin 2019,  JSON can be converted into an Origin C TreeNode. Origin C has a rich API for dealing with TreeNodes, so it is a very good way to manage JSON data.

To convert a string of JSON data to a TreeNode, simple use the JSON.FromString  method as follows:

TreeNode tnJson;
JSON.FromString(tnJson, strJson);

Additionally, there is a function that is especially useful for dealing with extracting data from the TreeNode:

BOOL JSON::TreeToVector(TreeNode& tree, vectorbase& vv, LPCSTR lpcszTagName = NULL);

This extracts a vector from a given TreeNode when that TreeNode contains data that was originally a scalar array in the JSON data.

Let’s look at a simple example of how easy it is to use these functions to import JSON data from a web service into an Origin worksheet.

Example

In the following example, we will query the Open Spectral Database for the IR Spectrum of Ceric Oxide. The request will be made using okutil_http_get  passing it the URI  http://osdb.info/spectra/scidata/000000316.  We will then convert the JSON response string into a TreeNode. Then, using the proper TreeNode “path syntax”, we will  extract the X and Y data vectors and put them into the active worksheet. For reference,  the structure of the TreeNode associated with the JSON data is presented at the end of this post.

Note: you can copy the code below and paste it into an Origin C file and compile it to run the example.

#include <Origin.h>

void web_service_json_import()
{
    string strURL = "http://osdb.info/spectra/scidata/00000316";
    string strJson;
    okutil_http_get(strURL, &strJson);
    if( strJson.IsEmpty() )
    {
        out_str("String returned from okutil_http_get is empty.\n");
        return;
    }

    // Convert the stringfied JSON into an Origin C Tree;
    TreeNode tnJson;
    JSON.FromString(tnJson, strJson);

    Worksheet wks = Project.ActiveLayer();
    Column colX = wks.Columns(0);
    Column colY = wks.Columns(1);

    vectorbase &vX = colX.GetDataObject();
    vectorbase &vY = colY.GetDataObject();

    // Load array data from the JSON tree into the vector pointers to the data fo the columns. 
    JSON.TreeToVector(tnJson.scidata.dataset.datagroup.dataseries._0.parameter.valuearray.numberarray, vX);
    JSON.TreeToVector(tnJson.scidata.dataset.datagroup.dataseries._1.parameter.valuearray.numberarray, vY);

}
More Advanced Functionality

We can expand upon the above example to illustrate two things: more thorough error checking for the HTTP GET  request and extracting individual strings to use for column long names. The changes to the above code are denoted in comments and highlighted.

#include <Origin.h>

void web_service_json_import_ex1()
{
    string strURL = "http://osdb.info/spectra/scidata/00000316";
    string strJson;
    okutil_http_get(strURL, &strJson);
    if( strJson.IsEmpty() )
    {
        out_str("String returned from okutil_http_get is empty.\n");
        return;
    }

    // Use the okutil_http_status function to check to make sure the call to
    // okutil_http_get returned an HTTP Status of 200 (OK).
    // okutil_http_get may return content but it may not be the expected content,
    // so it is good to check this way.
    DWORD dwHttpStatus = okutil_http_status();
    if( 200 != dwHttpStatus )
    {
        out_int("okutil_http_get had HTTP status: ", dwHttpStatus);
        return;
    }

    TreeNode tnJson;
    JSON.FromString(tnJson, strJson);

    Worksheet wks = Project.ActiveLayer();
    Column colX = wks.Columns(0);
    Column colY = wks.Columns(1);

    // Extract string values for the column long names from their respective TreeNodes.
    // When using this method, we MUST check to see that the TreeNodes are valid first
    // to avoid an error is they don't exist!
    if( tnJson.scidata.dataset.datagroup.dataseries._0.parameter.property )
        colX.SetLongName(tnJson.scidata.dataset.datagroup.dataseries._0.parameter.property.strVal);

    if( tnJson.scidata.dataset.datagroup.dataseries._1.parameter.property )
        colY.SetLongName(tnJson.scidata.dataset.datagroup.dataseries._1.parameter.property.strVal);
    
    vectorbase &vX = colX.GetDataObject();
    vectorbase &vY = colY.GetDataObject();

    JSON.TreeToVector(tnJson.scidata.dataset.datagroup.dataseries._0.parameter.valuearray.numberarray, vX);
    JSON.TreeToVector(tnJson.scidata.dataset.datagroup.dataseries._1.parameter.valuearray.numberarray, vY);
}

Additionally, there is a TreeNode method, GetNodeFromPath , that returns a TreeNode based on a string path. It is defined as:

TreeNode GetNodeFromPath(LPCSTR lpcszPath, BOOL bAddIfNotThere = FALSE, BOOL bCaseSensitive = FALSE)

It can be useful when you do not have a pre-defined TreeNode for the JSON data. In the following example, we have created a reusuable function to load data into a column based on a path string. This technique allows for code reuse and allows for clearer, simpler code. Observe how much shorter our main function is.

#include <Origin.h>
void web_service_json_import_ex2()
{
    string strURL = "http://osdb.info/spectra/scidata/00000316";
    string strJson;
    okutil_http_get(strURL, &strJson);
    if( strJson.IsEmpty() )
    {
        out_str("String returned from okutil_http_get is empty.\n");
        return;
    }
 
    Worksheet wks = Project.ActiveLayer();
    JsonArrayToColumn(strJson, "scidata.dataset.datagroup.dataseries._0.parameter.valuearray.numberarray", wks.Columns(0));
    JsonArrayToColumn(strJson, "scidata.dataset.datagroup.dataseries._1.parameter.valuearray.numberarray", wks.Columns(1));
 }

bool JsonArrayToColumn(const string& strJson, const string& strPath, const Column& col)
{
    if( strJson.IsEmpty() || strPath.IsEmpty() || !col.IsValid() )
        return false;

    TreeNode tnJson;
    JSON.FromString(tnJson, strJson);
    if( !tnJson.IsValid() )
        return false;

    TreeNode tn = tnJson.GetNodeFromPath(strPath);
    if( !tn.IsValid() )
        return false;
    
    vectorbase &vb = col.GetDataObject();
    return JSON.TreeToVector(tn, vb);
}
まとめ

We have illustrated how easy it can be to get data from a web service as JSON and import it into Origin. Of course, these are simple examples but they form the backbone of more complex import routines. Finally, please note that support for HTTP request headers will  become available in Origin 2019b.

JSON Data TreeNode Structure

In the code below, the nodes that we are using are highlighted.

OriginStorage
 +---context
 |    +---_0 = https://stuchalk.github.io/scidata/contexts/scidata.jsonld
 |    +---_1
 |    |    +---sci = http://stuchalk.github.io/scidata/ontology/scidata.owl#
 |    |    +---meas = http://stuchalk.github.io/scidata/ontology/scidata_measurement.owl#
 |    |    +---sub = http://stuchalk.github.io/scidata/ontology/substance/substance.owl#
 |    |    +---cao = http://champ-project.org/images/ontology/cao.owl#
 |    |    +---qudt = http://www.qudt.org/qudt/owl/1.0.0/unit.owl#
 |    |    +---dc = http://purl.org/dc/terms/
 |    |    +---ss = http://www.semanticweb.org/ontologies/cheminf.owl#
 |    |    \---xsd = http://www.w3.org/2001/XMLSchema#
 |    \---_2
 |         \---base = http://osdb.info/spectra/scidata/000000316/
 +---id = 
 +---uid = osdb:spectrum:000000316
 +---title = Ceric oxide (IR)
 +---author
 |    +---id = author
 |    +---type = dc:creator
 |    \---name = The Institute for Nanomaterials, Advanced Technology and Innovat
 +---description = IR spectrum of Ceric oxide
 +---publisher = The Institute for Nanomaterials, Advanced Technology and Innovat
 +---version = 1
 +---startdate = 2016-09-12
 +---permalink = http://osdb.info/spectra/view/000000316
 +---toc
 |    +---_0 = cao:spectrum
 |    +---_1 = sci:methodology
 |    +---_2 = meas:measurement
 |    +---_3 = sci:system
 |    +---_4 = sci:chemical
 |    +---_5 = sci:compound
 |    +---_6 = sci:dataset
 |    +---_7 = sci:dataseries
 |    +---_8 = sci:datapoint
 |    +---_9 = sci:datum
 |    \---_10 = sci:property
 +---scidata
 |    +---id = scidata/
 |    +---type = sci:scientificData
 |    +---type = property value
 |    +---property = Infrared Spectroscopy
 |    +---kind = spectrum
 |    +---methodology
 |    |    +---id = methodology/
 |    |    +---type = sci:methodology
 |    |    +---evaluation = experimental
 |    |    \---aspects
 |    |         \---_0
 |    |              +---id = measurement/1/
 |    |              +---type = meas:measurement
 |    |              +---techniqueType = spectroscopic
 |    |              +---technique = Infrared Spectroscopy
 |    |              +---instrumentType = IR
 |    |              +---instrument = Nicolet iN10
 |    |              \---processing = Ratio against background;%Transmittance->Absorbance;Subtraction or Addition;Smooth;Multiplied by a scalar;Truncated;ElementMultiply or ElementDivide;
 |    +---system
 |    |    +---id = system/
 |    |    +---type = sci:system
 |    |    +---discipline = chemistry
 |    |    +---subdiscipline = analytical chemistry
 |    |    \---facets
 |    |         +---_0
 |    |         |    +---id = compound/1/
 |    |         |    +---type = sci:compound
 |    |         |    +---name = Ceric oxide
 |    |         |    +---formula = CeO2
 |    |         |    +---molweight = 172.114
 |    |         |    +---inchi = InChI=1S/Ce.2O
 |    |         |    +---inchikey = CETPSERCERDGAM-UHFFFAOYSA-N
 |    |         |    \---iupacname = dioxocerium
 |    |         \---_1
 |    |              +---id = chemical/1/
 |    |              +---type = sci:chemical
 |    |              +---name = Ceric oxide
 |    |              +---description = A pure organic compound
 |    |              +---type = Single phase liquid
 |    |              \---components
 |    |                   \---_0
 |    |                        +---id = chemical/1//component/1/
 |    |                        +---type = sci:chemical
 |    |                        +---source = compound/1/
 |    |                        \---role
 |    |                             \---_0 = cao:analyte
 |    \---dataset
 |         +---id = dataset/
 |         +---type = sci:dataset
 |         +---source = measurement/1/
 |         +---scope = chemical/1/
 |         \---datagroup
 |              +---id = datagroup/1/
 |              +---type = sci:datagroup
 |              +---type = spectrum
 |              +---format = (x++(y..y))
 |              +---level = processed
 |              +---source = http://osdb.info/spectra/view/000000316/JCAMP
 |              +---attributes
 |              |    +---_0
 |              |    |    +---id = attribute/1/
 |              |    |    +---type = sci:attribute
 |              |    |    +---quantity = count
 |              |    |    +---property = Number of Data Points
 |              |    |    \---value
 |              |    |         +---id = attribute/1/value/
 |              |    |         +---type = sci:value
 |              |    |         \---number = 1712
 |              |    +---_1
 |              |    |    +---id = attribute/2/
 |              |    |    +---type = sci:attribute
 |              |    |    +---quantity = metric
 |              |    |    +---property = First X-axis Value
 |              |    |    \---value
 |              |    |         +---id = attribute/2/value/
 |              |    |         +---type = sci:value
 |              |    |         \---number = 700.033447
 |              |    +---_2
 |              |    |    +---id = attribute/3/
 |              |    |    +---type = sci:attribute
 |              |    |    +---quantity = metric
 |              |    |    +---property = Last X-axis Value
 |              |    |    \---value
 |              |    |         +---id = attribute/3/value/
 |              |    |         +---type = sci:value
 |              |    |         \---number = 3999.639893
 |              |    +---_3
 |              |    |    +---id = attribute/4/
 |              |    |    +---type = sci:attribute
 |              |    |    +---quantity = metric
 |              |    |    +---property = Maximum X-axis Value
 |              |    |    \---value
 |              |    |         +---id = attribute/4/value/
 |              |    |         +---type = sci:value
 |              |    |         \---number = 3999.639893
 |              |    +---_4
 |              |    |    +---id = attribute/5/
 |              |    |    +---type = sci:attribute
 |              |    |    +---quantity = metric
 |              |    |    +---property = Minimum X-axis Value
 |              |    |    \---value
 |              |    |         +---id = attribute/5/value/
 |              |    |         +---type = sci:value
 |              |    |         \---number = 700.033447
 |              |    +---_5
 |              |    |    +---id = attribute/6/
 |              |    |    +---type = sci:attribute
 |              |    |    +---quantity = metric
 |              |    |    +---property = Maximum Y-axis Value
 |              |    |    \---value
 |              |    |         +---id = attribute/6/value/
 |              |    |         +---type = sci:value
 |              |    |         \---number = 1
 |              |    +---_6
 |              |    |    +---id = attribute/7/
 |              |    |    +---type = sci:attribute
 |              |    |    +---quantity = metric
 |              |    |    +---property = Minimum Y-axis Value
 |              |    |    \---value
 |              |    |         +---id = attribute/7/value/
 |              |    |         +---type = sci:value
 |              |    |         \---number = 0
 |              |    +---_7
 |              |    |    +---id = attribute/8/
 |              |    |    +---type = sci:attribute
 |              |    |    +---quantity = metric
 |              |    |    +---property = X-axis Scaling Factor
 |              |    |    \---value
 |              |    |         +---id = attribute/8/value/
 |              |    |         +---type = sci:value
 |              |    |         \---number = 1
 |              |    +---_8
 |              |    |    +---id = attribute/9/
 |              |    |    +---type = sci:attribute
 |              |    |    +---quantity = metric
 |              |    |    +---property = Y-axis Scaling Factor
 |              |    |    \---value
 |              |    |         +---id = attribute/9/value/
 |              |    |         +---type = sci:value
 |              |    |         \---number = 1E-9
 |              |    +---_9
 |              |    |    +---id = attribute/10/
 |              |    |    +---type = sci:attribute
 |              |    |    +---quantity = metric
 |              |    |    +---property = First Y-axis Value
 |              |    |    \---value
 |              |    |         +---id = attribute/10/value/
 |              |    |         +---type = sci:value
 |              |    |         \---number = 0.959142
 |              |    +---_10
 |              |    |    +---id = attribute/11/
 |              |    |    +---type = sci:attribute
 |              |    |    +---quantity = metric
 |              |    |    +---property = X-axis Increment
 |              |    |    \---value
 |              |    |         +---id = attribute/11/value/
 |              |    |         +---type = sci:value
 |              |    |         \---number = 1.928467
 |              |    \---_11
 |              |         +---id = attribute/12/
 |              |         +---type = sci:attribute
 |              |         +---quantity = area density
 |              |         +---property = Spectral Resolution
 |              |         \---value
 |              |              +---id = attribute/12/value/
 |              |              +---type = sci:value
 |              |              \---number = 4
 |              \---dataseries
 |                   +---_0
 |                   |    +---id = dataseries/1/
 |                   |    +---type = sci:x-axis
 |                   |    +---label = Wavenumber
 |                   |    +---axis = independent
 |                   |    \---parameter
 |                   |         +---id = dataseries/1/parameter/
 |                   |         +---type = sci:parameter
 |                   |         +---quantity = inverse length
 |                   |         +---property = Wavenumber
 |                   |         \---valuearray
 |                   |              +---id = dataseries/1/parameter/valuearray/
 |                   |              +---type = sci:valuearray
 |                   |              +---datatype = decimal
 |                   |              \---numberarray
 |                   |                   +---_0 = 700.033
 |                   |                   +---_1 = 701.961467
 |                   |                   +---_2 = 703.889934
 |                   |                   +---_3 = 705.818401
 |                   |                   +---_4 = 707.746868
 |                   |                   +---_5 = 709.675335
 |                   |                   +---_6 = 711.603802
 |                   |                   +---_7 = 713.532269
 |                   |                   +---_8 = 715.460736
 |                   |                   +---_9 = 717.389203
 |                   |                   +---_10 = 719.31767
 |                   |                   ...
               |                   \---_1711 = 3999.640037
 |                   \---_1
 |                        +---id = dataseries/2/
 |                        +---type = sci:y-axis
 |                        +---label = Absorbance
 |                        +---axis = dependent
 |                        \---parameter
 |                             +---id = dataseries/2/parameter/
 |                             +---type = sci:parameter
 |                             +---quantity = dimensionless
 |                             +---property = Absorbance
 |                             \---valuearray
 |                                  +---id = dataseries/2/parameter/valuearray/
 |                                  +---type = sci:valuearray
 |                                  +---datatype = decimal
 |                                  \---numberarray
 |                                       +---_0 = 0.9591421
 |                                       +---_1 = 0.9670674
 |                                       +---_2 = 0.9746668
 |                                       +---_3 = 0.984482
 |                                       +---_4 = 0.9929059
 |                                       +---_5 = 0.9975391
 |                                       +---_6 = 1
 |                                       +---_7 = 0.999759
 |                                       +---_8 = 0.9970008
 |                                       +---_9 = 0.9918279
 |                                       +---_10 = 0.9841226
 |                   |                   ...
 |                                       \---_1711 = 0.2528931
 +---sources
 |    +---_0
 |    |    +---id = source/1/
 |    |    +---type = dc:source
 |    |    +---citation = Ceric oxide (IR) - The Open Spectral Database, http://osdb.info
 |    |    \---url = http://osdb.info/spectra/scidata/000000316/
 |    \---_1
 |         +---id = source/2/
 |         +---type = dc:source
 |         +---citation = Part of the Lanthanoid compounds Collection - 
 |         \---url = 
 \---rights
      +---id = rights/
      +---type = dc:rights
      \---license = http://creativecommons.org/publicdomain/zero/1.0/

 

About Chris Drozdowski

Chris Drozdowski is a Product Support Engineer at OriginLab. He loves to talk to customers and educate them. He particularly relishes diagnosing and solving difficult, edge-case issues. As well, he contributes code to help solve problems or enhance user experience. In his down time at work, he likes to research and write about esoteric product features. Outside of work, he enjoys spending time with his family, having fun with C++, working on his aquarium, and exploring craft beers.

View all posts by Chris Drozdowski →

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です