By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE table js (val varchar(1000))
INSERT INTO js VALUEs ('[{"confidence":99,"iso_code":"NJ","geoname_id":5101760,"names":{"en":"New Jersey","es":"Nueva Jersey","fr":"New Jersey","ja":"\u30cb\u30e5\u30fc\u30b8\u30e3\u30fc\u30b8\u30fc\u5dde","pt-BR":"Nova J\u00e9rsia","ru":"\u041d\u044c\u044e-\u0414\u0436\u0435\u0440\u0441\u0438","zh-CN":"\u65b0\u6cfd\u897f\u5dde"}}]
')
INSERT INTO js VALUEs ('')
SELECT * FROM js WHERE REPLACE(SUBSTRING_INDEX(SUBSTRING(vaL,LOCATE('"iso_code":"',val) + 11),',',1),'"','') IN ('NJ','NI')
| val |
|---|
| [{"confidence":99,"iso_code":"NJ","geoname_id":5101760,"names":{"en":"New Jersey","es":"Nueva Jersey","fr":"New Jersey","ja":"u30cbu30e5u30fcu30b8u30e3u30fcu30b8u30fcu5dde","pt-BR":"Nova Ju00e9rsia","ru":"u041du044cu044e-u0414u0436u0435u0440u0441u0438","zh-CN":"u65b0u6cfdu897fu5dde"}}] |
SELECT
REPLACE(SUBSTRING_INDEX(SUBSTRING(vaL,LOCATE('"iso_code":"',val) + 11),',',1),'"','') from js
| REPLACE(SUBSTRING_INDEX(SUBSTRING(vaL,LOCATE('"iso_code":"',val) + 11),',',1),'"','') |
|---|
| NJ |