cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - New to the community? Learn how to post a question and get help from PTC and industry experts! X

JSON to InfoTable

MFritzhand
7-Bedrock

JSON to InfoTable

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;

 

Screen Shot 2021-04-06 at 7.43.35 PM.png

 

Screen Shot 2021-04-06 at 7.42.10 PM.png

 

1 ACCEPTED SOLUTION

Accepted Solutions

@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;

 

View solution in original post

10 REPLIES 10
tdixit
13-Aquamarine
(To:MFritzhand)

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
13-Aquamarine
(To: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

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;

 

I am on TWX 9.0 @tdixit 

tdixit
13-Aquamarine
(To:MFritzhand)

Hi @MFritzhand 

 

Could you please attach Data Shape here

GrahicVisualizer_Stock_DS

 Regards,

Toolika Dixit

@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.

tdixit
13-Aquamarine
(To:MFritzhand)

Hi @MFritzhand 

 

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

 

Regards,

Toolika Dixit

@tdixit okay thank you so much! 

@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;

 

Top Tags