# I need help to find closest match from DB

**URL:** <https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176>\
**Category:** General\
**Created:** [29 December 2020 00:19 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176 "2020-12-29T00:19:37Z")\
**Posts on this page:** 11\
**Page:** 2

<div class="post-metadata">

**Author:** ![hotNipi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/hotnipi/32/383_2.png) [@hotNipi](https://discourse.nodered.org/u/hotNipi)\
**Post date:** [29 December 2020 18:19 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/21 "2020-12-29T18:19:06Z")

</div>

First result of google search gives

> <https://stackoverflow.com/questions/8584902/get-the-closest-number-out-of-an-array>

---

<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:** [29 December 2020 19:31 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/22 "2020-12-29T19:31:13Z")

</div>

Not sure exactly what your db array looks like, i may of missed it.

You where close with your reduce method. The prev and curr are probably objects so you would do something more like below. I have create a array that i think your db request should be able to return. goal can be set in msg.topic, in the inject node.

```auto
[{"id":"dc450f89.40902","type":"inject","z":"8d22ae29.7df6d","name":"","props":[{"p":"payload"},{"p":"topic","v":"26","vt":"num"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"hue\":10,\"value\":6},{\"hue\":20,\"value\":6.5},{\"hue\":30,\"value\":7},{\"hue\":30,\"value\":7.5},{\"hue\":40,\"value\":8},{\"hue\":50,\"value\":8.5},{\"hue\":60,\"value\":9},{\"hue\":80,\"value\":9.5},{\"hue\":80,\"value\":10}]","payloadType":"json","x":230,"y":2080,"wires":[["be043b0c.8a3898"]]},{"id":"be043b0c.8a3898","type":"function","z":"8d22ae29.7df6d","name":"","func":"let goal = msg.topic;\nmsg.payload = msg.payload.reduce((prev, curr) => Math.abs(prev.hue - goal) > Math.abs(curr.hue - goal) ? curr : prev )\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":380,"y":2080,"wires":[["dcd8ea98.f4e16"]]},{"id":"dcd8ea98.f4e16","type":"debug","z":"8d22ae29.7df6d","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":570,"y":2080,"wires":[]}]

```

also your `counts= [msg.payload.db]` is adding an outer array no need for []

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [29 December 2020 20:57 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/23 "2020-12-29T20:57:04Z")

</div>

I don't think @ajbuk21 actually wants the nearest number, he wants the index of the nearest number so that he can use it to get another attribute of the object at that position in the array. I don't think that `reduce` will do that, but I haven't investigated it thoroughly.

---

<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:** [29 December 2020 21:27 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/24 "2020-12-29T21:27:25Z")

</div>

The OP wants to look up the PH with the closest Hue. If he returns the hue along with with the PH from original DB request , he can then just search for closest hue and he will have the PH. Or he can retrun the closest hue only and then do another DB request with the hue to return the PH. The first approach would be simpler and only require one DB request. The reduce method will work.

Edit/ I think it would be possible(depending on DB) to do the look up for closest in the DB request also

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [29 December 2020 21:37 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/25 "2020-12-29T21:37:19Z")

</div>

Yes, that approach would work, but as I suggested, it would be more efficient to query both the hue and the ph together initially, then find the index with the nearest hue and then the ph is immediately available without another db query.  
Also a binary search of the (already sorted) array, or even a sequential search stopping when the nearest is found, is going to be more efficient than testing every element in the array as reduce() does.

---

<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:** [29 December 2020 21:41 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/26 "2020-12-29T21:41:51Z")

</div>

> [@Colin](#):
>
> suggested, it would be more efficient to query both the hue and the ph together initially, then find the index with the nearest hue and then the ph is immediately available without another db query.

Thats what i suggest when i say "return hue along with PH from original DB request"

But better, just to do the whole thing in db if possible.

> <https://stackoverflow.com/questions/7269243/how-to-get-nearest-value-from-database-in-mysql>

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [29 December 2020 22:06 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/27 "2020-12-29T22:06:25Z")

</div>

> [@E1cid](#):
>
> But better, just to do the whole thing in db if possible.

Agreed, I didn't realise you could do that.

---

<div class="post-metadata">

**Author:** ![ajbuk21](https://avatars.discourse-cdn.com/v4/letter/a/858c86/32.png) [@ajbuk21](https://discourse.nodered.org/u/ajbuk21)\
**Post date:** [29 December 2020 22:59 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/28 "2020-12-29T22:59:48Z")

</div>

YAY!! it works "ITS ALIVE" '"_ZAP OF LIGHTNING_"'

thank you Colin and E1cid you got me there in the end BIG THANKS

it would of been nice to get the DB to do the work, i did just try but i got was `syntax` errors??

![Untitled5](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/b/abdbbc849ddb6babf126f14d1c12d51d47422728.png)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [30 December 2020 07:16 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/29 "2020-12-30T07:16:56Z")

</div>

> [@ajbuk21](#):
>
> `syntax` errors??

Unless you show us the query you used and the error then it is impossible to comment.

---

<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:** [30 December 2020 08:47 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/30 "2020-12-30T08:47:33Z")

</div>

```auto
SELECT ABS(40 - hue) as nearest, value
FROM ph_results
ORDER BY nearest ASC
LIMIT 1

```

where 40 is the search value

Untested.

---

<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:** [13 January 2021 08:47 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/31 "2021-01-13T08:47:41Z")

</div>

Closed on 13 January 2021 08:47 UTC

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

[Previous page](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176.md?page=1)