DEV Community

Judy
Judy

Posted on

2 1 1 1 1

Get the whole group where at least one member meets the specified condition #eg36

Below is a table queried from BigQuery database. Its 4th field has comma-separated strings.

Image description

Use Java to do this: Retain the first 3 fields, split values the 4th field, make the distinct values names of the fields from 4th to Nth, write 1 or 0 under each new field to record whether they are originally included in the current row or not, and save result as a new file. Below is the expected result:

Image description

Write SPL code as follows:

Image description

A1: Query the database through JDBC.

A2: Split up each value of the 4th field and generate a record for each distinct value; ~ is the current item.

A3: Perform row-to-column transposition – retain the first 3 columns, transform values of the 4th column to the new field names, and write 1 under each new field if they are included in the current row in the original table and 0 if they are not.

A4: Write result to a csv file.

Read How to Call a SPL Script in Java to find how to integrate SPL into a Java application.

This is one of the problems on StackOverflow. You can click on it to see that the conventional solution is quite complicated, but the SPL approach is really simple and efficient.

SPL open source address

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read full post →

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more