JSON extract only first element in array no matter what my filter in query is

Question

so in the column country has two data which in json ["AX", "JP"], i want to display country 'JP' because my query says DESC. But result display is 'AX' which is wrong because its supposed to display 'JP'. This is because in my query i put $[0], so no matter what my filter in query it will always display the first element in this json ["AX", "JP"] where 'AX' is 0 in array. So how should i fix this dollar part $[0], so i can filter my data.

this is the data for passport column , inside this passport column has country data

[{"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": "-"}]

this is the query

LEFT JOIN (
      SELECT
      employees.id AS emp_id,
      JSON_UNQUOTE(JSON_EXTRACT(employees.passport, '$[0].country')) AS latest_passport_no
    FROM
      TAL.employees
    WHERE
      JSON_UNQUOTE(JSON_EXTRACT(employees.passport, '$[0].country')) IS NOT NULL
    ORDER BY
      JSON_UNQUOTE(JSON_EXTRACT(employees.passport, '$[0].country')) DESC
  ) AS latest_passports ON EMP.id = latest_passports.emp_id

Answer 1

You can use JSON_TABLE to extract all the country values from the employee.passport data. These can then be sorted descending and the "latest" value returned:

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

Output for your sample data:

id  latest_passport
1   JP

Demo on dbfiddle.uk