Join - Not working to plan

General
1

I have a flow calling data from a postgresql.

The table have two field - (sent SERIAL PRIMARY KEY, sentype jsonb);

I currently have only two entries.

The call to the DB I receive the array containing all the data.

object
payload: array[2]
0: object
sent: 1
sentype: object
sencom: "Digital matters Oyster"
verfware: "0.0.1"
verhware: "0.0.1"
1: object
sent: 2
sentype: object
sencom: "Abeeway Compact tracker"
verfware: "0.0.1"
verhware: "0.0.1"
topic: ""
_msgid: "e04b44b6059d1340"
pgsql: object
parts: object
complete: true

I split and re arrange the data, here I check the data and both msg looks good and the msg.parts is still there

msg1

object
payload: object
sent: 1
Sensor_Type: 1
sencom: "Digital matters Oyster"
verfware: "0.0.1"
verhware: "0.0.1"
topic: ""
parts: object
parts: object
id: "c886b5c1c58cd1ce"
type: "array"
count: 2
len: 1
index: 0
complete: true
_msgid: "25d6f83353f791e3"

msg2

object
payload: object
sent: 2
Sensor_Type: 2
sencom: "Abeeway Compact tracker"
verfware: "0.0.1"
verhware: "0.0.1"
topic: ""
parts: object
parts: object
id: "c886b5c1c58cd1ce"
type: "array"
count: 2
len: 1
index: 1
complete: true
_msgid: "56bca7b166b935a3"

Now I join them, I get two msg. returned, the firs one is exactly like msg1 above and the other one is a array with msg.payload.0 : undefined.

Any ideas pointers will be appreciated.

PS. Installed this copy of Node-Red 4-5 days ago.

msg1 join

object
payload: array[1]
0: object
sent: 1
Sensor_Type: 1
sencom: "Digital matters Oyster"
verfware: "0.0.1"
verhware: "0.0.1"
topic: ""
parts: object
id: 0.023315470394176918
type: "array"
index: 0
count: 1
_msgid: "25d6f83353f791e3

Join msg

object
payload: array[2]
0: undefined
1: object
sent: 2
Sensor_Type: 2
sencom: "Abeeway Compact tracker"
verfware: "0.0.1"
verhware: "0.0.1"
topic: ""
parts: object
id: 0.023315470394176918
type: "array"
index: 0
count: 1
_msgid: "56bca7b166b935a3"

image
image854×150 18.3 KB 

[
    {
        "id": "a2b7e605e4a2f886",
        "type": "postgresql",
        "z": "8db895d9ab727bbc",
        "name": "SELECT * FROM sentype;",
        "query": "SELECT * FROM sentype;",
        "postgreSQLConfig": "4705b0c3b6edca45",
        "split": true,
        "rowsPerMsg": "100",
        "outputs": 1,
        "x": 420,
        "y": 500,
        "wires": [
            [
                "f759a08192ecfd57",
                "ea3287d50431f64d"
            ]
        ]
    },
    {
        "id": "5754f606bf6da040",
        "type": "ui_table",
        "z": "8db895d9ab727bbc",
        "group": "ef81eae920809f6a",
        "name": "Table Sensor Type",
        "order": 1,
        "width": "27",
        "height": "7",
        "columns": [],
        "outputs": 0,
        "cts": false,
        "x": 1130,
        "y": 500,
        "wires": []
    },
    {
        "id": "a4166c394c601307",
        "type": "inject",
        "z": "8db895d9ab727bbc",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": true,
        "onceDelay": "2",
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 190,
        "y": 500,
        "wires": [
            [
                "a2b7e605e4a2f886"
            ]
        ]
    },
    {
        "id": "54dd39ca851824ec",
        "type": "debug",
        "z": "8db895d9ab727bbc",
        "name": "Split",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": true,
        "complete": "true",
        "targetType": "full",
        "statusVal": "payload",
        "statusType": "auto",
        "x": 710,
        "y": 620,
        "wires": []
    },
    {
        "id": "76dc74a888212fb1",
        "type": "change",
        "z": "8db895d9ab727bbc",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "payload.Sensor_Type",
                "pt": "msg",
                "to": "payload.sent",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "payload.sencom",
                "pt": "msg",
                "to": "payload.sentype.sencom",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "payload.verfware",
                "pt": "msg",
                "to": "payload.sentype.verfware",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "payload.verhware",
                "pt": "msg",
                "to": "payload.sentype.verfware",
                "tot": "msg"
            },
            {
                "t": "delete",
                "p": "pgsql",
                "pt": "msg"
            },
            {
                "t": "delete",
                "p": "payload.sentype",
                "pt": "msg"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 780,
        "y": 500,
        "wires": [
            [
                "77ddc73dd06e13b9",
                "0baf89e4943caefb"
            ]
        ]
    },
    {
        "id": "f759a08192ecfd57",
        "type": "split",
        "z": "8db895d9ab727bbc",
        "name": "",
        "splt": "\\n",
        "spltType": "str",
        "arraySplt": 1,
        "arraySpltType": "len",
        "stream": false,
        "addname": "",
        "x": 610,
        "y": 500,
        "wires": [
            [
                "76dc74a888212fb1",
                "54dd39ca851824ec"
            ]
        ]
    },
    {
        "id": "77ddc73dd06e13b9",
        "type": "join",
        "z": "8db895d9ab727bbc",
        "name": "",
        "mode": "auto",
        "build": "object",
        "property": "payload",
        "propertyType": "msg",
        "key": "topic",
        "joiner": "\\n",
        "joinerType": "str",
        "accumulate": true,
        "timeout": "",
        "count": "",
        "reduceRight": false,
        "reduceExp": "",
        "reduceInit": "",
        "reduceInitType": "num",
        "reduceFixup": "",
        "x": 950,
        "y": 500,
        "wires": [
            [
                "5754f606bf6da040",
                "0b95d4112f56a078"
            ]
        ]
    },
    {
        "id": "0baf89e4943caefb",
        "type": "debug",
        "z": "8db895d9ab727bbc",
        "name": "Rules",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": true,
        "complete": "true",
        "targetType": "full",
        "statusVal": "payload",
        "statusType": "auto",
        "x": 790,
        "y": 560,
        "wires": []
    },
    {
        "id": "0b95d4112f56a078",
        "type": "debug",
        "z": "8db895d9ab727bbc",
        "name": "Join",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": true,
        "complete": "true",
        "targetType": "full",
        "statusVal": "payload",
        "statusType": "auto",
        "x": 1010,
        "y": 560,
        "wires": []
    },
    {
        "id": "ea3287d50431f64d",
        "type": "debug",
        "z": "8db895d9ab727bbc",
        "name": "SELECT * FROM sentype;",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": true,
        "complete": "true",
        "targetType": "full",
        "statusVal": "payload",
        "statusType": "auto",
        "x": 480,
        "y": 560,
        "wires": []
    },
    {
        "id": "4705b0c3b6edca45",
        "type": "postgreSQLConfig",
        "name": "",
        "host": "139.162.239.60",
        "hostFieldType": "str",
        "port": "5432",
        "portFieldType": "num",
        "database": "soiot_db",
        "databaseFieldType": "str",
        "ssl": "false",
        "sslFieldType": "bool",
        "max": "10",
        "maxFieldType": "num",
        "min": "1",
        "minFieldType": "num",
        "idle": "1000",
        "idleFieldType": "num",
        "connectionTimeout": "10000",
        "connectionTimeoutFieldType": "num",
        "user": "soiot_backend_user",
        "userFieldType": "str",
        "password": "zxcvbasdfgqwert",
        "passwordFieldType": "str"
    },
    {
        "id": "ef81eae920809f6a",
        "type": "ui_group",
        "name": "Sensor Type",
        "tab": "22a6f54d87b61515",
        "order": 2,
        "disp": true,
        "width": "36",
        "collapse": false,
        "className": ""
    },
    {
        "id": "22a6f54d87b61515",
        "type": "ui_tab",
        "name": "DB Managment",
        "icon": "dashboard",
        "order": 1,
        "disabled": false,
        "hidden": false
    }
]
2

Hello Johan,

Would it possible to share the data coming from the database again ?
The way you shared it makes it impossible to see the full structure of the message or copy it in order to replicate the flow.

The proper way of sharing data from a msg is hovering over its top level in the Debug window and
using the Copy value button so the whole msg is copied with all of its sub-elements.

image

3

I know that thanks..... but for some reason I cannot copy any thigh for the debug pain. (Complete msg object, path or value, something to do with this PC of mine, the node-red instance is on a linode instance, so the bug with the flow is not related as it is two different machines)

4

Have you updated node-red recently? This is a known issue (due to be fixed in next release) if you are accessing an node-red in a browser via non HTTPS and not localhost

e.g. if you are using same computer, debug copy will work for http://localhost:1880 but not http://192.168.0.2:1880 - or if remote, then https://192.168.0.2:1880 will work.

5

Installed a couple of days back

Accessing via browser "http://xx.58.xx.xx:1880"

PS: Will soon update to https

6

After you have split and joined your messages - you have to add a join node as message/key object. As key you can take your sensor_type or sent property.

Especially it is important - that I guess you want to update a row in your table for each sensor. Otherwise if not and you will only an increasing table for each message than add each message to an existing array and send it once.

Afterwards you need to create the array for your ui_table.

So I tried to put your 2 msgs in to an inject node to simulate your flow as I have not the same systems

So first - I ignore your changes in your object. Your first pair split/join is only for modifying your data - but you get the same objects as directly from your database request.

So it has no meaning how to get into a combined object,

As I said I usen your posted msg1 and 2 to simulate the output.

YOu have the to produce a combined object for each sensor - I used payload.Sensor_Type as property for the key in the topic. Then you have to split the combined object again and produce an array.

Here the flow .- you have to add and put it after your join node:

image
image849×156 19 KB

image
image1300×686 36.3 KB

Zusammenfassung 
[{"id":"68bd54fe01cf87cc","type":"inject","z":"cb05e14b.326a9","name":"msg1","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"sent\":1,\"Sensor_Type\":1,\"sencom\":\"Digital matters Oyster\",\"verfware\":\"0.0.1\",\"verhware\":\"0.0.1\",\"id\":\"c886b5c1c58cd1ce\",\"type\":\"array\",\"count\":2,\"len\":1,\"complete\":true}","payloadType":"json","x":530,"y":2120,"wires":[["2d31a1e0d6feee3b","7b560b7094fde108"]]},{"id":"724c48224d836b43","type":"inject","z":"cb05e14b.326a9","name":"msg2","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"sent\":2,\"Sensor_Type\":2,\"sencom\":\"Abeeway Compact tracker\",\"verfware\":\"0.0.1\",\"verhware\":\"0.0.1\",\"topic\":\"\",\"id\":\"c886b5c1c58cd1ce\",\"type\":\"array\",\"count\":2,\"len\":1,\"index\":1,\"complete\":true}","payloadType":"json","x":530,"y":2160,"wires":[["93173fdc3ced0fd3","7b560b7094fde108"]]},{"id":"2d31a1e0d6feee3b","type":"debug","z":"cb05e14b.326a9","name":"msg1","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":690,"y":2060,"wires":[]},{"id":"93173fdc3ced0fd3","type":"debug","z":"cb05e14b.326a9","name":"msg2","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":690,"y":2240,"wires":[]},{"id":"7b560b7094fde108","type":"join","z":"cb05e14b.326a9","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"payload.Sensor_Type","joiner":"\\n","joinerType":"str","accumulate":true,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":730,"y":2140,"wires":[["90d496b19bb13901","7c70a37bc0049585"]]},{"id":"90d496b19bb13901","type":"debug","z":"cb05e14b.326a9","name":"combined","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":920,"y":2060,"wires":[]},{"id":"7c70a37bc0049585","type":"split","z":"cb05e14b.326a9","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":890,"y":2140,"wires":[["9cc0cef177469fa4"]]},{"id":"9cc0cef177469fa4","type":"join","z":"cb05e14b.326a9","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":1050,"y":2140,"wires":[["f76bf202607ee879"]]},{"id":"f76bf202607ee879","type":"debug","z":"cb05e14b.326a9","name":"array for ui_table","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1260,"y":2060,"wires":[]}]
7

Thank for your reply.

This follow works fine if you have a know array size.

I am doing a call from a DB and it is constantly growing, new data ingested by it by the minute.

So the manual join with output after know amount of msg. will not work.

8

If it is. Instantly growing then it is even easier. You need only add each object to an array in a flow variable or an array in the context of a function node using push method, or use the add command for each object as described in the help of the ui_table.Node

Even with small modifications you can use the flow - even without knowing the number of rows.

[{"id":"68bd54fe01cf87cc","type":"inject","z":"cb05e14b.326a9","name":"msg1","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"sent\":1,\"Sensor_Type\":1,\"sencom\":\"Digital matters Oyster\",\"verfware\":\"0.0.1\",\"verhware\":\"0.0.1\",\"id\":\"c886b5c1c58cd1ce\",\"type\":\"array\",\"count\":2,\"len\":1,\"complete\":true}","payloadType":"json","x":290,"y":1820,"wires":[["2d31a1e0d6feee3b","7b560b7094fde108"]]},{"id":"724c48224d836b43","type":"inject","z":"cb05e14b.326a9","name":"msg2","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"sent\":2,\"Sensor_Type\":2,\"sencom\":\"Abeeway Compact tracker\",\"verfware\":\"0.0.1\",\"verhware\":\"0.0.1\",\"topic\":\"\",\"id\":\"c886b5c1c58cd1ce\",\"type\":\"array\",\"count\":2,\"len\":1,\"index\":1,\"complete\":true}","payloadType":"json","x":290,"y":1860,"wires":[["93173fdc3ced0fd3","7b560b7094fde108"]]},{"id":"2d31a1e0d6feee3b","type":"debug","z":"cb05e14b.326a9","name":"msg1","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":490,"y":1820,"wires":[]},{"id":"93173fdc3ced0fd3","type":"debug","z":"cb05e14b.326a9","name":"msg2","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":490,"y":2000,"wires":[]},{"id":"7b560b7094fde108","type":"join","z":"cb05e14b.326a9","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"payload.Sensor_Type","joiner":"\\n","joinerType":"str","accumulate":true,"timeout":"","count":"1","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":530,"y":1900,"wires":[["90d496b19bb13901","7c70a37bc0049585"]]},{"id":"90d496b19bb13901","type":"debug","z":"cb05e14b.326a9","name":"combined","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":720,"y":1820,"wires":[]},{"id":"7c70a37bc0049585","type":"split","z":"cb05e14b.326a9","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":690,"y":1900,"wires":[["9cc0cef177469fa4"]]},{"id":"9cc0cef177469fa4","type":"join","z":"cb05e14b.326a9","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":850,"y":1900,"wires":[["f76bf202607ee879"]]},{"id":"f76bf202607ee879","type":"debug","z":"cb05e14b.326a9","name":"array for ui_table","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1060,"y":1820,"wires":[]},{"id":"596d1be2830e4d5e","type":"inject","z":"cb05e14b.326a9","name":"msg3","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"sent\":3,\"Sensor_Type\":3,\"sencom\":\"Digital matters Oyster1\",\"verfware\":\"0.0.1\",\"verhware\":\"0.0.1\",\"id\":\"c886b5c1c58cd1ce\",\"type\":\"array\",\"count\":2,\"len\":1,\"complete\":true}","payloadType":"json","x":290,"y":1900,"wires":[["7b560b7094fde108"]]},{"id":"a2062de304374614","type":"inject","z":"cb05e14b.326a9","name":"msg4","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"sent\":4,\"Sensor_Type\":4,\"sencom\":\"Abeeway Compact tracker 4\",\"verfware\":\"0.0.1\",\"verhware\":\"0.0.1\",\"topic\":\"\",\"id\":\"c886b5c1c58cd1ce\",\"type\":\"array\",\"count\":2,\"len\":1,\"index\":1,\"complete\":true}","payloadType":"json","x":290,"y":1940,"wires":[["7b560b7094fde108"]]}]
9

Thanks,

But you need on the first call to know the amount of records, it is not dynamic.

10

Hi Johan .. Node-red 2.1.6 was released by the developers if you want to upgrade and copy a working sample msg coming from your db in order to replicate your flow.

11

Thanks, done upgrade, it's working now.

12

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