select Id, cKey, MetaValue

from openjson('[{"id":1,"meta":[{"key":"key1","value":"ValueKey1"},{"key":"key2","value":"ValueKey2"}]},{"id":2,"meta":[{"key":"key2","value":"ValueKey2"}]},{"id":3,"meta":[{"key":"key1","value":"ValueKey1"}]}]', '$')

with (

id int '$.id',

jMeta nvarchar(max) '$.meta' as JSON

)

outer apply openjson(jMeta)

with (

cKey varchar(100) '$.key',

MetaValue varchar(100) '$.value'

)

),

cte as (

select c.id, s.cKey as cKey, c.MetaValue, max(s.cKey) over (partition by id) as max_cKey

from raw_data c

left join (select 'key1' as cKey) as s on s.cKey = c.cKey

)

select id, case when cKey = max_cKey then MetaValue else null end as MetaValue

from cte

where cKey = max_cKey or (cKey is null and max_cKey is null )