JSON to InfoTable

[MFritzhand]

Hello, I followed various articles from community forum here as well as this TWX article. and I am still having issues with my code outputting to an InfoTable. I am using an API to fetch Stock Price JSON data and having it be converted to an InfoTable, so it can be displayed into a graph in Mashups. 

I've attached screenshots of my DataShape Field Definitions as well as the JSON Data Output.

The TWX code, with it outputting the columns with "No Data" is below. I've tried playing around with the for loop and doing a data.array.length but that mentions length is undefined. I am not sure if that would output to it being an InfoTable either. Thank you.

var restParams = {

  proxyScheme: undefined /* STRING */,
  headers: undefined /* JSON */,
  ignoreSSLErrors: true /* BOOLEAN */,
  useNTLM: undefined /* BOOLEAN */,
  workstation: undefined /* STRING */,
  useProxy: undefined /* BOOLEAN */,
  withCookies: undefined /* BOOLEAN */,
  proxyHost: undefined /* STRING */,
  url: "https://finnhub.io/api/v1/quote?symbol=AAPL&token=c1mbi3i37fkpnsp59e6g" /* STRING */,
  content: undefined /* JSON */,
  timeout: undefined /* NUMBER */,
  proxyPort: undefined /* INTEGER */,
  password: undefined /* STRING */,
  domain: undefined /* STRING */,
  username: undefined /* STRING */

};

// result: JSON

var data = Resources["ContentLoaderFunctions"].GetJSON(restParams);

var params = {

infoTableName: "InfoTable" /* STRING */,
dataShapeName: "GrahicVisualizer_Stock_DS" /* DATASHAPENAME */

};

// result: INFOTABLE

var jsonTable = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape(params);

//var tableLength = CobotInfoTable.rows.length;
//fix code below
for(var i=0;i<data.length;i++)

{

jsonTable.AddRow({
    c:data.c, 
    h:data.h,
    l:data.l, 
    o:data.o,
    pc:data.pc,
    t:data.t
});

}

result = jsonTable;

 

[tdixit]

Hello @MFritzhand 

Thank you for reaching out to PTC

I will try this at my end and will get back to you.

Meanwhile could you please help me with your ThingWorx version

Regards,

Toolika Dixit

[tdixit]

Hi @MFritzhand 

I have tried to convert the JSON to InfoTable at my end and I am able to do that, I have attached the sample entities for your reference 

Please follow below steps on Composer:

1. Create a Thing e.g. "JsonToInfotableThing"
2. Create a Property type JSON e.g. "myjson"
3. Set the JSON input data to the property "myjson" 
   Note: Assuming sample input JSON as below
   {
       "chartType": "NONBOOLEAN",
       "PVAResults": {
           "rows": [{
               "count": 1,
               "pva": {
                   "rows": [{
                       "actual": 1.88999999,
                       "predicted": 1.8962275055869233
                   }]
   }
    
4. Create a Data Shape e.g. DS0524 having columns as Count, Actual and Predicted all of base type Numeric
5. Create a Custom JavaScript Service as below:

var params = {
        infoTableName : "InfoTable",
        dataShapeName : "DS0524"
};

//CreateInfoTableFromDataShape(infoTableName:STRING("InfoTable"), dataShapeName:STRING):INFOTABLE(DS0524)
var result = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape(params);

tableLength = me.myjson.PVAResults.rows.length;

for(var i=0; i<tableLength; i++) {
    result.AddRow({count:me.myjson.PVAResults.rows[i].count,actual:me.myjson.PVAResults.rows[i].pva.rows[0].actual,predicted:me.myjson.PVAResults.rows[i].pva.rows[0].predicted});
}

6. Test and validate Service to get the desired results

Regards,

Toolika Dixit

[MFritzhand]

Hi @tdixit ,

Thank you for getting back to me. What you provided to me is not what I am looking to do. I attached my code here. I am wanting to use the LoadJSON snippet which will automatically pull the JSON object information, what you provided was approaching it manually entry. And then have that LoadJSON code be converted to an InfoTable. There should be no input, since this will be automated. 

var restParams = {

  proxyScheme: undefined /* STRING */,
  headers: undefined /* JSON */,
  ignoreSSLErrors: true /* BOOLEAN */,
  useNTLM: undefined /* BOOLEAN */,
  workstation: undefined /* STRING */,
  useProxy: undefined /* BOOLEAN */,
  withCookies: undefined /* BOOLEAN */,
  proxyHost: undefined /* STRING */,
  url: "https://finnhub.io/api/v1/quote?symbol=AAPL&token=c1mbi3i37fkpnsp59e6g" /* STRING */,
  content: undefined /* JSON */,
  timeout: undefined /* NUMBER */,
  proxyPort: undefined /* INTEGER */,
  password: undefined /* STRING */,
  domain: undefined /* STRING */,
  username: undefined /* STRING */

};

// result: JSON

var data = Resources["ContentLoaderFunctions"].GetJSON(restParams);

var params = {

infoTableName: "InfoTable" /* STRING */,
dataShapeName: "GrahicVisualizer_Stock_DS" /* DATASHAPENAME */

};

// result: INFOTABLE

var jsonTable = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape(params);

//var tableLength = CobotInfoTable.rows.length;
//fix code below
for(var i=0;i<data.length;i++)

{

jsonTable.AddRow({
    c:data.c, 
    h:data.h,
    l:data.l, 
    o:data.o,
    pc:data.pc,
    t:data.t
});

}

result = jsonTable;

[MFritzhand]

I am on TWX 9.0 @tdixit 

[tdixit]

Hi @MFritzhand 

Could you please attach Data Shape here

GrahicVisualizer_Stock_DS

 Regards,

Toolika Dixit

[MFritzhand]

@tdixit here you are. The naming of the field definitions are the exact same as the API URL of the objects when you call. I've attached both screenshots in original reference. To reiterate the idea is to use GetJSON snippet which works. And then have that real time information be converted to an Infotable. The conversion would then add a new row to that infotable to continually update it. From there, we can pull it to a line graph using the data points.

[MFritzhand]

https://community.ptc.com/t5/ThingWorx-Developers/JSON-in-repository-to-InfoTable-is-returning-nothing/m-p/561790

This seems to be similar issue

[tdixit]

Hi @MFritzhand 

I will try to reproduce this at my end and will get back to you

Regards,

Toolika Dixit

[MFritzhand]

@tdixit okay thank you so much! 

[MFritzhand · Accepted solution]

@tdixit ,

I managed to solve the issue on my end. InfoTable really only function with arrays, that is why all the solutions on forums I were seeing here were mentioning the for loop to parse it. The API I was pulling from used just one specific object, so I was unable to turn that into an array. 

Through trial and error, I tried Stringify method, which didn't work really. I then proceeded utilizing Parse method. This achieved getting each number within the object by itself. I set a variable for each number. I was able to call each specific number... from there I tried converting to infotable using the AddRow snippet. Encountered an error, "unable to convert DOUBLE to JSON". I thought okay... I switched the datatypes on the datashape field defintions to number, so Double can be converted. I ran it, was successful. 

me.myjson = Resources["ContentLoaderFunctions"].GetJSON(params);

var myJSON = JSON.parse(me.myjson);
//result = data;
//result = myJSON;
var currentPrice = me.myjson.c;
var highPrice = me.myjson.h;
var lowPrice = me.myjson.l;
var openPrice = me.myjson.o;
var previousClose = me.myjson.pc;
var timeStamp = me.myjson.t;

var params = {
    infoTableName : "InfoTable",
    dataShapeName : "GrahicVisualizer_Stock_DS"
};
// CreateInfoTableFromDataShape(infoTableName:STRING("InfoTable"), dataShapeName:STRING):INFOTABLE(GrahicVisualizer_Stock_DS)
var infoTable = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape(params);

var newRow = new Object();
newRow.c = currentPrice;
newRow.h = highPrice;
newRow.l = lowPrice;
newRow.o = openPrice;
newRow.pc = previousClose;
newRow.t = timeStamp;

infoTable.AddRow(newRow);

result = infoTable;