Nested Array from MySQL Statement

General
1

Hi,

I have searched & read through some of the topics on here to figure this out before posting, however I haven't found anything to help me solve this. Prior to looking on here I have made several attempts to work this out and none came close to what I want to achieve therefore not even worth posting here.

Basically I have a MariaDB table that is being populated daily from an external source with booking details for multiple rooms. I'm running an SQL query to extract data based on date and using a portion of phone numbers as a pin code to unlock the door of the booked room.

I would like to manipulate the way the data is presented to have the pin code nested within the array. The attached image shows the query result showing the pin as the last item. I'm not sure whether this is best to be done in the query itself or using a function/change node from the output of the database.

This is the query:
t = "SELECT Concat(firstname, ' ', lastname) AS 'name',bookings.room AS 'roomaccess',bookings.starttime AS 'timefrom',bookings.endtime AS 'timeto',bookings.mode AS 'mode',bookings.daysofweek AS 'daysofweek',bookings.twofactor AS 'twofactor',bookings.code AS 'pin' FROM bookings WHERE startdate LIKE '"+msg.payload+"'"
return {topic:t}

The +msg.payload+ is a date being fed into the query.

database-output
database-output520Ă—773 39.6 KB

I'm hoping someone can assist with this as it sounds so simple but I haven't had any joy with it.

Thank you!

2

Can you show an example of what you are looking for in the output ?
The query will output an array of objects, nothing you can change in the query, but you can manipulate after the query.

3

Thanks for the prompt response...
I meant to include this but forgot. This is something similar to what I'm aiming for, I'd like the 'pin' to be nested beneath 'id'. Ignore the rfid's as I won't be using those for the moment.

database-output-2
database-output-2543Ă—809 35.4 KB

4

If i understand correctly, try this example

[{"id":"48fe1953.6ae07","type":"inject","z":"b779de97.b1b46","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"name\":\"tom field\",\"roomaccess\":\"room 4\",\"timefrom\":\"18:00\",\"timeto\":\"00:00\",\"mode\":\"active\",\"dayofweek\":\"0123456\",\"pin\":\"12345\"},{\"name\":\"tom field\",\"roomaccess\":\"room 4\",\"timefrom\":\"18:00\",\"timeto\":\"00:00\",\"mode\":\"active\",\"dayofweek\":\"0123456\",\"pin\":\"12345\"},{\"name\":\"tom field\",\"roomaccess\":\"room 4\",\"timefrom\":\"18:00\",\"timeto\":\"00:00\",\"mode\":\"active\",\"dayofweek\":\"0123456\",\"pin\":\"12345\"}]","payloadType":"json","x":90,"y":4360,"wires":[["860122fb.32e13"]]},{"id":"860122fb.32e13","type":"change","z":"b779de97.b1b46","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload.$merge([$sift($, function($v, $k){\t$k != \"pin\"\t}),{\"id\":[{\"pin\":$.pin}]}])","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":270,"y":4360,"wires":[["94229627.6ff1d"]]},{"id":"94229627.6ff1d","type":"debug","z":"b779de97.b1b46","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":500,"y":4360,"wires":[]}]
1 Like
5

@E1cid Thank you! This is exactly what I was looking for. It would have taken me forever to work out the code in the change node :clap:

6

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