Read data from JSON array

Hi Andrei,

I have used this code, to retrieve the last document based on timestamp from cloudant db. For example, if I have two documents first one with 2/8/2018 12:3 and second document with 2/8/2018 12: 7- timestamp. I expect this code to retrieve the second document with 2/8/2018 12:7 only and ignore the rest.

The code in the function node

let today = new Date().toDateString();
let extractor = function (elem) {
let extr = new Date(elem.payload.timestamp).toDateString();
node.warn(extr);
return extr == today;
}
var output = msg.payload.filter(extractor);
node.warn(output);
msg.payload = output;
return output;

The result is :
resu

The actual data that I have :

ccccc

Please guide me to solve this problem :disappointed_relieved:

Sir,

I just want to point out that the code now works correctly but the problem is when finding two documents in the same date, for example, the first document with 2/8/2018 2:20 and second documents with date 2/8/2018 2: 30, it will retrieve the first documents (2/8/2018 2:20 ). How can make it read the last document with date and time, please?
as shown below ,I have six objects all of them the same date but in different
times,the code selected only the object 0 while it should select last one .
11155555

Good, the answer for this question is using the pop() method (extract the last element from an array).

The code in your function should looks like below:

let today = new Date().toDateString();

let extractor = function (elem) {
    let extr = new Date(elem.payload.timestamp).toDateString();
    node.warn(extr == today);
    return extr == today;
}
    
var output = arr.filter(extractor);
let last = output.pop();
node.warn(last);
msg.payload = last;

return msg;

You will see the last element in yelllow color in the debug panel and it will be sent in msg.payload (add a debug node after your function to inspect msg payload).

Thanks for your reply,
The code given me the following errors

er

Indeed: I have posted wrongly. Correction below:

let today = new Date().toDateString();

let extractor = function (elem) {
    let extr = new Date(elem.payload.timestamp).toDateString();
    node.warn(extr == today);
    return extr == today;
}
    
var output = msg.payload.filter(extractor);
let last = output.pop();
node.warn(last);
msg.payload = last;

return msg;

I am confusing about something, I will be greatfull if anyone can expain it to me.

When my function node inserted the data into cloudant db, I have noticed that the documents stored not in order. For instance,I was thinking that my documents organized as following the first insert will then first in the db and the second will be second and so on, as shown below

But what I have found that when checked the document content and timestime that stored as followed

what is the wrong with this please !!

I donĀ“t know if it will be possible for someone to reply about the writing order in the database without having a look in your whole flow.

How about the question asked before (retrieve the last element) ? Is it all right now ?

NO, sir, I still have the same issue due to db not in order. The codes going to selected first the document with current date only from db but in case there more than one documents in the same date but a different time then the code looking only for the first document with the current time and ignore that latest document with current time .

Sir, I am ready to share my desktop with you to show you exact problem if you don't mind, please.

I don't know about cloudant but generally with a database you should always use a sort_by specification when retrieving data. So in your case you would sort by the date/time ascending, then select the last one, or descending and select the first. In SQL you can sort by a field and then use LIMIT to say how many you want so you would sort by date/time descending with LIMIT 1 and you would get the latest one.

I guess the best approach is like @Colin said (retrieving the data in the proper order). However it should not be difficult to change the function code to return the newest element (based on the timestamp). The code was written this way as your original statement was to retrieve the last in order (not the newest) element.

Thank you @Andrei
Thank you @Colin

I will consider that ,thanks again for your help .

Hi @Ahd,

This would be the modified function node. Again, I do not have a dataset for testing so there is always the risk that something is not good.

let today = new Date().toDateString();


let extractor = function (elem) {
    let extr = new Date(elem.payload.timestamp).toDateString();
    return extr == today;
}
    
let output1 = msg.payload.filter(extractor);

let output2 = output1.sort(function (a, b) {
    let atime = new Date(a.payload.timestamp);
    let btime = new Date(b.payload.timestamp);
  return atime-btime;
});

let last = output2.pop();

node.warn(last);
msg.payload =last;

return msg;

Sir,
I have run it then, it showed the following result

ggggggggggggggg

nnn

mmm... the function assumes that the timestamps value is a property nested inside the payload, like below:

r-001

Your post shows timestamp inside a different structure, like below:

r-002

Can you post another screenshot of the debug that shows how the timestamp appears inside the object?

The code, as written now, will first select the objects that have timestamp equals today (it will be a problem if there is no object stored with todaĆ½s date) generating a short array (variable output1) . This array will then be sorted out based on the timestamp and stored into the variable output2 . This last element (supposedly the newest one) is then extracted to the variable named "last" and stored in msg.payload.

You really would be much better to use the features of the database to give you just the latest. That is what databases are designed to do efficiently. To read them all in and then sort them is not the way to do it.

Hi @Andrei,

The screen below shows exact data that I have in DB.
bbbbbbbbbbbbbbbbbbbbbbb

By the way, when tested the code on the following data, the result was correct,
nnnnnnnnnnnnnnnnnnnn

But it fails when I have tested it on the real data that I have in my project which is shown in full screen above . Each object has two topics of data first is data which include time stamp and second is an image which includes image information.

Thanks@ Colin

I have tried my best to do that but I found cloudant db much different than sqldb. Because of it store the documents, not in order. the second stored them in object form and nest array that make the read more difficult.

Maybe you are not storing them the best way then. I cannot believe that it is not possible to attach a date to a document and then sort by date, that is such a basic requirement.
Like this possibly

1 Like

That explains the issue. In such case test this code inside the function node. It will fetch the timestamp inside payload.data

let today = new Date().toDateString();


let extractor = function (elem) {
    let extr = new Date(elem.payload.data.timestamp).toDateString();
    return extr == today;
}
    
let output1 = msg.payload.filter(extractor);

let output2 = output1.sort(function (a, b) {
    let atime = new Date(a.payload.data.timestamp);
    let btime = new Date(b.payload.data.timestamp);
  return atime-btime;
});

let last = output2.pop();

node.warn(last);
msg.payload =last;

return msg;
1 Like