{"id":22430,"date":"2024-10-17T06:09:12","date_gmt":"2024-10-17T06:09:12","guid":{"rendered":"http:\/\/www.withdata.com\/blog\/?p=22430"},"modified":"2024-10-18T02:06:52","modified_gmt":"2024-10-18T02:06:52","slug":"convert-nested-json-with-nested-arrays-to-csv","status":"publish","type":"post","link":"https:\/\/www.withdata.com\/blog\/use\/convert-nested-json-with-nested-arrays-to-csv.html","title":{"rendered":"Convert nested JSON with nested arrays to CSV"},"content":{"rendered":"

I see a post Convert nested json with nested arrays in csv<\/a>.<\/p>\n

The poster have this JSON data:<\/p>\n

[\r\n  {\r\n    \"aggregate_result\": [],\r\n    \"explain\": \"\",\r\n    \"key_with_document\": [\r\n      {\r\n        \"document\": {\r\n          \"creation_date\": 1643342434,\r\n          \"expiration_date\": 2053342527,\r\n          \"modification_date\": 1643342527,\r\n          \"revision\": 4,\r\n          \"struct\": {\r\n            \"MatchResponse\": [\r\n              {\r\n                \"adRefId\": \"e6040-c8dcdb165993\",\r\n                \"addrRefId\": \"city_list:0\",\r\n                \"MatchCode\": \"REGI_ADDR_BLOCK\",\r\n                \"maxScore\": 0.9968223809704663\r\n              },\r\n              {\r\n                \"adRefId\": \"800-3c7a04dc8d3f\",\r\n                \"addrRefId\": \"address_list:0\",\r\n                \"MatchCode\": \"_ADDR_BLOCK\",\r\n                \"maxScore\": 0\r\n              },\r\n              {\r\n                \"adRefId\": \"ab39f31d-6b21-4377-9c91-85fdd345c22a\",\r\n                \"addrRefId\": \"name_block_list:0\",\r\n                \"MatchCode\": \"ADDR_BLOCK\",\r\n                \"maxScore\": 0\r\n              }\r\n            ],\r\n            \"MatchStatus\": 200,\r\n            \"dataRefs\": [\r\n              {\r\n                \"addressRef\": {\r\n                  \"addrRefId\": \"0\",\r\n                  \"addrType\": \"REGISTRATION_ADDRESS\",\r\n                  \"addressLine1\": \"123 Test Street\",\r\n                  \"addressLine2\": \"\",\r\n                  \"city\": \"\",\r\n                  \"country\": \"Federation\",\r\n                  \"postalCode\": \"12345\",\r\n                  \"province\": \"\"\r\n                },\r\n                \"dataId\": \"0\"\r\n              }\r\n            ],\r\n            \"docType\": \"_SCREEN\",\r\n            \"extRefId1\": \"b326c63721536765412099\",\r\n            \"extRefId1Type\": \"\",\r\n            \"extRefId2\": \"\",\r\n            \"extRefId2Type\": \"_SETTINGS\",\r\n            \"ules\": [\r\n              \"1213395\"\r\n            ],\r\n            \"Status\": [\r\n              \"20\"\r\n            ]\r\n          }\r\n        },\r\n        \"key\": {\r\n          \"id\": [\r\n            {\r\n              \"collection\": \"__ROOT__\",\r\n              \"string\": \"3721536765412099_E\"\r\n            }\r\n          ],\r\n          \"is_partial\": false\r\n        }\r\n      }\r\n    ]\r\n  }\r\n]\r\n<\/pre>\n
He want to convert it to CSV like this:<\/p>\n
creation_date,expiration_date,modification_date,revision,adRefId,addrRefId,MatchCode,maxScore,struct\/MatchStatus,struct\/docType,struct\/extRefId1,struct\/extRefId1Type,struct\/extRefId2,struct\/extRefId2Type,struct\/ules,struct\/Status\r\n1643342434,2053342527,1643342527,4,e6040-c8dcdb165993,city_list:0,REGI_ADDR_BLOCK,0.9968223809704663,200,_SCREEN,b326c63721536765412099,,,_SETTINGS,1213395,20\r\n1643342434,2053342527,1643342527,4,800-3c7a04dc8d3f,address_list:0,_ADDR_BLOCK,0,200,_SCREEN,b326c63721536765412099,,,_SETTINGS,1213395,20\r\n1643342434,2053342527,1643342527,4,ab39f31d-6b21-4377-9c91-85fdd345c22a,name_block_list:0,ADDR_BLOCK,0,200,_SCREEN,b326c63721536765412099,,,_SETTINGS,1213395,20\r\n<\/pre>\n
I wonder if DataFileConverter<\/a>\u00a0can do this. So I test. Yes, it can convert nested JSON with nested arrays to CSV, in a minute.<\/p>\n
1. Open JSON file in DataFileConverter, set JSON options.<\/p>\n
\"Convert<\/p>\n
2. Set CSV options.
\n\"Convert<\/p>\n
3. “Next” and “Next”, convert, get CSV file.<\/p>\n","protected":false},"excerpt":{"rendered":"
I see a post Convert nested json with nested arrays in csv. The poster have this JSON data: [ { “aggregate_result”: [], “explain”: “”, “key_with_document”: [ { “document”: { “creation_date”: 1643342434, “expiration_date”: 2053342527, “modification_date”: 1643342527, “revision”: 4, “struct”: { “MatchResponse”: [ { “adRefId”: “e6040-c8dcdb165993”, “addrRefId”: “city_list:0”, “MatchCode”: “REGI_ADDR_BLOCK”, “maxScore”: 0.9968223809704663 }, { “adRefId”: “800-3c7a04dc8d3f”, “addrRefId”: […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3141],"tags":[34,1418,1498],"class_list":["post-22430","post","type-post","status-publish","format-standard","hentry","category-use","tag-csv","tag-json","tag-json-to-csv"],"_links":{"self":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/22430","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/comments?post=22430"}],"version-history":[{"count":6,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/22430\/revisions"}],"predecessor-version":[{"id":22438,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/22430\/revisions\/22438"}],"wp:attachment":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/media?parent=22430"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/categories?post=22430"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/tags?post=22430"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}