Convert Complex JSON To CSV file using JOLT

Question

I am new to JOLT and I don't know much about it, and I need the JOLT spec to convert my below JSON to CSV file:

{
  "bigRecords": [
    {
      "Records": {
        "Name": "R1",
        "Id": "1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01",
        "Type": "New",
        "Area": "Eng",
        "createdAt": "11-10-2022 06:10:00",
        "deletedDate": "11-10-2022 06:20:00",
        "deleted": true,
        "deletedBy": "userId",
        "createdBy": "userId"
      },
      "ancestors": [
        {
          "Name": "P1",
          "Type": "DELETE",
          "sta": "",
          "some": "",
          "Id": "1"
        }
      ]
    },
    {
      "Records": {
        "Name": "R2",
        "Id": "1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01",
        "Type": "DRAFT",
        "Area": "Eng",
        "createdAt": "11-10-2022 06:10:00",
        "deletedDate": "11-10-2022 06:20:00",
        "deleted": true,
        "deletedBy": "userId",
        "createdBy": "userId"
      },
      "dependentRecords": [
        {
          "Name": "P3",
          "Type": "DELETE",
          "sta": "1P0",
          "some": "1P02",
          "Id": "1P0"
        }
      ]
    }
  ]
}

Please help me in convert the complex json to CSV using the JOlT library, or any other way I can do achieve this, in spring boot?

Does this answer your question? [How to deserialize JSON into flat, Map-like structure?](https://stackoverflow.com/questions/20355261/how-to-deserialize-json-into-flat-map-like-structure) — kasptom, Jan 17 '23 at 21:01

score 0 · Answer 1 · answered Jan 17 '23 at 07:13

You have JsonFlattener which is easy to use. https://github.com/wnameless/json-flattener

Map<String, Object> flattenJson = JsonFlattener.flattenAsMap(json);

System.out.println(flattenJson);

// {a.b=1, a.c=null, a.d[0]=false, a.d[1]=true, e=f, g=2.3}

score 0 · Answer 2 · answered Jan 17 '23 at 14:57

Library Josson has flatten() function. It also has function unflatten() to reverse the operation.

https://github.com/octomix/josson

Deserialization

Josson josson = Josson.fromJsonString(
    "{" +
    "    \"bigRecords\": [" +
    "        {" +
    "        \"Records\": {" +
    "            \"Name\": \"R1\"," +
    "            \"Id\": \"1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01\"," +
    "            \"Type\": \"New\"," +
    "            \"Area\": \"Eng\"," +
    "            \"createdAt\": \"11-10-2022 06:10:00\"," +
    "            \"deletedDate\": \"11-10-2022 06:20:00\"," +
    "            \"deleted\": true," +
    "            \"deletedBy\": \"userId\"," +
    "            \"createdBy\": \"userId\"" +
    "            " +
    "        }," +
    "        \"ancestors\": [" +
    "            {" +
    "                \"Name\": \"P1\"," +
    "                \"Type\": \"DELETE\"," +
    "                \"sta\": \"\"," +
    "                \"some\": \"\"," +
    "                \"Id\": \"1\"" +
    "            }" +
    "        ]" +
    "    }, {" +
    "        \"Records\": {" +
    "            \"Name\": \"R2\"," +
    "            \"Id\": \"1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01\"," +
    "            \"Type\": \"DRAFT\"," +
    "            \"Area\": \"Eng\"," +
    "            \"createdAt\": \"11-10-2022 06:10:00\"," +
    "            \"deletedDate\": \"11-10-2022 06:20:00\"," +
    "            \"deleted\": true," +
    "            \"deletedBy\": \"userId\"," +
    "            \"createdBy\": \"userId\"" +
    "        }," +
    "        \"dependentRecords\": [" +
    "            {" +
    "                \"Name\": \"P3\"," +
    "                \"Type\": \"DELETE\"," +
    "                \"sta\": \"1P0\"," +
    "                \"some\": \"1P02\"," +
    "                \"Id\": \"1P0\"" +
    "            }" +
    "        ]" +
    "    }" +
    "    ]" +
    "}");

Style 1

JsonNode node = josson.getNode("flatten('.','[%d]')");
System.out.println(node.toPrettyString());

Output

{
  "bigRecords[0].Records.Name" : "R1",
  "bigRecords[0].Records.Id" : "1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01",
  "bigRecords[0].Records.Type" : "New",
  "bigRecords[0].Records.Area" : "Eng",
  "bigRecords[0].Records.createdAt" : "11-10-2022 06:10:00",
  "bigRecords[0].Records.deletedDate" : "11-10-2022 06:20:00",
  "bigRecords[0].Records.deleted" : true,
  "bigRecords[0].Records.deletedBy" : "userId",
  "bigRecords[0].Records.createdBy" : "userId",
  "bigRecords[0].ancestors[0].Name" : "P1",
  "bigRecords[0].ancestors[0].Type" : "DELETE",
  "bigRecords[0].ancestors[0].sta" : "",
  "bigRecords[0].ancestors[0].some" : "",
  "bigRecords[0].ancestors[0].Id" : "1",
  "bigRecords[1].Records.Name" : "R2",
  "bigRecords[1].Records.Id" : "1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01",
  "bigRecords[1].Records.Type" : "DRAFT",
  "bigRecords[1].Records.Area" : "Eng",
  "bigRecords[1].Records.createdAt" : "11-10-2022 06:10:00",
  "bigRecords[1].Records.deletedDate" : "11-10-2022 06:20:00",
  "bigRecords[1].Records.deleted" : true,
  "bigRecords[1].Records.deletedBy" : "userId",
  "bigRecords[1].Records.createdBy" : "userId",
  "bigRecords[1].dependentRecords[0].Name" : "P3",
  "bigRecords[1].dependentRecords[0].Type" : "DELETE",
  "bigRecords[1].dependentRecords[0].sta" : "1P0",
  "bigRecords[1].dependentRecords[0].some" : "1P02",
  "bigRecords[1].dependentRecords[0].Id" : "1P0"
}

Style 2

JsonNode node = josson.getNode("flatten('_')");
System.out.println(node.toPrettyString());

Output

{
  "bigRecords_0_Records_Name" : "R1",
  "bigRecords_0_Records_Id" : "1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01",
  "bigRecords_0_Records_Type" : "New",
  "bigRecords_0_Records_Area" : "Eng",
  "bigRecords_0_Records_createdAt" : "11-10-2022 06:10:00",
  "bigRecords_0_Records_deletedDate" : "11-10-2022 06:20:00",
  "bigRecords_0_Records_deleted" : true,
  "bigRecords_0_Records_deletedBy" : "userId",
  "bigRecords_0_Records_createdBy" : "userId",
  "bigRecords_0_ancestors_0_Name" : "P1",
  "bigRecords_0_ancestors_0_Type" : "DELETE",
  "bigRecords_0_ancestors_0_sta" : "",
  "bigRecords_0_ancestors_0_some" : "",
  "bigRecords_0_ancestors_0_Id" : "1",
  "bigRecords_1_Records_Name" : "R2",
  "bigRecords_1_Records_Id" : "1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01",
  "bigRecords_1_Records_Type" : "DRAFT",
  "bigRecords_1_Records_Area" : "Eng",
  "bigRecords_1_Records_createdAt" : "11-10-2022 06:10:00",
  "bigRecords_1_Records_deletedDate" : "11-10-2022 06:20:00",
  "bigRecords_1_Records_deleted" : true,
  "bigRecords_1_Records_deletedBy" : "userId",
  "bigRecords_1_Records_createdBy" : "userId",
  "bigRecords_1_dependentRecords_0_Name" : "P3",
  "bigRecords_1_dependentRecords_0_Type" : "DELETE",
  "bigRecords_1_dependentRecords_0_sta" : "1P0",
  "bigRecords_1_dependentRecords_0_some" : "1P02",
  "bigRecords_1_dependentRecords_0_Id" : "1P0"
}

Barbaros Özhan · Answer 3 · 2023-01-21T11:03:09.747

Through using Jolt;

you can separate the levels by respective deepness of the leaves, and tile underscore separated ampersands which are node representatives in order to flatten the whole JSON such as

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {// the level of the indexes of the array
          "Reco*": {
            "*": {
              "@": "&4_&3_&2_&" // & : the replicator of the current level value, &2, &3, ... represents going respective 2, 3 levels up the tree 
            }
          },          
          "*": {// else case -> keys other than "Records"
            "*": {// the level of the indexes of the respective arrays
              "*": {
                "@": "&5_&4_&3_&2_&"
              }
            }
          }
        }
      }
    }
  }
]

which yields

{
  "bigRecords_0_Records_Name" : "R1",
  "bigRecords_0_Records_Id" : "1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01",
  "bigRecords_0_Records_Type" : "New",
  "bigRecords_0_Records_Area" : "Eng",
  "bigRecords_0_Records_createdAt" : "11-10-2022 06:10:00",
  "bigRecords_0_Records_deletedDate" : "11-10-2022 06:20:00",
  "bigRecords_0_Records_deleted" : true,
  "bigRecords_0_Records_deletedBy" : "userId",
  "bigRecords_0_Records_createdBy" : "userId",
  "bigRecords_0_ancestors_0_Name" : "P1",
  "bigRecords_0_ancestors_0_Type" : "DELETE",
  "bigRecords_0_ancestors_0_sta" : "",
  "bigRecords_0_ancestors_0_some" : "",
  "bigRecords_0_ancestors_0_Id" : "1",
  "bigRecords_1_Records_Name" : "R2",
  "bigRecords_1_Records_Id" : "1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01",
  "bigRecords_1_Records_Type" : "DRAFT",
  "bigRecords_1_Records_Area" : "Eng",
  "bigRecords_1_Records_createdAt" : "11-10-2022 06:10:00",
  "bigRecords_1_Records_deletedDate" : "11-10-2022 06:20:00",
  "bigRecords_1_Records_deleted" : true,
  "bigRecords_1_Records_deletedBy" : "userId",
  "bigRecords_1_Records_createdBy" : "userId",
  "bigRecords_1_dependentRecords_0_Name" : "P3",
  "bigRecords_1_dependentRecords_0_Type" : "DELETE",
  "bigRecords_1_dependentRecords_0_sta" : "1P0",
  "bigRecords_1_dependentRecords_0_some" : "1P02",
  "bigRecords_1_dependentRecords_0_Id" : "1P0"
}

Convert Complex JSON To CSV file using JOLT

3 Answers3