Remove the key part from a JSON string

[Marshy]

Hi Everyone,

I'm new to Node Red and I'm trying to use it to read out around 30 rows of data from an SQL server database and import that data into a google sheet.

I'm stuck on converting the JSON string I'm getting from my SQL query into a JSON string the google sheet node will accept.

The string from the SQL looks something like this:

[{"ID":1,"Description":"First","Total":100,"GroupID":3},{"ID":2,"Description":"Second","Total":131,"GroupID":2}]

But to get that data into Google sheets I have to remove the key so it looks like this

[["1","First","100","3"],["2","Second","131","2"]]

Is there an easy way to do this or do I have to write a script inside a function node to do it?

[Colin]

@Marshy

The string from the SQL

Are you sure it is a string? I would have expected it to be a JavaScript object.

[E1cid]

Asuming you mean Javascript object

Here are three examples

[{"id":"8dc3265970cd0547","type":"inject","z":"b9860b4b9de8c8da","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"ID\":1,\"Description\":\"First\",\"Total\":100,\"GroupID\":3},{\"ID\":2,\"Description\":\"Second\",\"Total\":131,\"GroupID\":2}]","payloadType":"json","x":330,"y":140,"wires":[["bb07fd5c10612f36","956aa217d92944a8","e925a44cbb4a53c0"]]},{"id":"bb07fd5c10612f36","type":"change","z":"b9860b4b9de8c8da","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload.[$.*]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":500,"y":140,"wires":[["083833d08ec38286"]]},{"id":"956aa217d92944a8","type":"split","z":"b9860b4b9de8c8da","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":350,"y":200,"wires":[["cda2454120e5e59b"]]},{"id":"e925a44cbb4a53c0","type":"function","z":"b9860b4b9de8c8da","name":"function 21","func":"msg.payload = msg.payload.map(obj => Object.values(obj))\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":350,"y":280,"wires":[["083833d08ec38286"]]},{"id":"083833d08ec38286","type":"debug","z":"b9860b4b9de8c8da","name":"debug 328","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":790,"y":140,"wires":[]},{"id":"cda2454120e5e59b","type":"split","z":"b9860b4b9de8c8da","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":470,"y":200,"wires":[["e0932a902820ce5f"]]},{"id":"e09848e55151b735","type":"join","z":"b9860b4b9de8c8da","name":"","mode":"auto","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":"false","timeout":"","count":"","reduceRight":false,"x":790,"y":240,"wires":[["083833d08ec38286"]]},{"id":"e0932a902820ce5f","type":"change","z":"b9860b4b9de8c8da","name":"","rules":[{"t":"set","p":"parts.type","pt":"msg","to":"array","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":650,"y":200,"wires":[["bb2830e8b8f0baed"]]},{"id":"bb2830e8b8f0baed","type":"join","z":"b9860b4b9de8c8da","name":"","mode":"auto","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":"false","timeout":"","count":"","reduceRight":false,"x":670,"y":240,"wires":[["e09848e55151b735"]]}]

If it is a JSON (string) then you can use a JSON node to convert from string to object.

[Marshy]

Yes apologies it was a javascript object.
Thanks for your solution it works perfectly!
Just need to get my head around how each solution works.