如何解析非标准JSON格式的数组型字符串并导入DolphinDB表？

你需要解析数组型字符串并插入DolphinDB表，每个嵌套数组作为单独记录，示例数据如下：

'[["1743051840000","87498.4","87498.8","87498.4","87498.8","0"],["1743051780000","87502.8","87508.6","87484","87502.2","1"]]'

期望生成包含timestamp、value1至value4、flag字段的表，但调用fromJson和parseJsonTable均报错：

v  ='[["1743051840000","87498.4","87498.8","87498.4","87498.8","0"],["1743051780000","87502.8","87508.6","87484","87502.2","1"]]' 
fromJson(v)

// output:
fromJson(v) => The data form is not supported.

parseJsonTable(v)

// output:
parseJsonTable(v) => Can't find json beginning character '{'.

解决方法 #

DolphinDB的fromJson函数支持JSON数组，但你的字符串中包含HTML转义字符"，这是导致解析失败的核心原因。只需先处理转义，再解析转换即可，无需复杂预处理，步骤如下：

1. 替换HTML转义字符：用replace函数把字符串中的"替换为标准双引号"
2. 解析JSON数组：使用fromJson将处理后的字符串解析为DolphinDB数组
3. 转换为目标表：将数组转换为表，并指定列名和对应数据类型

完整代码示例 #

// 原始数据字符串
v = '[["1743051840000","87498.4","87498.8","87498.4","87498.8","0"],["1743051780000","87502.8","87508.6","87484","87502.2","1"]]'

// 步骤1：替换转义字符
cleanV = replace(v, """, "\"")

// 步骤2：解析为数组
dataArray = fromJson(cleanV)

// 步骤3：转换为目标表，同时转换数据类型
resultTable = table(
    timestamp(datetime(dataArray[0], "ms")) as timestamp,
    double(dataArray[1]) as value1,
    double(dataArray[2]) as value2,
    double(dataArray[3]) as value3,
    double(dataArray[4]) as value4,
    int(dataArray[5]) as flag
)

// 查看结果
select * from resultTable

代码说明 #

• replace(v, """, "\"")：将HTML转义的双引号还原为标准JSON格式的双引号
• fromJson(cleanV)：此时字符串是标准的JSON数组，可正常解析为DolphinDB的二维数组
• 转换表时，通过timestamp(datetime(dataArray[0], "ms"))将毫秒级时间戳转换为DolphinDB的timestamp类型；数值列用double转换，flag用int转换，匹配目标表结构

内容的提问来源于stack exchange，提问作者Stella.W