Educating my self - some guidance asked

General
1

Hi,
trying to use JSON data instead of parsing them with PHP and storing them into mySQL, but I am stuck with getting "Fields have different lengths".
Below is my data. Can someone help me get this data into table like form?

I am able to get "id", state", but not anything under "stats". I believe it is due the fact that "stats" are not there if "state" is not "connected".

I get "id" as "*.id" and set query as JSONATA.

**************** START DATA *******************
{
"SrtReceivers": [
{},
{
"id": "95.166.22.151:35166->0.0.0.0:20203",
"state": "connected",
"retryCount": 2,
"stats": {}
},
{
"id": "95.166.22.151:57988->0.0.0.0:20204",
"state": "connected",
"retryCount": 2,
"stats": {
"time": 71488923,
"window": {
"flow": 8192,
"congestion": 8192,
"flight": 0
},
"link": {
"rtt": 3.804,
"mbpsBandwidth": 398.88,
"mbpsMaxBandwidth": 1250
},
"recv": {
"packetsReceived": 23226703,
"packetsReceivedRetransmitted": 2351,
"packetsLost": 3025,
"packetsDropped": 0,
"packetsBelated": 949,
"NAKsSent": 1564,
"bytesReceived": 31588316080,
"bytesLost": 4114000,
"bytesDropped": 0,
"mbpsRate": 3.5349
}
}
},
{
"id": "95.166.22.151:54740->0.0.0.0:20205",
"state": "connected",
"retryCount": 2,
"stats": {
"time": 75070998,
"window": {
"flow": 8192,
"congestion": 8192,
"flight": 0
},
"link": {
"rtt": 3.859,
"mbpsBandwidth": 336.6,
"mbpsMaxBandwidth": 1250
},
"recv": {
"packetsReceived": 29920947,
"packetsReceivedRetransmitted": 2667,
"packetsLost": 2886,
"packetsDropped": 0,
"packetsBelated": 951,
"NAKsSent": 1942,
"bytesReceived": 40692487920,
"bytesLost": 3924960,
"bytesDropped": 0,
"mbpsRate": 4.33643
}
}
},
{
"id": "95.166.22.100:52552->0.0.0.0:20207",
"state": "connected",
"retryCount": 4,
"stats": {
"time": 71488980,
"window": {
"flow": 8192,
"congestion": 8192,
"flight": 0
},
"link": {
"rtt": 3.2,
"mbpsBandwidth": 110.628,
"mbpsMaxBandwidth": 1250
},
"recv": {
"packetsReceived": 29797101,
"packetsReceivedRetransmitted": 30389,
"packetsLost": 31692,
"packetsDropped": 0,
"packetsBelated": 8556,
"NAKsSent": 22089,
"bytesReceived": 40524057360,
"bytesLost": 43101120,
"bytesDropped": 0,
"mbpsRate": 4.53486
}
}
},
{
"id": "95.166.22.100:60125->0.0.0.0:20208",
"state": "connected",
"retryCount": 2,
"stats": {
"time": 128933317,
"window": {
"flow": 8192,
"congestion": 8192,
"flight": 0
},
"link": {
"rtt": 3.371,
"mbpsBandwidth": 189.888,
"mbpsMaxBandwidth": 1250
},
"recv": {
"packetsReceived": 51017037,
"packetsReceivedRetransmitted": 59865,
"packetsLost": 63460,
"packetsDropped": 0,
"packetsBelated": 17558,
"NAKsSent": 42658,
"bytesReceived": 69383170320,
"bytesLost": 86305600,
"bytesDropped": 0,
"mbpsRate": 4.30506
}
}
},
{
"id": "95.166.22.151:39395->0.0.0.0:20206",
"state": "connected",
"retryCount": 4,
"stats": {
"time": 30142922,
"window": {
"flow": 8192,
"congestion": 8192,
"flight": 0
},
"link": {
"rtt": 4.027,
"mbpsBandwidth": 232.212,
"mbpsMaxBandwidth": 1000
},
"recv": {
"packetsReceived": 1631449,
"packetsReceivedRetransmitted": 110,
"packetsLost": 98,
"packetsDropped": 0,
"packetsBelated": 23,
"NAKsSent": 88,
"bytesReceived": 2218770640,
"bytesLost": 133280,
"bytesDropped": 0,
"mbpsRate": 0.588867
}
}
}
]
}

**************** END DATA *******************

2

Welcome to the community.

Could you please post code within code fences so that it is readable. Thanks.

3
4 
{
"SrtReceivers": [
{},
{
"id": "95.166.22.151:35166->0.0.0.0:20203",
"state": "connected",
"retryCount": 2,
"stats": {}
},
{
"id": "95.166.22.151:57988->0.0.0.0:20204",
"state": "connected",
"retryCount": 2,
"stats": {
"time": 71488923,
"window": {
"flow": 8192,
"congestion": 8192,
"flight": 0
},
"link": {
"rtt": 3.804,
"mbpsBandwidth": 398.88,
"mbpsMaxBandwidth": 1250
},
"recv": {
"packetsReceived": 23226703,
"packetsReceivedRetransmitted": 2351,
"packetsLost": 3025,
"packetsDropped": 0,
"packetsBelated": 949,
"NAKsSent": 1564,
"bytesReceived": 31588316080,
"bytesLost": 4114000,
"bytesDropped": 0,
"mbpsRate": 3.5349
}
}
},
{
"id": "95.166.22.151:54740->0.0.0.0:20205",
"state": "connected",
"retryCount": 2,
"stats": {
"time": 75070998,
"window": {
"flow": 8192,
"congestion": 8192,
"flight": 0
},
"link": {
"rtt": 3.859,
"mbpsBandwidth": 336.6,
"mbpsMaxBandwidth": 1250
},
"recv": {
"packetsReceived": 29920947,
"packetsReceivedRetransmitted": 2667,
"packetsLost": 2886,
"packetsDropped": 0,
"packetsBelated": 951,
"NAKsSent": 1942,
"bytesReceived": 40692487920,
"bytesLost": 3924960,
"bytesDropped": 0,
"mbpsRate": 4.33643
}
}
},
{
"id": "95.166.22.100:52552->0.0.0.0:20207",
"state": "connected",
"retryCount": 4,
"stats": {
"time": 71488980,
"window": {
"flow": 8192,
"congestion": 8192,
"flight": 0
},
"link": {
"rtt": 3.2,
"mbpsBandwidth": 110.628,
"mbpsMaxBandwidth": 1250
},
"recv": {
"packetsReceived": 29797101,
"packetsReceivedRetransmitted": 30389,
"packetsLost": 31692,
"packetsDropped": 0,
"packetsBelated": 8556,
"NAKsSent": 22089,
"bytesReceived": 40524057360,
"bytesLost": 43101120,
"bytesDropped": 0,
"mbpsRate": 4.53486
}
}
},
{
"id": "95.166.22.100:60125->0.0.0.0:20208",
"state": "connected",
"retryCount": 2,
"stats": {
"time": 128933317,
"window": {
"flow": 8192,
"congestion": 8192,
"flight": 0
},
"link": {
"rtt": 3.371,
"mbpsBandwidth": 189.888,
"mbpsMaxBandwidth": 1250
},
"recv": {
"packetsReceived": 51017037,
"packetsReceivedRetransmitted": 59865,
"packetsLost": 63460,
"packetsDropped": 0,
"packetsBelated": 17558,
"NAKsSent": 42658,
"bytesReceived": 69383170320,
"bytesLost": 86305600,
"bytesDropped": 0,
"mbpsRate": 4.30506
}
}
},
{
"id": "95.166.22.151:39395->0.0.0.0:20206",
"state": "connected",
"retryCount": 4,
"stats": {
"time": 30142922,
"window": {
"flow": 8192,
"congestion": 8192,
"flight": 0
},
"link": {
"rtt": 4.027,
"mbpsBandwidth": 232.212,
"mbpsMaxBandwidth": 1000
},
"recv": {
"packetsReceived": 1631449,
"packetsReceivedRetransmitted": 110,
"packetsLost": 98,
"packetsDropped": 0,
"packetsBelated": 23,
"NAKsSent": 88,
"bytesReceived": 2218770640,
"bytesLost": 133280,
"bytesDropped": 0,
"mbpsRate": 0.588867
}
}
}
]
}
5

Please post the complete flow.

6 
{
  "SrtReceivers": [
    {},
    {
      "id": "95.166.22.151:35166->0.0.0.0:20203",
      "state": "connected",
      "retryCount": 2,
      "stats": {}
    },
    {
      "id": "95.166.22.151:57988->0.0.0.0:20204",
      "state": "connected",
      "retryCount": 2,
      "stats": {
        "time": 71488923,
        "window": {
          "flow": 8192,
          "congestion": 8192,
          "flight": 0
        },
        "link": {
          "rtt": 3.804,
          "mbpsBandwidth": 398.88,
          "mbpsMaxBandwidth": 1250
        },
        "recv": {
          "packetsReceived": 23226703,
          "packetsReceivedRetransmitted": 2351,
          "packetsLost": 3025,
          "packetsDropped": 0,
          "packetsBelated": 949,
          "NAKsSent": 1564,
          "bytesReceived": 31588316080,
          "bytesLost": 4114000,
          "bytesDropped": 0,
          "mbpsRate": 3.5349
        }
      }
    },
    {
      "id": "95.166.22.151:54740->0.0.0.0:20205",
      "state": "connected",
      "retryCount": 2,
      "stats": {
        "time": 75070998,
        "window": {
          "flow": 8192,
          "congestion": 8192,
          "flight": 0
        },
        "link": {
          "rtt": 3.859,
          "mbpsBandwidth": 336.6,
          "mbpsMaxBandwidth": 1250
        },
        "recv": {
          "packetsReceived": 29920947,
          "packetsReceivedRetransmitted": 2667,
          "packetsLost": 2886,
          "packetsDropped": 0,
          "packetsBelated": 951,
          "NAKsSent": 1942,
          "bytesReceived": 40692487920,
          "bytesLost": 3924960,
          "bytesDropped": 0,
          "mbpsRate": 4.33643
        }
      }
    },
    {
      "id": "95.166.22.100:52552->0.0.0.0:20207",
      "state": "connected",
      "retryCount": 4,
      "stats": {
        "time": 71488980,
        "window": {
          "flow": 8192,
          "congestion": 8192,
          "flight": 0
        },
        "link": {
          "rtt": 3.2,
          "mbpsBandwidth": 110.628,
          "mbpsMaxBandwidth": 1250
        },
        "recv": {
          "packetsReceived": 29797101,
          "packetsReceivedRetransmitted": 30389,
          "packetsLost": 31692,
          "packetsDropped": 0,
          "packetsBelated": 8556,
          "NAKsSent": 22089,
          "bytesReceived": 40524057360,
          "bytesLost": 43101120,
          "bytesDropped": 0,
          "mbpsRate": 4.53486
        }
      }
    },
    {
      "id": "95.166.22.100:60125->0.0.0.0:20208",
      "state": "connected",
      "retryCount": 2,
      "stats": {
        "time": 128933317,
        "window": {
          "flow": 8192,
          "congestion": 8192,
          "flight": 0
        },
        "link": {
          "rtt": 3.371,
          "mbpsBandwidth": 189.888,
          "mbpsMaxBandwidth": 1250
        },
        "recv": {
          "packetsReceived": 51017037,
          "packetsReceivedRetransmitted": 59865,
          "packetsLost": 63460,
          "packetsDropped": 0,
          "packetsBelated": 17558,
          "NAKsSent": 42658,
          "bytesReceived": 69383170320,
          "bytesLost": 86305600,
          "bytesDropped": 0,
          "mbpsRate": 4.30506
        }
      }
    },
    {
      "id": "95.166.22.151:39395->0.0.0.0:20206",
      "state": "connected",
      "retryCount": 4,
      "stats": {
        "time": 30142922,
        "window": {
          "flow": 8192,
          "congestion": 8192,
          "flight": 0
        },
        "link": {
          "rtt": 4.027,
          "mbpsBandwidth": 232.212,
          "mbpsMaxBandwidth": 1000
        },
        "recv": {
          "packetsReceived": 1631449,
          "packetsReceivedRetransmitted": 110,
          "packetsLost": 98,
          "packetsDropped": 0,
          "packetsBelated": 23,
          "NAKsSent": 88,
          "bytesReceived": 2218770640,
          "bytesLost": 133280,
          "bytesDropped": 0,
          "mbpsRate": 0.588867
        }
      }
    }
  ]
}
7 
{
"SrtReceivers": [
{},
{
"id": "95.166.22.151:35166->0.0.0.0:20203",
"state": "connected",
"retryCount": 2,
"stats": {}
},
{
"id": "95.166.22.151:57988->0.0.0.0:20204",
"state": "connected",
"retryCount": 2,
"stats": {
"time": 71488923,
"window": {
"flow": 8192,
"congestion": 8192,
"flight": 0
},
"link": {
"rtt": 3.804,
"mbpsBandwidth": 398.88,
"mbpsMaxBandwidth": 1250
},
"recv": {
"packetsReceived": 23226703,
"packetsReceivedRetransmitted": 2351,
"packetsLost": 3025,
"packetsDropped": 0,
"packetsBelated": 949,
"NAKsSent": 1564,
"bytesReceived": 31588316080,
"bytesLost": 4114000,
"bytesDropped": 0,
"mbpsRate": 3.5349
}
}
},
{
"id": "95.166.22.151:54740->0.0.0.0:20205",
"state": "connected",
"retryCount": 2,
"stats": {
"time": 75070998,
"window": {
"flow": 8192,
"congestion": 8192,
"flight": 0
},
"link": {
"rtt": 3.859,
"mbpsBandwidth": 336.6,
"mbpsMaxBandwidth": 1250
},
"recv": {
"packetsReceived": 29920947,
"packetsReceivedRetransmitted": 2667,
"packetsLost": 2886,
"packetsDropped": 0,
"packetsBelated": 951,
"NAKsSent": 1942,
"bytesReceived": 40692487920,
"bytesLost": 3924960,
"bytesDropped": 0,
"mbpsRate": 4.33643
}
}
},
{
"id": "95.166.22.100:52552->0.0.0.0:20207",
"state": "connected",
"retryCount": 4,
"stats": {
"time": 71488980,
"window": {
"flow": 8192,
"congestion": 8192,
"flight": 0
},
"link": {
"rtt": 3.2,
"mbpsBandwidth": 110.628,
"mbpsMaxBandwidth": 1250
},
"recv": {
"packetsReceived": 29797101,
"packetsReceivedRetransmitted": 30389,
"packetsLost": 31692,
"packetsDropped": 0,
"packetsBelated": 8556,
"NAKsSent": 22089,
"bytesReceived": 40524057360,
"bytesLost": 43101120,
"bytesDropped": 0,
"mbpsRate": 4.53486
}
}
},
{
"id": "95.166.22.100:60125->0.0.0.0:20208",
"state": "connected",
"retryCount": 2,
"stats": {
"time": 128933317,
"window": {
"flow": 8192,
"congestion": 8192,
"flight": 0
},
"link": {
"rtt": 3.371,
"mbpsBandwidth": 189.888,
"mbpsMaxBandwidth": 1250
},
"recv": {
"packetsReceived": 51017037,
"packetsReceivedRetransmitted": 59865,
"packetsLost": 63460,
"packetsDropped": 0,
"packetsBelated": 17558,
"NAKsSent": 42658,
"bytesReceived": 69383170320,
"bytesLost": 86305600,
"bytesDropped": 0,
"mbpsRate": 4.30506
}
}
},
{
"id": "95.166.22.151:39395->0.0.0.0:20206",
"state": "connected",
"retryCount": 4,
"stats": {
"time": 30142922,
"window": {
"flow": 8192,
"congestion": 8192,
"flight": 0
},
"link": {
"rtt": 4.027,
"mbpsBandwidth": 232.212,
"mbpsMaxBandwidth": 1000
},
"recv": {
"packetsReceived": 1631449,
"packetsReceivedRetransmitted": 110,
"packetsLost": 98,
"packetsDropped": 0,
"packetsBelated": 23,
"NAKsSent": 88,
"bytesReceived": 2218770640,
"bytesLost": 133280,
"bytesDropped": 0,
"mbpsRate": 0.588867
}
}
}
]
}
8

Your question is vague and offers no format of the output you require, or which object properties you want to view in a table.

Here is an example that will get you started.
for more info search angular ng-repeat.

[{"id":"309f3c05.abf754","type":"inject","z":"c791cbc0.84f648","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{   \"SrtReceivers\": [     {},     {       \"id\": \"95.166.22.151:35166->0.0.0.0:20203\",       \"state\": \"connected\",       \"retryCount\": 2,       \"stats\": {}     },     {       \"id\": \"95.166.22.151:57988->0.0.0.0:20204\",       \"state\": \"connected\",       \"retryCount\": 2,       \"stats\": {         \"time\": 71488923,         \"window\": {           \"flow\": 8192,           \"congestion\": 8192,           \"flight\": 0         },         \"link\": {           \"rtt\": 3.804,           \"mbpsBandwidth\": 398.88,           \"mbpsMaxBandwidth\": 1250         },         \"recv\": {           \"packetsReceived\": 23226703,           \"packetsReceivedRetransmitted\": 2351,           \"packetsLost\": 3025,           \"packetsDropped\": 0,           \"packetsBelated\": 949,           \"NAKsSent\": 1564,           \"bytesReceived\": 31588316080,           \"bytesLost\": 4114000,           \"bytesDropped\": 0,           \"mbpsRate\": 3.5349         }       }     },     {       \"id\": \"95.166.22.151:54740->0.0.0.0:20205\",       \"state\": \"connected\",       \"retryCount\": 2,       \"stats\": {         \"time\": 75070998,         \"window\": {           \"flow\": 8192,           \"congestion\": 8192,           \"flight\": 0         },         \"link\": {           \"rtt\": 3.859,           \"mbpsBandwidth\": 336.6,           \"mbpsMaxBandwidth\": 1250         },         \"recv\": {           \"packetsReceived\": 29920947,           \"packetsReceivedRetransmitted\": 2667,           \"packetsLost\": 2886,           \"packetsDropped\": 0,           \"packetsBelated\": 951,           \"NAKsSent\": 1942,           \"bytesReceived\": 40692487920,           \"bytesLost\": 3924960,           \"bytesDropped\": 0,           \"mbpsRate\": 4.33643         }       }     },     {       \"id\": \"95.166.22.100:52552->0.0.0.0:20207\",       \"state\": \"connected\",       \"retryCount\": 4,       \"stats\": {         \"time\": 71488980,         \"window\": {           \"flow\": 8192,           \"congestion\": 8192,           \"flight\": 0         },         \"link\": {           \"rtt\": 3.2,           \"mbpsBandwidth\": 110.628,           \"mbpsMaxBandwidth\": 1250         },         \"recv\": {           \"packetsReceived\": 29797101,           \"packetsReceivedRetransmitted\": 30389,           \"packetsLost\": 31692,           \"packetsDropped\": 0,           \"packetsBelated\": 8556,           \"NAKsSent\": 22089,           \"bytesReceived\": 40524057360,           \"bytesLost\": 43101120,           \"bytesDropped\": 0,           \"mbpsRate\": 4.53486         }       }     },     {       \"id\": \"95.166.22.100:60125->0.0.0.0:20208\",       \"state\": \"connected\",       \"retryCount\": 2,       \"stats\": {         \"time\": 128933317,         \"window\": {           \"flow\": 8192,           \"congestion\": 8192,           \"flight\": 0         },         \"link\": {           \"rtt\": 3.371,           \"mbpsBandwidth\": 189.888,           \"mbpsMaxBandwidth\": 1250         },         \"recv\": {           \"packetsReceived\": 51017037,           \"packetsReceivedRetransmitted\": 59865,           \"packetsLost\": 63460,           \"packetsDropped\": 0,           \"packetsBelated\": 17558,           \"NAKsSent\": 42658,           \"bytesReceived\": 69383170320,           \"bytesLost\": 86305600,           \"bytesDropped\": 0,           \"mbpsRate\": 4.30506         }       }     },     {       \"id\": \"95.166.22.151:39395->0.0.0.0:20206\",       \"state\": \"connected\",       \"retryCount\": 4,       \"stats\": {         \"time\": 30142922,         \"window\": {           \"flow\": 8192,           \"congestion\": 8192,           \"flight\": 0         },         \"link\": {           \"rtt\": 4.027,           \"mbpsBandwidth\": 232.212,           \"mbpsMaxBandwidth\": 1000         },         \"recv\": {           \"packetsReceived\": 1631449,           \"packetsReceivedRetransmitted\": 110,           \"packetsLost\": 98,           \"packetsDropped\": 0,           \"packetsBelated\": 23,           \"NAKsSent\": 88,           \"bytesReceived\": 2218770640,           \"bytesLost\": 133280,           \"bytesDropped\": 0,           \"mbpsRate\": 0.588867         }       }     }   ] }","payloadType":"json","x":70,"y":1580,"wires":[["4fb0e4c5.421be4"]]},{"id":"4fb0e4c5.421be4","type":"change","z":"c791cbc0.84f648","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload.SrtReceivers[id].{\"id\":$.id,\t    \"state\": $.state,\t    \"retryCount\": $.retryCount,\t    \"time\": $.stats.time,\t    \"flow\": $.stats.window.flow,\t    \"mbpsMaxBandwidth\": $.stats.link.mbpsMaxBandwidth,\t    \"packetsReceived\": $.stats.recv.packetsReceived})","tot":"jsonata"},{"t":"set","p":"names","pt":"msg","to":"$keys($spread(msg.payload))","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":230,"y":1580,"wires":[["fd0c43cb.e1701","72cdbe7f.04fa88"]]},{"id":"fd0c43cb.e1701","type":"ui_template","z":"c791cbc0.84f648","group":"b9d366f18ff7ae74","name":"","order":22,"width":0,"height":0,"format":"<table>\n    <tr>\n        <td ng-repeat=\"key in msg.names\">{{key}}</td>\n    </tr>\n    <tr ng-repeat=\"rows in msg.payload\">\n        <td ng-repeat=\"items in rows\">{{items}}</td>\n    </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","className":"","x":450,"y":1580,"wires":[[]]},{"id":"72cdbe7f.04fa88","type":"debug","z":"c791cbc0.84f648","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":570,"y":1660,"wires":[]},{"id":"b9d366f18ff7ae74","type":"ui_group","name":"test","tab":"ffba43d73860d25d","order":1,"disp":true,"width":12,"collapse":false},{"id":"ffba43d73860d25d","type":"ui_tab","name":"Level","icon":"dashboard","order":3,"disabled":false,"hidden":false}]
9

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