SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table Cars (
  carId   varchar(20),
  carType varchar(20),
  parts varchar(200)
);


insert into Cars values
('Z001',    'Company', '[{"Value":"New","Type":"Brakes","Code":"D"},{"Value":"Upgraded","Type":"Doors","Code":"E1"}]'),
('Z002',    'Company', '[{"Value":null,"Type":"Brakes","Code":"D"},{"Value":null,"Type":"Doors","Code":"E1"}]'),
('Z003',    'Company', '[{"Value":"USed","Type":"Brakes","Code":"D"},{"Value":"New","Type":"Tires","Code":"G7"}]');
3 rows affected

SELECT carId, carType, Code, Type, [value]
FROM Cars
CROSS APPLY OPENJSON([parts], '$')
  WITH (
    Code NVARCHAR(50) '$.Code',
    Type NVARCHAR(50) '$.Type',
    [value] NVARCHAR(50) '$.Value'
  );


carId
carType
Code
Type
value


Z001
Company
D
Brakes
New

Z001
Company
E1
Doors
Upgraded

Z002
Company
D
Brakes
null

Z002
Company
E1
Doors
null

Z003
Company
D
Brakes
USed

Z003
Company
G7
Tires
New


with cte as (
  SELECT carId, carType, Code, Type, value
  FROM Cars
  CROSS APPLY OPENJSON([parts], '$')
  WITH (
    Code NVARCHAR(50) '$.Code',
    Type NVARCHAR(50) '$.Type',
    value NVARCHAR(50) '$.Value'
  )
)
select carId, carType,
  MAX(case when Type = 'Brakes' then Code end) as BrakesCode,
  MAX(case when Type = 'Doors' then [Value] end) as DoorsValue
from cte
where Type in ('Brakes', 'Doors')
group by carId, carType


carId
carType
BrakesCode
DoorsValue


Z001
Company
D
Upgraded

Z002
Company
D
null

Z003
Company
D
null


Warning: Null value is eliminated by an aggregate or other SET operation.

carId	carType	Code	Type	value
Z001	Company	D	Brakes	New
Z001	Company	E1	Doors	Upgraded
Z002	Company	D	Brakes	null
Z002	Company	E1	Doors	null
Z003	Company	D	Brakes	USed
Z003	Company	G7	Tires	New