# JSON array to CSV
**URL:** https://discourse.nodered.org/t/json-array-to-csv/67362
**Category:** General
**Tags:** function-node
**Created:** [5 September 2022 18:55 UTC](https://discourse.nodered.org/t/json-array-to-csv/67362 "2022-09-05T18:55:29Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@Sphere](https://discourse.nodered.org/u/Sphere)
#### Post date: [5 September 2022 18:55 UTC](https://discourse.nodered.org/t/json-array-to-csv/67362/1 "2022-09-05T18:55:29Z")
Hellou, i am newbie and i am strugling how to convert json data array into csv format to be able to work in google spread sheet.
my data are in JSON format like bellow
```auto
{
"_msgid":"ed76c5391f37b3d7",
"payload":{
"auth_status":201,
"auth_status_message":"201: Auth Accepted",
"resp_code":200,
"resp_note":"200: OK",
"response":{
"success":true,
"inventory_details":{
"207646":{
"id":207646,
"item_id":8588008019191,
"catalog_id":"8588008019191",
"ean":"8588008019191",
"name":"Bezkofeinová – Peru – 200g",
"count_types":{
"all":4,
"expired":0,
"damaged":0,
"ordered":0,
"reserved":0,
"changed_at":"2022-09-05 20:28:41"
},
"paired_cards":[
{
"id":156420,
"type_id":0,
"size":{
"width":150,
"height":30,
"depth":250
},
"weight":210
},
{
"id":171174,
"type_id":0,
"size":{
"width":155,
"height":28,
"depth":230
},
"weight":218
},
{
"id":173330,
"type_id":0,
"size":{
"width":130,
"height":37,
"depth":230
},
"weight":218
},
{
"id":189246,
"type_id":0,
"size":{
"width":155,
"height":130,
"depth":30
},
"weight":220
},
{
"id":218392,
"type_id":0,
"size":{
"width":140,
"height":200,
"depth":40
},
"weight":218
}
]
},
```
and i need to have each new line for every one inventory\_details like bellow:
id, item\_id, catalog\_id, ean, name, all, expired, damaged, ordered, reserved
207646,8588008019191,8588008019191,8588008019191,Bezkofeinová – Peru – 200g,4,0,0,0,0
thanks for advise how to proceed.
---
### Author:  [@E1cid](https://discourse.nodered.org/u/E1cid)
#### Post date: [5 September 2022 19:58 UTC](https://discourse.nodered.org/t/json-array-to-csv/67362/2 "2022-09-05T19:58:59Z")
You can use jsonata transform to alter the object to an array of objects, then pass it through a csv node .
eg
```auto
[{"id":"fc667944.ec8168","type":"inject","z":"30af2d3e.d94ea2","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"auth_status\":201,\"auth_status_message\":\"201: Auth Accepted\",\"resp_code\":200,\"resp_note\":\"200: OK\",\"response\":{\"success\":true,\"inventory_details\":{\"207646\":{\"id\":207646,\"item_id\":8588008019191,\"catalog_id\":\"8588008019191\",\"ean\":\"8588008019191\",\"name\":\"Bezkofeinová – Peru – 200g\",\"count_types\":{\"all\":4,\"expired\":0,\"damaged\":0,\"ordered\":0,\"reserved\":0,\"changed_at\":\"2022-09-05 20:28:41\"},\"paired_cards\":[{\"id\":156420,\"type_id\":0,\"size\":{\"width\":150,\"height\":30,\"depth\":250},\"weight\":210},{\"id\":171174,\"type_id\":0,\"size\":{\"width\":155,\"height\":28,\"depth\":230},\"weight\":218},{\"id\":173330,\"type_id\":0,\"size\":{\"width\":130,\"height\":37,\"depth\":230},\"weight\":218},{\"id\":189246,\"type_id\":0,\"size\":{\"width\":155,\"height\":130,\"depth\":30},\"weight\":220},{\"id\":218392,\"type_id\":0,\"size\":{\"width\":140,\"height\":200,\"depth\":40},\"weight\":218}]},\"207647\":{\"id\":207647,\"item_id\":8588008019192,\"catalog_id\":\"8588008019192\",\"ean\":\"8588008019192\",\"name\":\"Bezkofeinová – Peru – 200g\",\"count_types\":{\"all\":4,\"expired\":0,\"damaged\":0,\"ordered\":0,\"reserved\":0,\"changed_at\":\"2022-09-05 20:28:41\"},\"paired_cards\":[{\"id\":156420,\"type_id\":0,\"size\":{\"width\":150,\"height\":30,\"depth\":250},\"weight\":210},{\"id\":171174,\"type_id\":0,\"size\":{\"width\":155,\"height\":28,\"depth\":230},\"weight\":218},{\"id\":173330,\"type_id\":0,\"size\":{\"width\":130,\"height\":37,\"depth\":230},\"weight\":218},{\"id\":189246,\"type_id\":0,\"size\":{\"width\":155,\"height\":130,\"depth\":30},\"weight\":220},{\"id\":218392,\"type_id\":0,\"size\":{\"width\":140,\"height\":200,\"depth\":40},\"weight\":218}]}}}}","payloadType":"json","x":190,"y":720,"wires":[["1c497b98.7a006c","d42ce5e4.2dea58"]]},{"id":"1c497b98.7a006c","type":"change","z":"30af2d3e.d94ea2","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"[$$.payload.response.inventory_details.* ~> |$|$.count_types, ['count_types','paired_cards','changed_at']|]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":350,"y":720,"wires":[["392d82b8.62aaae","a218a294.df2108"]]},{"id":"d42ce5e4.2dea58","type":"debug","z":"30af2d3e.d94ea2","name":"object","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":270,"y":680,"wires":[]},{"id":"392d82b8.62aaae","type":"csv","z":"30af2d3e.d94ea2","name":"","sep":",","hdrin":"","hdrout":"all","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":520,"y":720,"wires":[["6814e6fd.4a589"]]},{"id":"a218a294.df2108","type":"debug","z":"30af2d3e.d94ea2","name":"array","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":440,"y":680,"wires":[]},{"id":"6814e6fd.4a589","type":"debug","z":"30af2d3e.d94ea2","name":"csv","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":610,"y":720,"wires":[]}]
```
jsonata expression
```auto
[$$.payload.response.inventory_details.* ~> |$|$.count_types, ['count_types','paired_cards','changed_at']|]
```
output
```auto
id,item_id,catalog_id,ean,name,all,expired,damaged,ordered,reserved
207646,8588008019191,8588008019191,8588008019191,Bezkofeinová – Peru – 200g,4,0,0,0,0
207647,8588008019192,8588008019192,8588008019192,Bezkofeinová – Peru – 200g,4,0,0,0,0
```
---
### Author:  [@Sphere](https://discourse.nodered.org/u/Sphere)
#### Post date: [6 September 2022 15:32 UTC](https://discourse.nodered.org/t/json-array-to-csv/67362/3 "2022-09-06T15:32:36Z")
> [@E1cid](#):
>
> jsonata
i played with it and it grab values what i need only there is one problem that into google sheet it is imported into one column 😕
is there any way that it will be imported column into new column and also line by line?
---
### Author:  [@E1cid](https://discourse.nodered.org/u/E1cid)
#### Post date: [6 September 2022 15:40 UTC](https://discourse.nodered.org/t/json-array-to-csv/67362/4 "2022-09-06T15:40:48Z")
I answered you initial question, I do not use google sheets so i would not know how to configure it, Is there not any example flows in the node read import flow library.
Hopefully someone else may know.
[edit] from what i have read i think node-red.contrib-google-sheet accepts an array of arrays
you could try this, but it's only a guess.
```auto
[{"id":"fc667944.ec8168","type":"inject","z":"30af2d3e.d94ea2","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"auth_status\":201,\"auth_status_message\":\"201: Auth Accepted\",\"resp_code\":200,\"resp_note\":\"200: OK\",\"response\":{\"success\":true,\"inventory_details\":{\"207646\":{\"id\":207646,\"item_id\":8588008019191,\"catalog_id\":\"8588008019191\",\"ean\":\"8588008019191\",\"name\":\"Bezkofeinová – Peru – 200g\",\"count_types\":{\"all\":4,\"expired\":0,\"damaged\":0,\"ordered\":0,\"reserved\":0,\"changed_at\":\"2022-09-05 20:28:41\"},\"paired_cards\":[{\"id\":156420,\"type_id\":0,\"size\":{\"width\":150,\"height\":30,\"depth\":250},\"weight\":210},{\"id\":171174,\"type_id\":0,\"size\":{\"width\":155,\"height\":28,\"depth\":230},\"weight\":218},{\"id\":173330,\"type_id\":0,\"size\":{\"width\":130,\"height\":37,\"depth\":230},\"weight\":218},{\"id\":189246,\"type_id\":0,\"size\":{\"width\":155,\"height\":130,\"depth\":30},\"weight\":220},{\"id\":218392,\"type_id\":0,\"size\":{\"width\":140,\"height\":200,\"depth\":40},\"weight\":218}]},\"207647\":{\"id\":207647,\"item_id\":8588008019192,\"catalog_id\":\"8588008019192\",\"ean\":\"8588008019192\",\"name\":\"Bezkofeinová – Peru – 200g\",\"count_types\":{\"all\":4,\"expired\":0,\"damaged\":0,\"ordered\":0,\"reserved\":0,\"changed_at\":\"2022-09-05 20:28:41\"},\"paired_cards\":[{\"id\":156420,\"type_id\":0,\"size\":{\"width\":150,\"height\":30,\"depth\":250},\"weight\":210},{\"id\":171174,\"type_id\":0,\"size\":{\"width\":155,\"height\":28,\"depth\":230},\"weight\":218},{\"id\":173330,\"type_id\":0,\"size\":{\"width\":130,\"height\":37,\"depth\":230},\"weight\":218},{\"id\":189246,\"type_id\":0,\"size\":{\"width\":155,\"height\":130,\"depth\":30},\"weight\":220},{\"id\":218392,\"type_id\":0,\"size\":{\"width\":140,\"height\":200,\"depth\":40},\"weight\":218}]}}}}","payloadType":"json","x":190,"y":720,"wires":[["1c497b98.7a006c","d42ce5e4.2dea58"]]},{"id":"1c497b98.7a006c","type":"change","z":"30af2d3e.d94ea2","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"($arr := [$$.payload.response.inventory_details.* ~> |$|$.count_types, ['count_types','paired_cards','changed_at']|];\t$append([[$keys($arr[0])]], ($count($arr) > 1 ? $arr.[$.*] : [[$arr.[$.*]]]))\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":400,"y":720,"wires":[["a218a294.df2108"]]},{"id":"d42ce5e4.2dea58","type":"debug","z":"30af2d3e.d94ea2","name":"object","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":270,"y":680,"wires":[]},{"id":"afc36ff9.40a538","type":"inject","z":"30af2d3e.d94ea2","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"auth_status\":201,\"auth_status_message\":\"201: Auth Accepted\",\"resp_code\":200,\"resp_note\":\"200: OK\",\"response\":{\"success\":true,\"inventory_details\":{\"207646\":{\"id\":207646,\"item_id\":8588008019191,\"catalog_id\":\"8588008019191\",\"ean\":\"8588008019191\",\"name\":\"Bezkofeinová – Peru – 200g\",\"count_types\":{\"all\":4,\"expired\":0,\"damaged\":0,\"ordered\":0,\"reserved\":0,\"changed_at\":\"2022-09-05 20:28:41\"},\"paired_cards\":[{\"id\":156420,\"type_id\":0,\"size\":{\"width\":150,\"height\":30,\"depth\":250},\"weight\":210},{\"id\":171174,\"type_id\":0,\"size\":{\"width\":155,\"height\":28,\"depth\":230},\"weight\":218},{\"id\":173330,\"type_id\":0,\"size\":{\"width\":130,\"height\":37,\"depth\":230},\"weight\":218},{\"id\":189246,\"type_id\":0,\"size\":{\"width\":155,\"height\":130,\"depth\":30},\"weight\":220},{\"id\":218392,\"type_id\":0,\"size\":{\"width\":140,\"height\":200,\"depth\":40},\"weight\":218}]}}}}","payloadType":"json","x":190,"y":760,"wires":[["1c497b98.7a006c"]]},{"id":"a218a294.df2108","type":"debug","z":"30af2d3e.d94ea2","name":"array","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":440,"y":680,"wires":[]}]
```
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [5 November 2022 15:40 UTC](https://discourse.nodered.org/t/json-array-to-csv/67362/5 "2022-11-05T15:40:56Z")
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.