How To Write Query In Mysql For Json Array Objects

Based on the requirement I need to change logic. I have students table and columns like id and val. I want to write a select query. Student table contains data: id val 1 {'

Solution 1:

I would use JSON_CONTAINS.. Check dbfiddle (mariadb_10.4)

syntax is JSON_CONTAINS(target, candidate[, path])

SELECT
    *
FROM`student`WHEREJSON_CONTAINS(JSON_EXTRACT(`val`, '$.stdran'), '1') 
    ORJSON_EXTRACT(`val`, '$.stdran') ISNULLORJSON_EXTRACT(`val`, '$.stdran') = '[]'

And result For input 1 is

For input 4 is

Solution 2:

I recently came up with the same problem and used these two approaches. See here: fetching records from a json object

Solution 3:

Simply use Json_Contains

CREATETABLE table1 (
  `id`INTEGER,
  `val`JSON

INSERT INTO table1
VALUES
  ('1', '{"stdId":1,"stdName":"student","stdAddress":"testLoc","stdran":[1,2,3]}'),

  ('2', '{"stdId":2,"stdName":"student2","stdAddress":"testLoc","stdran":[1,2,3,4]}'),
  ('3', '{"stdId":3,"stdName":"student3","stdAddress":"testLoc","stdran":[1]}'),
  ('4', '{"stdId":4,"stdName":"student4","stdAddress":"testLoc","stdran":[2,3]}');

SELECT id FROM table1 WHERE JSON_CONTAINS(val,'1', '$.stdran') ; 

| id |
| -: |
|  1 |
|  2 |
|  3 |

db<>fiddle here

CREATETABLEtable1 (
  `id` INTEGER,
  `val` LONGTEXT
);

INSERT INTO table1
VALUES
  ('1', '{"stdId":1,"stdName":"student","stdAddress":"testLoc","stdran":[1,2,3]}'),

  ('2', '{"stdId":2,"stdName":"student2","stdAddress":"testLoc","stdran":[1,2,3,4]}'),
  ('3', '{"stdId":3,"stdName":"student3","stdAddress":"testLoc","stdran":[1]}'),
  ('4', '{"stdId":4,"stdName":"student4","stdAddress":"testLoc","stdran":[2,3]}');

SELECT id FROM table1 WHERE JSON_CONTAINS(val,'1', '$.stdran') ; 

| id |
| -: |
|  1 |
|  2 |
|  3 |

db<>fiddle here