Question 110212 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Convert Complex JSON To CSV file using JOLT

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?

Tags:

3

Answers


  1. 0

    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}

    Login or Signup to reply.
  2. 0

    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"
}
    Login or Signup to reply.
  3. 0

    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"
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search