JOLT Flatten Unnamed nested array and Split function

Question

Working on transformation and created spec for the input. The output is nested array but I am expecting flattened array. 2. Need to split the string and get the first element. split function doesn't seems to be working.

Please find the input, spec , output and expected output below.

Input

[
  {
    "tables": [
      {
        "columns": [
          {
            "size": 20,
            "nullable": false,
            "databaseSpecificType": "varchar",
            "generated": false,
            "dataType": "VARCHAR",
            "name": "firstname",
            "width": "(20)",
            "decimalDigits": 0,
            "remarks": "",
            "autoIncremented": false
          },
          {
            "size": 20,
            "nullable": false,
            "databaseSpecificType": "varchar",
            "generated": false,
            "dataType": "VARCHAR",
            "name": "lastname",
            "width": "(20)",
            "decimalDigits": 0,
            "remarks": "",
            "autoIncremented": false
          }
        ],
        "name": "authors",
        "fullName": "books.authors",
        "type": "table",
        "triggers": [],
        "tableConstraints": [],
        "remarks": "Contact details for book authors",
        "primaryKey": {
          "columns": {
            "sortSequence": "ascending",
            "name": "id"
          },
          "unique": true,
          "name": "pk_authors",
          "remarks": ""
        }
      },
      {
        "columns": [
          {
            "size": 10,
            "nullable": true,
            "databaseSpecificType": "int4",
            "generated": false,
            "dataType": "INTEGER",
            "name": "id",
            "width": "",
            "decimalDigits": 0,
            "remarks": "",
            "autoIncremented": false
          }
        ],
        "name": "authorslist",
        "fullName": "books.authorslist",
        "type": "view",
        "triggers": [],
        "tableConstraints": [],
        "remarks": "",
        "primaryKey": {}
      }
    ],
    "schemaCrawlerHeaderInfo": {
      "crawlTimestamp": "2018-05-23 10:21:55",
      "title": ""
    }
  }
]

Spec:

[{
  "operation": "shift",
  "spec": {
    "*": {
      "tables": {
        "*": {
          "columns": {
            "*": {
              "@(2,name)": "[&3].[&1].TABLE.tableName",
              "@(2,fullName)": ["[&3].[&1].TABLE.fullName", "[&3].[&1].DB.fullName"],
              "@(2,remarks)": "[&3].[&1].TABLE.tableDesc",
              "name": "[&3].[&1].COLUMN.name",
              "dataType": "[&3].[&1].COLUMN.dataType",
              "size": "[&3].[&1].COLUMN.size",
              "nullable": "[&3].[&1].COLUMN.nullable",
              "databaseSpecificType": "[&3].[&1].COLUMN.databaseSpecificType",
              "width": "[&3].[&1].COLUMN.width",
              "decimalDigits": "[&3].[&1].COLUMN.decimalDigits",
              "remarks": "[&3].[&1].COLUMN.remarks",
              "autoIncremented": "[&3].[&1].COLUMN.autoIncremented"
            }
          }
        }
      }
    }
  }
}]

Output

[
  [
    {
      "TABLE": {
        "tableName": "authors",
        "fullName": "books.authors",
        "tableDesc": "Contact details for book authors"
      },
      "DB": {
        "fullName": "books.authors"
      },
      "COLUMN": {
        "name": "firstname",
        "dataType": "VARCHAR",
        "size": 20,
        "nullable": false,
        "databaseSpecificType": "varchar",
        "width": "(20)",
        "decimalDigits": 0,
        "remarks": "",
        "autoIncremented": false
      }
    },
    {
      "TABLE": {
        "tableName": "authors",
        "fullName": "books.authors",
        "tableDesc": "Contact details for book authors"
      },
      "DB": {
        "fullName": "books.authors"
      },
      "COLUMN": {
        "name": "lastname",
        "dataType": "VARCHAR",
        "size": 20,
        "nullable": false,
        "databaseSpecificType": "varchar",
        "width": "(20)",
        "decimalDigits": 0,
        "remarks": "",
        "autoIncremented": false
      }
    }
  ],
  [
    {
      "TABLE": {
        "tableName": "authorslist",
        "fullName": "books.authorslist",
        "tableDesc": ""
      },
      "DB": {
        "fullName": "books.authorslist"
      },
      "COLUMN": {
        "name": "id",
        "dataType": "INTEGER",
        "size": 10,
        "nullable": true,
        "databaseSpecificType": "int4",
        "width": "",
        "decimalDigits": 0,
        "remarks": "",
        "autoIncremented": false
      }
    }
  ]
]Iamexpectingtheflattenedoutputlikebelowinsinglearrayrathernestedarray.[
  {
    "TABLE": {
      "tableName": "authors",
      "fullName": "books.authors",
      "tableDesc": "Contact details for book authors"
    },
    "DB": {
      "fullName": "books.authors"
    },
    "COLUMN": {
      "name": "firstname",
      "dataType": "VARCHAR",
      "size": 20,
      "nullable": false,
      "databaseSpecificType": "varchar",
      "width": "(20)",
      "decimalDigits": 0,
      "remarks": "",
      "autoIncremented": false
    }
  },
  {
    "TABLE": {
      "tableName": "authors",
      "fullName": "books.authors",
      "tableDesc": "Contact details for book authors"
    },
    "DB": {
      "fullName": "books.authors"
    },
    "COLUMN": {
      "name": "lastname",
      "dataType": "VARCHAR",
      "size": 20,
      "nullable": false,
      "databaseSpecificType": "varchar",
      "width": "(20)",
      "decimalDigits": 0,
      "remarks": "",
      "autoIncremented": false
    }
  },
  {
    "TABLE": {
      "tableName": "authorslist",
      "fullName": "books.authorslist",
      "tableDesc": ""
    },
    "DB": {
      "fullName": "books.authorslist"
    },
    "COLUMN": {
      "name": "id",
      "dataType": "INTEGER",
      "size": 10,
      "nullable": true,
      "databaseSpecificType": "int4",
      "width": "",
      "decimalDigits": 0,
      "remarks": "",
      "autoIncremented": false
    }
  }
]

I am expecting the flattened output like below in single array rather nested array.

[
  {
    "TABLE": {
      "tableName": "authors",
      "fullName": "books.authors",
      "tableDesc": "Contact details for book authors"
    },
    "DB": {
      "fullName": "books.authors"
    },
    "COLUMN": {
      "name": "firstname",
      "dataType": "VARCHAR",
      "size": 20,
      "nullable": false,
      "databaseSpecificType": "varchar",
      "width": "(20)",
      "decimalDigits": 0,
      "remarks": "",
      "autoIncremented": false
    }
  },
  {
    "TABLE": {
      "tableName": "authors",
      "fullName": "books.authors",
      "tableDesc": "Contact details for book authors"
    },
    "DB": {
      "fullName": "books.authors"
    },
    "COLUMN": {
      "name": "lastname",
      "dataType": "VARCHAR",
      "size": 20,
      "nullable": false,
      "databaseSpecificType": "varchar",
      "width": "(20)",
      "decimalDigits": 0,
      "remarks": "",
      "autoIncremented": false
    }
  },
  {
    "TABLE": {
      "tableName": "authorslist",
      "fullName": "books.authorslist",
      "tableDesc": ""
    },
    "DB": {
      "fullName": "books.authorslist"
    },
    "COLUMN": {
      "name": "id",
      "dataType": "INTEGER",
      "size": 10,
      "nullable": true,
      "databaseSpecificType": "int4",
      "width": "",
      "decimalDigits": 0,
      "remarks": "",
      "autoIncremented": false
    }
  }
]

And also tried split and firstelement function to get the dbname but split is not working.

"DB" : {
    "fullName" : "books.authors"
  }

"DB" : {
    "fullName" : "books"
  }

Any help on this would be great.

score 0 · Answer 1 · answered Jun 10 '18 at 17:28

Have to do two shifts.

Spec and explaination

[
  {
    // Your input data is two nested arrays.
    // Tables array, and its nested columns array.
    // The number of elements in your output array is 
    //  the total number of column elements in your input doc.
    // 
    // Can't do it in a single shift.  
    // The sample input tables arrays is length 2
    // The sample input columsn arrays are length 2 and 1.
    // You want an output array of length 3, there is no "3" 
    //  that can be referenced by the first shift operation.
    //
    // So first, "build" the final TABLE, COLUMN, DB format, by
    //  pushing table information down into the columns data, BUT
    //  keeping the same nested table and columns array structure.
    "operation": "shift",
    "spec": {
      "0": {
        "tables": {
          "*": { // tables index
            "columns": {
              "*": { // columns index
                // grabbing info off the "parent" table entry
                "@(2,name)": "tables[&3].cols[&1].TABLE.tableName",
                "@(2,fullName)": ["tables[&3].cols[&1].TABLE.fullName", "tables[&3].cols[&1].DB.fullName"],
                "@(2,remarks)": "tables[&3].cols[&1].TABLE.tableDesc",
                // 
                // handling all the column level data
                "*": "tables[&3].cols[&1].COLUMN.&"
              }
            }
          }
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "tables": {
        "*": {
          "cols": {
            // now walk the nested tables and cols array
            // and accumulate each "fully formatted" 
            // col entry, into the output top-level array
            "*": "[]"
          }
        }
      }
    }
  }
]

JOLT Flatten Unnamed nested array and Split function

1 Answers1