Json show records on table

Hi, guys I'm trying to recover and show my datafrom a json file on the dashboard but I don't know what is the problem.
This is my flow

[{"id":"cb5a86a8.addd98","type":"ui_template","z":"1c449847.08bad8","group":"f8e1666f.2b1148","name":"UI Table JSON","order":1,"width":"","height":"","format":"<style>\n.table\n{\n    height:400px;\n    width:700px;\n    background:lightblue;\n}\n</style>\n<div class=\"table\">\n<table style=\"width:100%\">\n  <tr>\n    <th>TIMESTAMP</th> \n    <th>SENSOR</th>\n    <th>RAW DATA (JSON)</th>\n  </tr>\n  <tr ng-repeat=\"x in msg.payload | limitTo:10\">\n    <td>{{msg.payload[$index].dateTime}}</td>\n    <td>{{msg.payload[$index].humedad}}</td>\n    <td>{{msg.payload[$index].presionlitros}}</td>\n  </tr>\n</table>\n</div>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","x":840,"y":300,"wires":[[]]},{"id":"bab96227.512f1","type":"inject","z":"1c449847.08bad8","name":"show RECORDS","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"1","crontab":"","once":true,"onceDelay":"","topic":"SELECT * FROM DATA ORDER BY TIMESTAMP DESC LIMIT 100","payload":"","payloadType":"date","x":290,"y":280,"wires":[["f3153f55.c0671"]]},{"id":"693f4c32.4fe174","type":"inject","z":"1c449847.08bad8","name":"create database table","repeat":"","crontab":"","once":false,"onceDelay":"","topic":"CREATE TABLE data(TIMESTAMP INT PRIMARY KEY NOT NULL, SENSOR TEXT NOT NULL,RAW_DATA TEXT NOT NULL)","payload":"","payloadType":"date","x":300,"y":340,"wires":[["41051f95.b85d2"]]},{"id":"21a079e5.2adbd6","type":"inject","z":"1c449847.08bad8","name":"delete records","repeat":"","crontab":"","once":false,"onceDelay":"","topic":"DELETE FROM DATA WHERE TIMESTAMP>=0","payload":"","payloadType":"date","x":300,"y":380,"wires":[["41051f95.b85d2"]]},{"id":"786cd462.6f1f0c","type":"inject","z":"1c449847.08bad8","name":"Drop Table","repeat":"","crontab":"","once":false,"onceDelay":"","topic":"drop table data","payload":"","payloadType":"date","x":300,"y":440,"wires":[["41051f95.b85d2"]]},{"id":"f3153f55.c0671","type":"file","z":"1c449847.08bad8","name":"log sensor data","filename":"/home/pi/Desktop/pruebasensores15072020/pruebasensorespresionhumedad.json","appendNewline":true,"createDir":true,"overwriteFile":"false","x":560,"y":300,"wires":[["cb5a86a8.addd98","9ab59f45.6ef32"]]},{"id":"41051f95.b85d2","type":"file","z":"1c449847.08bad8","name":"log sensor data","filename":"/home/pi/Desktop/pruebasensores15072020/pruebasensorespresionhumedad.json","appendNewline":true,"createDir":true,"overwriteFile":"false","x":800,"y":400,"wires":[[]]},{"id":"9ab59f45.6ef32","type":"debug","z":"1c449847.08bad8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":830,"y":220,"wires":[]},{"id":"f8e1666f.2b1148","type":"ui_group","z":"","name":"basedatos","tab":"82a0a0c2.92e02","order":2,"disp":true,"width":"6","collapse":false},{"id":"82a0a0c2.92e02","type":"ui_tab","z":"","name":"Home 2","icon":"dashboard","disabled":false,"hidden":false}]

Can you explain a bit more? You look like you are tryng t oread a database but are using a file node so I don't understand your flow or your question.

actually I'm trying to read a file node.

Why do your infect nodes contain sql statements?

what do you mean?

Typo - 'infect' should be 'inject'

Why do you have SQL here?

because I use to have it with sql lite node here is my old code I thougth that it will be easy just changing some nodes.

[{"id":"23aa06ea.a29e6a","type":"tab","label":"Database 2","disabled":true,"info":""},{"id":"b65f0397.0ae0d","type":"ui_template","z":"23aa06ea.a29e6a","group":"287e9249.a5911e","name":"UI Table JSON","order":1,"width":"","height":"","format":"<style>\n.table\n{\n    height:400px;\n    width:700px;\n    background:lightblue;\n}\n</style>\n<div class=\"table\">\n<table style=\"width:100%\">\n  <tr>\n    <th>TIMESTAMP</th> \n    <th>SENSOR</th>\n    <th>RAW DATA (JSON)</th>\n  </tr>\n  <tr ng-repeat=\"x in msg.payload | limitTo:10\">\n    <td>{{msg.payload[$index].TIMESTAMP}}</td>\n    <td>{{msg.payload[$index].SENSOR}}</td>\n    <td>{{msg.payload[$index].RAW_DATA}}</td>\n  </tr>\n</table>\n</div>","storeOutMessages":true,"fwdInMessages":true,"templateScope":"local","x":760,"y":60,"wires":[[]]},{"id":"bfa43eb9.b5542","type":"inject","z":"23aa06ea.a29e6a","name":"Test Data","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":140,"y":300,"wires":[["4843a284.28f34c"]]},{"id":"4843a284.28f34c","type":"function","z":"23aa06ea.a29e6a","name":"test data","func":"var count=context.get('count');\nvar timestamp=context.get('timestamp');\n\nif (count==undefined)\n{\n    count=1;\n    timestamp=1;\n}\nvar data1={\"sensor\":\"sensor1\",\"temperature\":21,\"humidity\":61};\nvar data2={\"sensor\":\"sensor2\",\"temperature\":22,\"humidity\":62};\nvar data3={\"sensor\":\"sensor3\",\"temperature\":23,\"humidity\":63};\nif (count%20==0)\n    count=-count;\ndata1.temperature+=(count*.25);\n//data2.temperature+=(count*.5);\n//data3.temperature+=(count*.75);\ndata1.humidity+=(count*.25);\n//data2.humidity+=(count*.5);\n//data3.humidity+=(count*.75);\nmsg.payload=[data1];\nvar msg1={};\nvar msg2={};\nvar msg3={};\n\ndata1.timestamp=timestamp;\ntimestamp+=1;\ndata2.timestamp=timestamp;\ntimestamp+=1;\ndata3.timestamp=timestamp;\n\nmsg1.payload=JSON.stringify(data1);\nmsg2.payload=JSON.stringify(data2);\nmsg3.payload=JSON.stringify(data3);\ntimestamp+=1;\ncount+=1;\ncontext.set('count',count);\ncontext.set('timestamp',timestamp);\nreturn [[msg1,msg2,msg3]];","outputs":1,"noerr":0,"x":240,"y":500,"wires":[["7cb83111.87e86","e9b6423c.b9958"]]},{"id":"e9b6423c.b9958","type":"function","z":"23aa06ea.a29e6a","name":"write query","func":"var raw_data=msg.payload;\nvar temp=JSON.parse(msg.payload);\nvar timestamp=temp.timestamp;\nvar sensor=temp.sensor;\nmsg=\"(timestamp,sensor,raw_data) values(\"+timestamp +\",\\'\"+sensor +\"\\'\"+ \",\\'\"+raw_data+\"\\'\"  +\")\";\nvar topic=\"INSERT INTO DATA \" +msg;\nvar msg1={};\nmsg1.topic=topic;\nmsg1.payload=timestamp;\nreturn msg1;","outputs":1,"noerr":0,"x":350,"y":380,"wires":[["1d8c5979.d645a7","3cb65cbb.c75244"]]},{"id":"1d8c5979.d645a7","type":"debug","z":"23aa06ea.a29e6a","name":"sql out","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":530,"y":340,"wires":[]},{"id":"7cb83111.87e86","type":"debug","z":"23aa06ea.a29e6a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":490,"y":520,"wires":[]},{"id":"3cb65cbb.c75244","type":"delay","z":"23aa06ea.a29e6a","name":"","pauseType":"delay","timeout":"1","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":560,"y":440,"wires":[["17bd7ab0.4eb095"]]},{"id":"17bd7ab0.4eb095","type":"sqlite","z":"23aa06ea.a29e6a","mydb":"b7f1797e.26cdb8","sqlquery":"msg.topic","sql":"","name":"","x":550,"y":200,"wires":[[]]},{"id":"64270a9e.f44b04","type":"inject","z":"23aa06ea.a29e6a","name":"show RECORDS","repeat":"1","crontab":"","once":true,"onceDelay":"","topic":"SELECT * FROM DATA ORDER BY TIMESTAMP DESC LIMIT 100","payload":"","payloadType":"date","x":230,"y":80,"wires":[["89035ff5.a928c"]]},{"id":"89035ff5.a928c","type":"sqlite","z":"23aa06ea.a29e6a","mydb":"b7f1797e.26cdb8","sqlquery":"msg.topic","sql":"","name":"","x":470,"y":60,"wires":[["b65f0397.0ae0d"]]},{"id":"15b0d1f2.14337e","type":"inject","z":"23aa06ea.a29e6a","name":"create database table","repeat":"","crontab":"","once":false,"onceDelay":"","topic":"CREATE TABLE data(TIMESTAMP INT PRIMARY KEY NOT NULL, SENSOR TEXT NOT NULL,RAW_DATA TEXT NOT NULL)","payload":"","payloadType":"date","x":240,"y":140,"wires":[["17bd7ab0.4eb095"]]},{"id":"a0096879.d546d8","type":"inject","z":"23aa06ea.a29e6a","name":"delete records","repeat":"","crontab":"","once":false,"onceDelay":"","topic":"DELETE FROM DATA WHERE TIMESTAMP>=0","payload":"","payloadType":"date","x":240,"y":180,"wires":[["17bd7ab0.4eb095"]]},{"id":"1c6b0b54.896545","type":"inject","z":"23aa06ea.a29e6a","name":"Drop Table","repeat":"","crontab":"","once":false,"onceDelay":"","topic":"drop table data","payload":"","payloadType":"date","x":240,"y":240,"wires":[["17bd7ab0.4eb095"]]},{"id":"1adf9b4e.606d05","type":"ui_button","z":"23aa06ea.a29e6a","name":"","group":"287e9249.a5911e","order":1,"width":0,"height":0,"passthru":false,"label":"show","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"SELECT * FROM DATA ORDER BY TIMESTAMP DESC LIMIT 100","x":110,"y":20,"wires":[["89035ff5.a928c"]]},{"id":"d21fea21.05cc28","type":"ui_button","z":"23aa06ea.a29e6a","name":"","group":"287e9249.a5911e","order":1,"width":0,"height":0,"passthru":false,"label":"create","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"DELETE FROM DATA WHERE TIMESTAMP>=0","x":50,"y":120,"wires":[["17bd7ab0.4eb095"]]},{"id":"4def0327.0df9cc","type":"ui_button","z":"23aa06ea.a29e6a","name":"","group":"287e9249.a5911e","order":1,"width":0,"height":0,"passthru":false,"label":"test data","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":100,"y":360,"wires":[["4843a284.28f34c"]]},{"id":"287e9249.a5911e","type":"ui_group","z":"","name":"Sensors","tab":"554130bd.d602c","order":1,"disp":true,"width":"14","collapse":false},{"id":"554130bd.d602c","type":"ui_tab","z":"","name":"Home","icon":"dashboard"}]

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.