Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();

version

PostgreSQL 10.23 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-15), 64-bit

SELECT 1

create table test (data jsonb);
insert into test values ('{"cars":[{"id":1,"brand":"BMW","parts":[{"partId":5,"type":"battery"}]},{"id":2,"brand":"Mercedes","parts":[{"partId":5,"type":"battery"},{"partId":6,"type":"engine"}]}]}');
insert into test values ('{"cars":[{"id":1,"brand":"BM3W","parts":[{"partId":5,"type":"battery"}]},{"id":2,"brand":"Mercedes","parts":[{"partId":5,"type":"battery"},{"partId":6,"type":"engine"}]}]}')

CREATE TABLE

INSERT 0 1

INSERT 0 1

select x.y from test 
cross join lateral jsonb_array_elements(data->'cars') x(y) 

y

{"id": 1, "brand": "BMW", "parts": [{"type": "battery", "partId": 5}]}

{"id": 2, "brand": "Mercedes", "parts": [{"type": "battery", "partId": 5}, {"type": "engine", "partId": 6}]}

{"id": 1, "brand": "BM3W", "parts": [{"type": "battery", "partId": 5}]}

{"id": 2, "brand": "Mercedes", "parts": [{"type": "battery", "partId": 5}, {"type": "engine", "partId": 6}]}

SELECT 4

select distinct x.y->>'id',x.y->>'brand' from test 
cross join lateral jsonb_array_elements(data->'cars') x(y)
cross join lateral jsonb_array_elements(x.y->'parts') a(b)
where a.b->>'type'='battery'

?column?
?column?

2
Mercedes

1
BMW

1
BM3W

SELECT 3