# Unable to query json data from http request

**URL:** <https://discourse.nodered.org/t/unable-to-query-json-data-from-http-request/54707>\
**Category:** General\
**Tags:** http-request\
**Created:** [4 December 2021 11:15 UTC](https://discourse.nodered.org/t/unable-to-query-json-data-from-http-request/54707 "2021-12-04T11:15:48Z")\
**Posts on this page:** 10\
**Page:** 2

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [6 December 2021 08:36 UTC](https://discourse.nodered.org/t/unable-to-query-json-data-from-http-request/54707/21 "2021-12-06T08:36:17Z")

</div>

> [@blob](#):
>
> eventually send it to Mysql DB and also store it in json db (node named - deb)

ok .. thats is very clear .. although i dont see a reason for using the json db node .. are you are storing different information with it than what is already in mySQL ?

my recommendation would be to simply query mySQL as shown in my previous post

---

<div class="post-metadata">

**Author:** ![blob](https://avatars.discourse-cdn.com/v4/letter/b/ebca7d/32.png) [@blob](https://discourse.nodered.org/u/blob)\
**Post date:** [6 December 2021 08:48 UTC](https://discourse.nodered.org/t/unable-to-query-json-data-from-http-request/54707/22 "2021-12-06T08:48:04Z")

</div>

> [@UnborN](#):
>
> are you are storing different information with it than what is already in mySQL ?

Although both store the same data but Mysql DB stores this data permanently and json db just has the latest data. The data is requested every minute. The interval cycle to request the data from all the dishes is 1 minute. At any given point of time json db has the last minute data.

> [@UnborN](#):
>
> my recommendation would be to simply query mySQL as shown in my previous post

Can we build an API using node red by the approach you are suggesting ? querying Mysql ? There is huge data there in Mysql, last two years data plus addition of data everyday.

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [6 December 2021 09:02 UTC](https://discourse.nodered.org/t/unable-to-query-json-data-from-http-request/54707/23 "2021-12-06T09:02:34Z")

</div>

> [@blob](#):
>
> Can we build an API using node red by the approach you are suggesting ? querying Mysql ? There is huge data there in Mysql, last two years data plus addition of data everyday.

ofcourse you can .. as i showed above .. you just need to change the SQL command around a bit to just return the last matching row instead of 2 year old data

tell us the db table name and show us a screenshot of the column names and a few rows to get an understanding of the db structure

it could be something like :

```javascript
let ip = msg.payload.ip;

// replace <table> with your db table
// replace <ip-column> with your ip column name 
msg.topic = `SELECT * FROM <table> WHERE <ip-column> = '${ip}' ORDER BY <datatime> DESC LIMIT 1`

return msg;

```

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [7 December 2021 05:06 UTC](https://discourse.nodered.org/t/unable-to-query-json-data-from-http-request/54707/24 "2021-12-07T05:06:30Z")

</div>

If you are concerned about performance, by querying the mySQL database to get the last record and prefer to use the json db (deb) node instead .. then wire it, as it would, in the following configuration

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/7/c7c5daba42ba353376b4406b78c88bdd00e73eb5.png)

Use a Complete msg Debug node after it and copy/paste the complete msg here to see what http-in msg properties are preserved. It could be the case that the deb node replaces the `msg.payload.ip` with its returned data and that's why it was failing when you tried @E1cid 's example ?  
As Nick mentioned, you could get the same information from `msg.req.query.ip`

**Test Flow :**

```auto
[{"id":"47ecb492.c9102c","type":"http in","z":"223ced0e.462472","name":"","url":"/hello-json","method":"get","upload":false,"swaggerDoc":"","x":260,"y":260,"wires":[["763000fc934138c3"]]},{"id":"2d66c3bc.4e255c","type":"http response","z":"223ced0e.462472","name":"","statusCode":"","headers":{"content-type":"application/json"},"x":770,"y":260,"wires":[]},{"id":"763000fc934138c3","type":"DataOut","z":"223ced0e.462472","collection":"c9de68d4.2e9f18","name":"deb","path":"/","error":true,"x":430,"y":260,"wires":[["e4f767caf4639011","38f079a43a66df1d"]]},{"id":"e4f767caf4639011","type":"debug","z":"223ced0e.462472","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":530,"y":200,"wires":[]},{"id":"38f079a43a66df1d","type":"function","z":"223ced0e.462472","name":"filter","func":"// get the url param from req if payload is destroyed by Deb result\nlet ip = msg.req.query.ip;\n\n\nif (Array.isArray(msg.payload)) {\n msg.payload = msg.payload.filter(el => el.ip === ip)\n}\n\nelse {\n msg.payload = { error: \"No data available\" }\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":590,"y":260,"wires":[["2d66c3bc.4e255c"]]},{"id":"c9de68d4.2e9f18","type":"json-db-collection","name":"deb","collection":"deb","save":true}]

```

---

<div class="post-metadata">

**Author:** ![blob](https://avatars.discourse-cdn.com/v4/letter/b/ebca7d/32.png) [@blob](https://discourse.nodered.org/u/blob)\
**Post date:** [7 December 2021 05:31 UTC](https://discourse.nodered.org/t/unable-to-query-json-data-from-http-request/54707/25 "2021-12-07T05:31:36Z")

</div>

I did as you said and got the following -

![complete msg](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/b/4b2d9d40626020edb238b8ad26ddfeb0e8f3592f.jpeg)

![ip error](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/3/e39f81c89cc653c3b939a7fc196ab2ff1d862e37.jpeg)

PS - I also tried by querying Mysql db as you suggested earlier and it did the trick. But as I said I don't want node red or the react server to query every now and then through the entire historical data as well as the present data to refresh the contents on the React app.

Our React app will be a live page similar to what people have done with the covid data represented making use of covid data related API's and React. Our aim is to get the latest data from the solar field and the dishes to detect any errors the dishes might be encountering or the health status of the dishes.

Thanks.

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [7 December 2021 05:37 UTC](https://discourse.nodered.org/t/unable-to-query-json-data-from-http-request/54707/26 "2021-12-07T05:37:43Z")

</div>

> [@blob](#):
>
> I also tried by querying Mysql db as you suggested earlier and it did the trick. But as I said I don't want node red or the react server to query every now and then through the entire historical data

indeed you are right .. i was thinking about it too when you said thats it was 2 year of data

* * *

.. i see that msg.payload is a JSON string  
you need to use a JSON node after deb node to make it into a JS Object

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/5/4590ad51e14217ec7717f6060ac5c43ae4f6984e.png)

```auto
[{"id":"47ecb492.c9102c","type":"http in","z":"223ced0e.462472","name":"","url":"/hello-json","method":"get","upload":false,"swaggerDoc":"","x":260,"y":260,"wires":[["763000fc934138c3"]]},{"id":"2d66c3bc.4e255c","type":"http response","z":"223ced0e.462472","name":"","statusCode":"","headers":{"content-type":"application/json"},"x":910,"y":260,"wires":[]},{"id":"763000fc934138c3","type":"DataOut","z":"223ced0e.462472","collection":"c9de68d4.2e9f18","name":"deb","path":"/","error":true,"x":430,"y":260,"wires":[["e4f767caf4639011","10da9c322ec6a1f8"]]},{"id":"e4f767caf4639011","type":"debug","z":"223ced0e.462472","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":530,"y":200,"wires":[]},{"id":"38f079a43a66df1d","type":"function","z":"223ced0e.462472","name":"filter","func":"// get the url param from req if payload is destroyed by Deb result\nlet ip = msg.req.query.ip;\n\n\nif (Array.isArray(msg.payload)) {\n msg.payload = msg.payload.filter(el => el.ip === ip)\n}\n\nelse {\n msg.payload = { error: \"No data available\" }\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":730,"y":260,"wires":[["2d66c3bc.4e255c"]]},{"id":"10da9c322ec6a1f8","type":"json","z":"223ced0e.462472","name":"","property":"payload","action":"","pretty":false,"x":570,"y":260,"wires":[["38f079a43a66df1d"]]},{"id":"c9de68d4.2e9f18","type":"json-db-collection","name":"deb","collection":"deb","save":true}]

```

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [7 December 2021 08:09 UTC](https://discourse.nodered.org/t/unable-to-query-json-data-from-http-request/54707/27 "2021-12-07T08:09:10Z")

</div>

> [@UnborN](#):
>
> eplaces the `msg.payload.ip` with its returned data and that's why it was failing when you tried @E1cid 's example ?

Not the reason it failed as msg.payload.ip should exist in my example, as seen in image below.

 ![Screenshot_20211207-080315](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/f/5fef417855d8a7adbf543aca827c193dc8f5846b.png)  
The reason it failed is that, at that time the unkown deb node the OP added to the example over wrote msg.payload.

And my second example used msg.req.params.json\_hello which is also correct as it used the http in node :name convention

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [7 December 2021 08:25 UTC](https://discourse.nodered.org/t/unable-to-query-json-data-from-http-request/54707/28 "2021-12-07T08:25:58Z")

</div>

> [@E1cid](#):
>
> Not the reason it failed as msg.payload.ip should exist in my example

I know .. your examples were correct .. i meant, we didnt know how @blob merged the logic from them into his flow with that json db node

---

<div class="post-metadata">

**Author:** ![blob](https://avatars.discourse-cdn.com/v4/letter/b/ebca7d/32.png) [@blob](https://discourse.nodered.org/u/blob)\
**Post date:** [7 December 2021 10:19 UTC](https://discourse.nodered.org/t/unable-to-query-json-data-from-http-request/54707/29 "2021-12-07T10:19:46Z")

</div>

@UnborN I tired your flow and it did what was expected. Thanks a lot. 👍

 ![Success](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/b/fba041f358da6174c90898e2f05ae8aa8988661f.jpeg)

![complet_msg](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/f/ff1d29db32f89875a33b2bd3a583a6e364ff788e.jpeg)

@E1cid Thank you for your solution also, it's just that I may have not implemented it correctly. From the very beginning of this discussion you were consistently on it.

@knolleary Thank you for taking part in this discussion and providing your valuable inputs. Your participation in this discussion also gave me an opportunity to have some interaction with you. I have always admired you as the mentor/creator of Node Red and I have high regards for you.

Thank you all.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [21 December 2021 10:19 UTC](https://discourse.nodered.org/t/unable-to-query-json-data-from-http-request/54707/30 "2021-12-21T10:19:56Z")

</div>

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

[Previous page](https://discourse.nodered.org/t/unable-to-query-json-data-from-http-request/54707.md?page=1)