Array to IN operator within Database

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.

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

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.