Handling Nested Array Data in DolphinDB Imports

DolphinDB provides high-performance data processing with native support for Array Vectors (such as INT[], DOUBLE[]). However, importing structured array-like text (for example, [1, 2, 3] or delimited lists within a single cell) from text files using standard loadText can sometimes be tricky. By default, loadText treats these fields as standard strings, and passing an Array Vector type directly into an auto-generated schema table may not automatically parse the inner string values.

In this guide, we explore the recommended and most efficient ways to parse string-encoded arrays into true DolphinDB Array Vectors during or after ingestion.

Method 1: Post-Load Transformation Using split and Casting (Recommended for Flexibility)

If you have already imported the dataset as strings, the most straightforward approach is to strip the surrounding square brackets and apply the split function vectorially.

// Sample setup simulating your CSV content
t = table(`A`B as col1, ["[1,2,3]", "[4,5,6]"] as col2)

// 1. Strip brackets and parse into INT[]
update t set col2 = split(substr(col2, 1, strlen(col2) - 2), ",").int()

// Verify the data type
schema(t).colDefs

In this example:

  • substr(col2, 1, strlen(col2) - 2) removes the leading [ and trailing ].
  • split(..., ",") breaks the comma-separated values into vector elements.
  • .int() casts the resulting string tokens into integer values, creating an INT[] array vector.

Method 2: Automating Across Multiple Columns

If your dataset contains 50+ columns and multiple array fields, manually writing updates for each column can become redundant. You can automate this process dynamically across all designated columns using metaprogramming:

// Specify the array columns you want to convert
arrayCols = `col2`col5`col12

// Loop and transform dynamically
for (c in arrayCols) {
    colData = t[c]
    // Clean brackets and split
    t[c] = split(substr(colData, 1, strlen(colData) - 2), ",").int()
}

This keeps your code maintainable and DRY, irrespective of how many array columns your schema contains.

Method 3: Ingestion via loadTextEx with a Transform Function

When loading large datasets directly into a distributed database (DFS) or memory, you can avoid loading everything as strings first by providing a transform function to loadTextEx:

def parseNestedArrays(mutable t) {
    t["col2"] = split(substr(t.col2, 1, strlen(t.col2) - 2), ",").int()
    return t
}

// Using loadTextEx with a transform callback
db = database("dfs://nestedDB", VALUE, 2023.01.01..2023.12.31)
pt = loadTextEx(db, `pt, `date, "data.csv", delimiter='|', transform=parseNestedArrays)

The transform parameter processes each block of records as it reads them from the file chunk-by-chunk, minimizing memory overhead and directly persisting typed array vectors.

Summary

While loadText treats bracketed arrays as raw strings by default, combining split(), substr(), and type casting provides a reliable way to construct DolphinDB array vectors. For high-throughput production pipelines, combine this logic inside a transform callback with loadTextEx to parse multi-column arrays seamlessly.