...

Split and Extend Each String in a Specific Column to Multiple Rows

 
record split(3)

Problem description & analysis

We have a database table SAMPLE. The data is as follows:

CLIENT

DATA

1

["a","b","c"]

2

["k"]

We are trying to split the JSON-format strings in DATA column and generate a row for each split character, as shown below:

CLIENT

DATA

1

a

1

b

1

c

2

k

Solution

Write the following script p1.dfx in esProc:

A

1

=connect("demo")

2

=A1.query@x("SELECT * FROM SAMPLE")

3

=A2.news(json(DATA);CLIENT,~:DATA)

Explanation:

A1   Connect to the database named demo.

A2  Return the query result as a table sequence and auto-close the database connection when the query is finished.

A3   Parse each DATA value into a sequence according to JSON format and split members into multiple rows.

See How to Call an SPL SCript in Java to learn about the method of integrating the SPL script with a Java program.

Q & A Collection

https://stackoverflow.com/questions/64145560/json-array-column-split-into-rows-sql


record split(3)
 
 •  218 View  •