Postgres, get all items from one table which ids are not in another tables JSON array column?

user_13143462 0
Viewed 65

I have two tables:

cars table which contains:

id | name
1  | Audi
2  | Mercedes
3  | BMW

electric_cars

id | cars_id | non_valid_cars (json)
1  | 1       | [1,3]
2  | 3       | [1]
3  | 2       | [2,3]

How to select all records from the cars table which are not in the non_valid_cars array of ids in the electric_cars column with id cars_id?

Also, I am using Laravel Framework, but I will translate a query into the framework.

laravel postgresql sql php
1 Answers

You can use a NOT EXISTS condition:

select c.*
from cars c
where not exists (select * 
                  from electric_cars ec
                  where ec.non_valid_cars::jsonb @> to_jsonb(c.id)
                    and ec.cars_id = c.id);

Note that the use of jsonb is recommended over json so you might want to change that to avoid the casting.

user_3303150
Related