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

How to retrieve data for JSON

Highlighted
Level 1

How to retrieve data for JSON

Hi,

I need to retrieve data from JSON and store in datatable. JSON is having a multiple tags. Please refer a below JSON data. Please let me know if anyone have a solution for it. Thanks in advance.

{"PhoneLocationList": {"PhoneLocation": [{

   "PhoneNo": "1234567890",

   "Status": "Success",

   "Location":    {

      "latitude": 50.3,

      "longitude": 20.2,

      "altitude": 3000,

      "accuracy": 60,

      "timestamp": "2013-06-24T10:50:57.000+0530"

   }

}]}}

Thanks and Regards,

Vaibhav Sharma

4 REPLIES 4

Re: How to retrieve data for JSON

Hi Vaibhav, if i understand your use case right here you can follow this knowledge base article How to convert a "Json input" to an "Infotable" in a ThingWorx service

Could you check this and let us know if you still have an issue.

Thanks,

Re: How to retrieve data for JSON

I already tried this example and it is working fine but my JSON is different due to which i am facing problem. Please refer above JSON format.

Re: How to retrieve data for JSON

Your data must be reformatted into an InfoTable format which looks like this for a table containing name and email columns:

(Text widget won't preserve the formatting from JSON Lint - you can see it structured if you use that tool)

{

"rows": [{

"name": "Abab",

"email": "example1@ptc.com"

},

{

"name": "Qaqa",

"email": "example2@ptc.com"

}

],

"dataShape": {

"fieldDefinitions": {

"name": {

"name": "name",

"aspects": {},

"description": "user name",

"baseType": "STRING",

"ordinal": 0

},

"email": {

"name": "email",

"aspects": {},

"description": "email address",

"baseType": "STRING",

"ordinal": 0

}

}

}

}

The point is that to be an InfoTable , you have two requirements: an array named 'rows' and a datashape describing the properties in each element of the 'rows' array. Your JSON structure is not flat so it can't just be turned into an InfoTable . You will either need to:

- flatten your structure by taking the child properties of Location and restating them as Loc.lattitude, Loc.longitude, ... so they fit into the above format.

- turn Location into a complete infotable (rows array plus dataShape) and make that the content of one of the properties in your top-level table.

If you want to easily see all the data fields in a grid in a mashup (columns in a grid will not render the value of an InfoTable column), you should flatten the JSON structure. If you want to carry Location in the table so you can use it as a data source for another table (say in a popup mashup), then the embedded InfoTable would be the easier approach.

Re: How to retrieve data for JSON

Hello Bruce Hulse,

 

Excellent response to how to serialize a data table for thingworx.  My use case of AddOrUpdateDataTableEntry:

{"rows":[{"CompSerial":"123412341235","StationName":"MyDesktop","UserName":"IRWINJ","Timestamp":"2019-01-14T15:43:38"}],
"dataShape": {"fieldDefinitions": {
"CompSerial": {"name": "CompSerial","aspects": {},"description": "Component serial number","baseType": "STRING","ordinal": 0},
"StationName": {"name": "StationName","aspects": {},"description": "Local machine name","baseType": "STRING","ordinal": 0},
"UserName": {"name": "UserName","aspects": {},"description": "SL/1Click user name","baseType": "STRING","ordinal": 0},
"Timestamp": {"name": "Timestamp","aspects": {},"description": "Transacion date/time","baseType": "STRING","ordinal": 0}}}}