0

I have JSON from maxmind in my table column with datatype varcher(1000)

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

how do I search and out put all iso_code who have NJ NY I have array of iso codes

without the JSON I would have done as follow

select geotbl.id,geotbl.email from geotbl where  iso_code IN ('NY','NJ')

not all rows have JSON text some are null or empty

here is dbfiddle : dbfiddle

skcrpk
  • 558
  • 5
  • 18

1 Answers1

1

You can use json_extract to get the shortcut for Nwe Jersey

you can of course use IN clause as well instead of the eqal

CREATE table js (val JSON)

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"}}]
')

SELECT 1 FROM js WHERE JSON_EXTRACT(val, "$[*].iso_code") = 'NJ'

|  1 |
| -: |

SELECT 

JSON_EXTRACT(val, "$[*].iso_code")  from js

| JSON_EXTRACT(val, "$[*].iso_code") |
| :--------------------------------- |
| ["NJ"]                             |

db<>fiddle here

I don't know why MySQL doesn't like your JSON, jsonlint had no problems with it, but you can always use string functions, if you don't can't use json datatype

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"}}]<br> |

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                                                                                    |
|                                                                                       |

db<>fiddle here

nbk
  • 45,398
  • 8
  • 30
  • 47
  • SELECT JSON_EXTRACT(subdivisions, "$[*].iso_code") from geo_test_datas; get error Error Code: 3141. Invalid JSON text in argument 1 to function json_extract: "The document is empty." at position 0. – skcrpk Jul 08 '21 at 18:44
  • you must provide a dbfiddle, so i can check that, as you see in principle this works – nbk Jul 08 '21 at 18:48
  • not all columns have JSON text some are null or empty , thats why the error , also the datatype of column in varchar(1000) not JSON – skcrpk Jul 08 '21 at 19:12
  • here is the dbfiddle https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e74b59c9c93199ece5ac7e438e7deb6f – skcrpk Jul 08 '21 at 19:17
  • i can't see why, so use string functions – nbk Jul 08 '21 at 19:36