Convert nested JSON with nested arrays to CSV

Shiji Pan

2 years ago

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": "address_list:0",
                "MatchCode": "_ADDR_BLOCK",
                "maxScore": 0
              },
              {
                "adRefId": "ab39f31d-6b21-4377-9c91-85fdd345c22a",
                "addrRefId": "name_block_list:0",
                "MatchCode": "ADDR_BLOCK",
                "maxScore": 0
              }
            ],
            "MatchStatus": 200,
            "dataRefs": [
              {
                "addressRef": {
                  "addrRefId": "0",
                  "addrType": "REGISTRATION_ADDRESS",
                  "addressLine1": "123 Test Street",
                  "addressLine2": "",
                  "city": "",
                  "country": "Federation",
                  "postalCode": "12345",
                  "province": ""
                },
                "dataId": "0"
              }
            ],
            "docType": "_SCREEN",
            "extRefId1": "b326c63721536765412099",
            "extRefId1Type": "",
            "extRefId2": "",
            "extRefId2Type": "_SETTINGS",
            "ules": [
              "1213395"
            ],
            "Status": [
              "20"
            ]
          }
        },
        "key": {
          "id": [
            {
              "collection": "__ROOT__",
              "string": "3721536765412099_E"
            }
          ],
          "is_partial": false
        }
      }
    ]
  }
]

He want to convert it to CSV like this:

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
1643342434,2053342527,1643342527,4,e6040-c8dcdb165993,city_list:0,REGI_ADDR_BLOCK,0.9968223809704663,200,_SCREEN,b326c63721536765412099,,,_SETTINGS,1213395,20
1643342434,2053342527,1643342527,4,800-3c7a04dc8d3f,address_list:0,_ADDR_BLOCK,0,200,_SCREEN,b326c63721536765412099,,,_SETTINGS,1213395,20
1643342434,2053342527,1643342527,4,ab39f31d-6b21-4377-9c91-85fdd345c22a,name_block_list:0,ADDR_BLOCK,0,200,_SCREEN,b326c63721536765412099,,,_SETTINGS,1213395,20

I wonder if DataFileConverter can do this. So I test. Yes, it can convert nested JSON with nested arrays to CSV, in a minute.

1. Open JSON file in DataFileConverter, set JSON options.

2. Set CSV options.

3. “Next” and “Next”, convert, get CSV file.

JSON → CSV reference

Type mapping

Value kind	in JSON	in CSV
Int	`1234 (number)`	`1234`
Decimal	`1234.56 (number)`	`1234.56`
Float	`1.234e10 (number)`	`1.234e+10`
Text	`"text" (string)`	`quoted only when it contains a comma or a quote`
Long text	`"multi-line\ntext" (escaped newline inside a string)`	`must be quoted; line breaks inside force multi-line records`
Date	`"2026-09-18" (string)`	`2026-09-18`
Datetime	`"2026-09-18T14:30:00Z" (string)`	`2026-09-18 14:30:00`
Binary	`"base64:..." (string)`	`not representable - base64 or hex string`
Boolean	`true / false (boolean)`	`TRUE / FALSE, or 1 / 0 depending on the writer`
JSON	`nested object or array, kept as structure`	`the whole JSON value quoted, with internal quotes doubled`

Dialect differences

Both JSON and CSV are file formats, so what differs below is how each one chooses to represent a value.

Property	in JSON	in CSV
identifiers	`Double quotes; backslash escapes`	`Double quotes; a literal quote is doubled ("" )`
dates	`No date type: dates arrive as strings and the reader must p...`	`Conventional text: 2026-09-18 or 2026-09-18 14:30:00`
binary	`Not supported natively - base64 inside a string`	`Not supported`
separator	`Braces and commas`	`Comma ","`
encoding	`UTF-8 by definition`	`No declaration - UTF-8 works only with a BOM`
typing	`string, number, boolean, null, object, array - no date type`	`None - every field is text`
shape	`Nested objects and arrays`	`Flat table, optional header row`
ceiling	`Whole document is usually parsed into memory`	`No formal limit; row count bounded by memory`

Convert your data online, free

Free in-browser converters for CSV, Excel, JSON, XML and SQL: Withdata Online Data Conversion. Large files, batch conversion or scheduling? See the offline desktop apps, or browse all data conversion tools.