RaqForum 41 No.
354 View  • 
     ...

SQL, extract unique values of JSON format field from each group

 

Below is a table in PostgreSQL. Its 1st column contains IDs, according to which rows are ordered. The 2nd column is the grouping column. The 3rd column contains JSON-like values (non-standard JSON), where there are duplicate items.

id

component_type

component_names

1

INGESTION

{ingestiona,atul, ingestiona, ingestionb}

2

INGESTION

{test_s3_prerit, atul}

3

DQM

{testmigration}

4

SCRIPT

{scripta,scripta,scripta,testimportscript,testimportscript,b,c,c,x}

5

SCRIPT

{testimportscript, scripta,scripta,a,a,b,b,c}

6

SCRIPT

{Script_Python,scripta,x}

7

BUSINESS_RULES

{s3_testH_Graph}

8

EXPORT

{Export2}

Use Java to do this: Extract unique items of the 3rd column values from each group and keep them at their original positions in the original records. The task can be interpreted like this: According to the order of records in each group, delete duplicate items downward and keep the unique items at their original positions.

component_type

component_names

INGESTION

{ingestiona,atul,ingestionb}

INGESTION

{test_s3_prerit}

DQM

{testmigration}

SCRIPT

{scripta,testimportscript,b,c,x}

SCRIPT

{a}

SCRIPT

{Script_Python}

BUSINESS_RULES

{s3_testH_Graph}

EXPORT

{Export2}

Write the following SPL code:

1

=post1.query("select component_type,component_names from tb order by id")

2

=A1.run(#2=mid(#2,2,len(#2)-2).split@tc() )

3

=A2.group@u(#1).(~.run( #2=(#2[-1]|#2).id@u() ).rvs().run(#2=#2\#2[1]))

4

return A1.run(#2="{"+#2.concat@c()+"}")

A1: Query the database through JDBC.

A2: Split the 2nd field of each record into multiple items.

A3: Group records by the 1st column while keeping the original order, and handle each group: cumulate all items of the 2nd column for each record according to the order of records and remove duplicates; reverse the order of records; find difference between the cumulative result of the current record and that of the next record.

A4: Restore to the original string format and return the final result.

Read How to Call a SPL Script in Java to find how to integrate SPL into a Java application.
Source:https://stackoverflow.com/questions/78414871/appying-unique-constraint-on-text-and-text-column-in-pgsql


 
 •  354 View  •