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.
Advertisement
Answer
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.