How can I parse array-style string data that isn't in standard JSON format?

Question

I have data in an array-style string format that I need to parse and insert into a DolphinDB table, with each nested array element becoming a separate record. Here's an example of the data:

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

I want to transform this into a table structure like:

timestamp	value1	value2	value3	value4	flag
1743051840000	87498.4	87498.8	87498.4	87498.8	0
1743051780000	87502.8	87508.6	87484	87502.2	1

This isn't standard JSON format (it lacks key-value pairs with {} braces), and DolphinDB's function fromHJson and similar functions like parseJsonTable seem to only work with proper JSON objects that have {} notation.

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 '{'.

Are there any built-in DDB functions that can handle this array-style data? Should I pre-process this data before storing it in DDB, or is there a clean way to handle it as-is?

saki · Accepted Answer · 2025-06-30 03:53:11Z

DolphinDB provides the parseExpr function which can convert strings into executable code, combined with eval() to execute it:

v = "[1,1,2,3]"
re = parseExpr(v).eval()
re
// output:
[1,1,2,3]

typestr(re)
// output:
'FAST INT VECTOR'

Therefore we can do the same thing to the array-style string, and transform it into a real array:

s = '[["1743051840000","87498.4","87498.8","87498.4","87498.8","0"],["1743051780000","87502.8","87508.6","87484","87502.2","1"]]'
v1 = parseExpr(s).eval()

v1
// output:
(["1743051840000","87498.4","87498.8","87498.4","87498.8","0"],["1743051780000","87502.8","87508.6","87484","87502.2","1"])

But it is not enough. You can still find that the value are all strings which is not match for the original type in table. Suppose the defination of the table is as below:

t = table(1:0, `time`val1`val2`val3`val4`val5, `TIMESTAMP`DOUBLE`DOUBLE`DOUBLE`DOUBLE`INT)

If we directly use tableInsert to insert v1 into t, the server will response error, because the column type is not match:

tableInsert(table, args...)
If args... is a tuple, it must have the same number of elements as the number of columns of table and each element of args... must have the same data type as the corresponding column of table.

// transpose v1 and change each element in a tuple from rows to cols
v2 = v1.transpose()
v2
// output:
(["1743051840000","1743051780000"],["87498.4","87502.8"],["87498.8","87508.6"],["87498.4","87484"],["87498.8","87502.2"],["0","1"])

tableInsert(t, v2)
// output:
// tableInsert(t, v2) => Failed to append data to column 'time'

We can use parseExpr again, and transform the string element into value element:

v3 = v2.parseExpr:E().eval:E:E()

:E is a adverb and can be used to apply a function (specified by func or operator) to each element of args.

We first use parseExpr into each element of tuple v2, and change the value into metacode:

v2.parseExpr:E()
// output:
((< 1743051840000 >,< 1743051780000 >),(< 87498.399999999994179 >,< 87502.80000000000291 >),(< 87498.80000000000291 >,< 87508.60000000000582 >),(< 87498.399999999994179 >,< 87484 >),(< 87498.80000000000291 >,< 87502.199999999997089 >),(< 0 >,< 1 >))

To execute each meta code in the 2-dimension tuple, we need to iteratively use adverb :E.

v3 = v2.parseExpr:E().eval:E:E()
v3
// output:
([1743051840000,1743051780000],[87498.399999999994179,87502.80000000000291],[87498.80000000000291,87508.60000000000582],(87498.399999999994179,87484),[87498.80000000000291,87502.199999999997089],[0,1])

After change all the value element, we can directly insert the record into table:

tableInsert(t, v3)
select * from t

// output:
time                    val1                  val2                 val3                  val4                  val5
----------------------- --------------------- -------------------- --------------------- --------------------- ----
2025.03.27T05:04:00.000 87498.399999999994179 87498.80000000000291 87498.399999999994179 87498.80000000000291  0   
2025.03.27T05:03:00.000 87502.80000000000291  87508.60000000000582 87484                 87502.199999999997089 1

Collectives™ on Stack Overflow

How can I parse array-style string data that isn't in standard JSON format?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related