• Guest, before posting your code please take these rules into consideration:
    • It is required to use our BBCode feature to display your code. While within the editor click < / > or >_ and place your code within the BB Code prompt. This helps others with finding a solution by making it easier to read and easier to copy.
    • You can also use markdown to share your code. When using markdown your code will be automatically converted to BBCode. For help with markdown check out the markdown guide.
    • Don't share a wall of code. All we want is the problem area, the code related to your issue.


    To learn more about how to use our BBCode feature, please click here.

    Thank you, Code Forum.

JavaScript Accessing JSON-data

JustinB

JustinB

Coder
Hello people,

I'm currently working on a project with node-red. I implemented a tabulator-table inside the dashboard-template-node and filling it with data from a mysql database.

Everything worked fine, until I tested it with a bigger database. I realized that it isn't the best way to load the whole database inside the table (it wont load the 900.000 rows). So I read the docs of tabulator and got in love with progressive loading. So I set up a script to make the database available to ajax and tried to get it working. At least the table shows up but the content isn't loading. I have a feeling theres something wrong with my syntax (never worked very much with json). Maybe you guys can see whats wrong right here.

The content of the ajax looks like this:

JSON: 
{"payload":[{"Register":1,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":2,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":3,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":4,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":5,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":6,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":7,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":8,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":9,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":10,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":11,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":12,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":13,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":14,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":15,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":16,"Druck":59,"Zeitstempel":"13:37:41:967"}]}

And the settings of the tabulator-table looks like this:

JavaScript: 
var tableM = new Tabulator("#table-messungen", {
        height:"1000px",
        placeholder:"Keine Daten verfügbar!",
        layout:"fitColumns",
        //groupBy:"Zeitstempel",
        //groupToggleElement:"header",
        ajaxURL:"http://10.207.48.36:5555/fetch-messungen",
        progressiveLoad:"load",
        progressiveLoadDelay:200,
        /*
        initialSort:[
            {column:"Zeitstempel", dir:"asc"},
        ],
         */
        columns:[
            {title:"Register", field:"payload[0].Register", width:150},
            {title:"Druck (Pascal)", field:"payload[0].Druck"},
            {title:"Zeitstempel", field:"payload[0].Zeitstempel"},
        ],
     
    });


EDIT: I just noticed tabulator seems to only accept an array.

Screenshot 2022-09-27 142612.png

What can I do?
 
Last edited:
Sort by date Sort by votes
JustinB said:
Hello people,

I'm currently working on a project with node-red. I implemented a tabulator-table inside the dashboard-template-node and filling it with data from a mysql database.

Everything worked fine, until I tested it with a bigger database. I realized that it isn't the best way to load the whole database inside the table (it wont load the 900.000 rows). So I read the docs of tabulator and got in love with progressive loading. So I set up a script to make the database available to ajax and tried to get it working. At least the table shows up but the content isn't loading. I have a feeling theres something wrong with my syntax (never worked very much with json). Maybe you guys can see whats wrong right here.

The content of the ajax looks like this:

JSON: 
{"payload":[{"Register":1,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":2,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":3,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":4,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":5,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":6,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":7,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":8,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":9,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":10,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":11,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":12,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":13,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":14,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":15,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":16,"Druck":59,"Zeitstempel":"13:37:41:967"}]}

And the settings of the tabulator-table looks like this:

JavaScript: 
var tableM = new Tabulator("#table-messungen", {
        height:"1000px",
        placeholder:"Keine Daten verfügbar!",
        layout:"fitColumns",
        //groupBy:"Zeitstempel",
        //groupToggleElement:"header",
        ajaxURL:"http://10.207.48.36:5555/fetch-messungen",
        progressiveLoad:"load",
        progressiveLoadDelay:200,
        /*
        initialSort:[
            {column:"Zeitstempel", dir:"asc"},
        ],
         */
        columns:[
            {title:"Register", field:"payload[0].Register", width:150},
            {title:"Druck (Pascal)", field:"payload[0].Druck"},
            {title:"Zeitstempel", field:"payload[0].Zeitstempel"},
        ],
    
    });


EDIT: I just noticed tabulator seems to only accept an array.

View attachment 1643

What can I do?
Click to expand...
Hi there, if you run the json you provided through a validator, it checks out fine.
jsonlint.com

The JSON Validator

JSONLint is the free online validator and json formatter tool for JSON, a lightweight data-interchange format. You can format json, validate json, with a quick and easy copy+paste.
jsonlint.com jsonlint.com

The issue is that somewhere in your function you are not providing an array. I suspect that this may solve your issue
Code: 
var myJsonObj = JSON.parse(jsonFromAjaxCall);
var myArr = myJsonObj.payload;
 
Upvote 0 Downvote
Antero360 said:
Hi there, if you run the json you provided through a validator, it checks out fine.
jsonlint.com

The JSON Validator

JSONLint is the free online validator and json formatter tool for JSON, a lightweight data-interchange format. You can format json, validate json, with a quick and easy copy+paste.
jsonlint.com jsonlint.com

The issue is that somewhere in your function you are not providing an array. I suspect that this may solve your issue
Code: 
var myJsonObj = JSON.parse(jsonFromAjaxCall);
var myArr = myJsonObj.payload;
Click to expand...
Thanks for your reply!

I realized that tabulator expects an array but my ajax function is returning a json. I never worked with "express" and "body-parser" and dont understand what I have to change in the code to return an array:

JavaScript: 
var express = require('express')
var path = require('path')
var createError = require('http-errors')
var cors = require('cors')
var bodyParser = require('body-parser')
var app = express()

var dbMySQLNode = require('./database')
// view engine setup
app.set('views', path.join(__dirname, '/'))
app.set('view engine', 'ejs')
app.use(bodyParser.json())
app.use(
  bodyParser.urlencoded({
    extended: true,
  }),
)

app.use(cors())
app.get('/', (req, res) => {
  res.render('index')
})
app.get('/fetch-countries', function (req, res) {
  dbMySQLNode.query('SELECT * FROM Country ORDER BY id desc', function (
    error,
    response,
  ) {
    if (error) {
      res.json({
        msg: error,
      })
    } else {
      res.json({
        msg: 'Data successfully fetched',
        country: response,
      })
    }
  })
})
app.listen(5555, function () {
  console.log('Node app is being served on port: 5555')
})
module.exports = app
 
Upvote 0 Downvote
JustinB said:
Thanks for your reply!

I realized that tabulator expects an array but my ajax function is returning a json. I never worked with "express" and "body-parser" and dont understand what I have to change in the code to return an array:

JavaScript: 
var express = require('express')
var path = require('path')
var createError = require('http-errors')
var cors = require('cors')
var bodyParser = require('body-parser')
var app = express()

var dbMySQLNode = require('./database')
// view engine setup
app.set('views', path.join(__dirname, '/'))
app.set('view engine', 'ejs')
app.use(bodyParser.json())
app.use(
  bodyParser.urlencoded({
    extended: true,
  }),
)

app.use(cors())
app.get('/', (req, res) => {
  res.render('index')
})
app.get('/fetch-countries', function (req, res) {
  dbMySQLNode.query('SELECT * FROM Country ORDER BY id desc', function (
    error,
    response,
  ) {
    if (error) {
      res.json({
        msg: error,
      })
    } else {
      res.json({
        msg: 'Data successfully fetched',
        country: response,
      })
    }
  })
})
app.listen(5555, function () {
  console.log('Node app is being served on port: 5555')
})
module.exports = app
Click to expand...
Hey there,
Sorry for not responding earlier. Been a rather meeting filled morning at work. Which of the calls produces that json you had earlier?
 
Upvote 0 Downvote
Antero360 said:
Hey there,
Sorry for not responding earlier. Been a rather meeting filled morning at work. Which of the calls produces that json you had earlier?
Click to expand...
No problem :)

Thats my question, I got this code from here. Seems to do its job very well, if I connect with my browser to port 5555 I can see that the data is getting fetched. One general question: in which format does MySQL export these data? As an array, json or something else?

If I had to guess I would say these rows with .json() are generating the json.. So maybe there is an array function too?
 
Upvote 0 Downvote
JustinB said:
No problem :)

Thats my question, I got this code from here. Seems to do its job very well, if I connect with my browser to port 5555 I can see that the data is getting fetched. One general question: in which format does MySQL export these data? As an array, json or something else?

If I had to guess I would say these rows with .json() are generating the json.. So maybe there is an array function too?
Click to expand...
So to answer your question, you seem to be confusing data export and data fetching. When working with databases, especially SQL/MySQL, if you are exporting your database directly from SQL Server or MySQL portal, you would generally either get a .sql file, or a .csv file. You could export it to JSON, but that would require a bit more work and custom software. When you are calling your database to get data, you as the developer has to decide what format you want the data to come back as. Typically, we have the data come back as JSON, just because it's easier to parse through and easier to convert the data to an object in the language we work with. However, there is also XML formatting.
 
Upvote 0 Downvote
Antero360 said:
So to answer your question, you seem to be confusing data export and data fetching. When working with databases, especially SQL/MySQL, if you are exporting your database directly from SQL Server or MySQL portal, you would generally either get a .sql file, or a .csv file. You could export it to JSON, but that would require a bit more work and custom software. When you are calling your database to get data, you as the developer has to decide what format you want the data to come back as. Typically, we have the data come back as JSON, just because it's easier to parse through and easier to convert the data to an object in the language we work with. However, there is also XML formatting.
Click to expand...
Okay, do you think I can somehow convert it to an array inside the server file?
 
Upvote 0 Downvote
Based on the sample json you provided:
JSON: 
{"payload":[{"Register":1,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":2,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":3,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":4,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":5,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":6,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":7,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":8,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":9,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":10,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":11,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":12,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":13,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":14,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":15,"Druck":59,"Zeitstempel":"13:37:41:967"},
{"Register":16,"Druck":59,"Zeitstempel":"13:37:41:967"}]}

You already have an array in there. The key "payload" contains an array of json strings.
what you could do is have a class to handle the root of your json, in this case, you only have 1 root element, "Payload", which has one property, an array of Objects, in this case, we will call them objs, each with a property of Register, Druck, Zeitstempel.
 
Last edited:
Upvote 0 Downvote
I really dont know what to do.. No matter what I'm trying, its always throwing

1664353681542.png

So I printed the response to the console to check what nodered is receiving and got the following

1664353853158.png

Why is it so difficult to me to access this data :rolleyes:
 
Upvote 0 Downvote
@Antero360 I finally got it to work. The problem was inside the server function. I had to change the name of the array (payload -> data) and the response also has to hand over a parameter called last_page, which i just set to 1 (as long as it works 😂)
 
Upvote 0 Downvote
JustinB said:
@Antero360 I finally got it to work. The problem was inside the server function. I had to change the name of the array (payload -> data) and the response also has to hand over a parameter called last_page, which i just set to 1 (as long as it works 😂)
Click to expand...
Glad to hear you got it working :). and lol, it happens. You have no idea how many times I've been stumped for hours on a bug, only to find out that it's a simple fix at the end... aggravating to no end XD
 
Upvote 0 Downvote
Top Bottom