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

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.

CodePudding user response：

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.

CodePudding user response：

You can convert your json array to integers, then just do a not in:

with excl as
(
    select ec.id
        , e::text::int nvid
    from electric_cars ec, json_array_elements(non_valid_cars) e
    where ec.id = 1
)
select *
from cars c
where c.id not in (select * from excl)

Without a CTE:

select *
from cars c
where c.id not in (select *
                   from (select e::text::int nvid
                         from electric_cars ec, json_array_elements(non_valid_cars) e
                         where ec.id = 1
                         ) a
                   )