MySQL 8.0

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

DROP TABLE IF EXISTS `user`;
CREATE TABLE IF NOT EXISTS `user` (
  `profile` JSON NOT NULL
);
INSERT INTO `user` (`profile`)
VALUES ('{ "year": "2001" }');
SELECT *
FROM `user`
WHERE `profile` -> "$.year" IN ("2001", "1");

profile

7b2279656172223a202232303031227d

SELECT *
FROM `user`
WHERE `profile` -> "$.year" IN (2001, 1);

profile

SELECT *
FROM `user`
WHERE CAST(`profile` -> "$.year"  AS UNSIGNED) IN ("2001", "1");

profile

7b2279656172223a202232303031227d

SELECT *
FROM `user`
WHERE CAST(`profile` ->> "$.year"  AS CHAR) IN ("2001", "1");

profile

7b2279656172223a202232303031227d