Array to IN operator within Database

Designing Pipelines
#1

Scenario:
We are querying a table in Postgresql and retrieving records with ID.

The query output is similar to this:

[
{

    "record_id": [
        403,
        412,
        414
    ]
}

]

After completing the processing, I want to update the postgresql table for those record_id that the processing is complete.

Problem: Converted the record_id as:
“(”+record_id.map(x => parseInt(x)).join(",")+")" before sending this to the sql query (using JDBC execute)
After this step, my output is this: “record_idSQL”: “(403,412,414)”
since record_idSQL is now a string, the moment I invoke the database query and using IN operator to find the rows with these record_ids, the adapter is adding a ’ ’ and it is erroring out.
From the error i get the following:

update abc.def set status = ‘3’ where record_id in ‘(403,412,414)’ was aborted. ERROR: syntax error at or near “$5” Position.

record_id in postgresql is a int4 datatype and is sequence field with auto incrementing.

Any help is highly appreciated.

#2

Hey @rajendraj

Why do you map(x => parseInt(x)) when you don’t need to. Simply go with join(","). Next, when you obtain the output you shouldn’t have something like ‘(403,412,414)’ but just without the single quotes, i.e. (403,412,414).
Actually the single quotes are erroring out.

I made a test pipeline for updating in Postgres, which you can take a look:

PostgreSQL update example_2020_07_06.slp (4.8 KB)

BR, Igor Micev

2 Likes
#3

Igor,

Thanks for the solution. After I posted the question, I did solve by doing join but used ANY operator provided by Postgresql which made it much more simpler to solve in my case. Thanks once again though.