By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table employees (id INT, passport JSON)
insert into employees values(1, '[{"status": 1, "country": "AX", "issued_date": "2022-07-05", "passport_no": "ABS123433NEW3", "expiration_date": "2000-06-07"}, {"status": 1, "country": "JP", "issued_date": "2022-05-01", "passport_no": "TK84773812NEW3", "expiration_date": "-"}]'),
(2, '[{"status": 1, "country": "US", "issued_date": "2022-07-05", "passport_no": "ABS123433NEW3", "expiration_date": "2000-06-07"}, {"status": 1, "country": "DE", "issued_date": "2022-05-01", "passport_no": "TK84773812NEW3", "expiration_date": "-"}]')
Records: 2 Duplicates: 0 Warnings: 0
SELECT e.id, MAX(jt.country) AS latest_passport
FROM employees e
JOIN JSON_TABLE(e.passport,
'$[*]' COLUMNS(
country VARCHAR(2) PATH '$.country'
)
) AS jt
GROUP BY e.id
| id | latest_passport |
|---|---|
| 1 | JP |
| 2 | US |