0

I have to query the citizenId in JSON column named id_card to see whether it's '1103701468998' or '1555123407115' or not.

Somehow, I can't query from the table with JSON_EXTRACT() as below sql

select * from (
SELECT
case_id, application_id, status, user_id,  id_card -> "$.citizenId" as CID
from lending_application
) a
where a.cid in
('1103701468998','1555123407115')

its return with Empty set, 1 warning (0.01 sec)

However, I can query like this

select * from (
SELECT
case_id, application_id, status, user_id,  id_card -> "$.citizenId" as CID
from lending_application
) a
where a.cid in
('1103701468998')

Is this a JSON_EXTRACT() limitation?

Peem
  • 91
  • 1
  • 9
  • Please include some sample JSON data for `id_card`. – Tim Biegeleisen Jun 24 '19 at 09:01
  • See the Mysql deocumentation on creating indexes on JSON fields: https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html – Mike Jun 24 '19 at 09:02
  • Reproduced on [db-fiddle](https://www.db-fiddle.com/f/9TUBYJpowHptnpW5mfUT5e/0) - V 5.7 and 8.0 – Paul Spiegel Jun 24 '19 at 09:31
  • @TimBiegeleisen please see [db-fiddle](https://www.db-fiddle.com/f/9TUBYJpowHptnpW5mfUT5e/0) from Paul – Peem Jun 24 '19 at 10:18
  • @Mike but if I create , let say , temp table. I still can't use the column generate from specific field in json to be used in where IN clause? – Peem Jun 24 '19 at 10:32
  • As I see it (and I've not done this myself), you're creating a virtual column on part of the JSON data. Creating an index on that will save those values and update them whenever the JSON data is modified so this should (in theory) allow you to access it in a search. Declaring column_name GENERATED ALWAYS AS (json_column->"$.json_field") should do this without the need for temporary tables. – Mike Jun 24 '19 at 10:56

0 Answers0