Compare Python list to table rows-CodePudding

I have a Python list which looks like this:

[{'id': 5, 'field1': True}, {'id': 6, 'field1': False}]

It's just a list of dictionaries containing key/value pairs. I want to look up rows from a table in a Postgres database that match on id and differ on field1.

Say my table looks like:

id   field1
-----------
5    True
6    True

Only the second row should be in the result: it matches on id = 6 but differs on field1 = true

Is there any way to achieve this with SQL or would I need to loop through it manually? My use case involves a lot of rows, and this process would be repeated many times, so I'm trying to find the most efficient way to do it.

CodePudding user response：

There's a couple of ways to do this in PostgreSQL, using a WHERE IN clause:

SELECT *
FROM test
WHERE id IN (5, 6) AND (id, field1) NOT IN ((5, True), (6, False))

or a JOIN to a values table:

SELECT test.*
FROM test
JOIN (VALUES (5, True), (6, False)) AS v(id, field1)
ON test.id = v.id AND test.field1 != v.field1

In both cases for your sample data the result is

id  field1
6   t

With large amounts of data I would expect the JOIN query to be most efficient. You can build the VALUES part of the query using something like:

ll = [{'id': 5, 'field1': True}, {'id': 6, 'field1': False}]
','.join(str(tuple(d.values())) for d in ll)

which for your sample data gives:

(5, True),(6, False)

CodePudding user response：

Your data looks like a JSON array, which you can pass as is:

SELECT t.*
FROM   jsonb_array_elements('[{"id": 5, "field1": true}, {"id": 6, "field1": false}]'::jsonb) j(obj)
JOIN   tbl t ON t.id     =  (obj->>'id')::int
            AND t.field1 <> (obj->>'field1')::bool

The same as prepared statement (Python has its own feature for this):

PREPARE qr1(json) AS 
SELECT t.*
FROM   json_array_elements($1) j(obj)
JOIN   tbl t ON t.id     =  (obj->>'id')::int
            AND t.field1 <> (obj->>'field1')::bool;

Then:

EXECUTE qr1('[{"id": 5, "field1": true}, {"id": 6, "field1": false}]');

db<>fiddle here

Be sure to have an index on tbl (id, field1).