JSON array to CSV

General
1

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

{
   "_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.

2

You can use jsonata transform to alter the object to an array of objects, then pass it through a csv node .
eg

[{"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

[$$.payload.response.inventory_details.* ~> |$|$.count_types, ['count_types','paired_cards','changed_at']|]

output

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
1 Like
3

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 :confused:

image
image1368×314 33.4 KB

is there any way that it will be imported column into new column and also line by line?

4

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.

[{"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":[]}]
5

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