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.
Thank you so much for your help.
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.