CODEWITHSUNDEEP
javamysqlsqlpostgresql
vinodh kumar
vinodh kumar

Reputation: 137

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 {"stdId":1,"stdName":"student","stdAddress":"testLoc","stdran":[1,2,3]}
2 {"stdId":2,"stdName":"student2","stdAddress":"testLoc","stdran":[2,3,4]}
3 {"stdId":3,"stdName":"student3","stdAddress":"testLoc","stdran":[1]}
4 {"stdId":4,"stdAddress":"testLoc","stdran":[]}
5 {}
6 {"stdId":5}

I want to show records where if stdran.size()>0 then I need to check whether 1 exists or not. if exits I need to throw that record and also I need to throw records like where val ={} and val doesn't contains stdran[] and if contains, it's stdran.size()=0

ex: if input :1
expecting output
id val
1 {"stdId":1,"stdName":"student","stdAddress":"testLoc","stdran":[1,2,3]}
3 {"stdId":3,"stdName":"student3","stdAddress":"testLoc","stdran":[1]}
4 {"stdId":4,"stdAddress":"testLoc","stdran":[]}
5 {}
6 {"stdId":5}

if input :4
expecting output
id val
2 {"stdId":2,"stdName":"student2","stdAddress":"testLoc","stdran":[2,3,4]}
4 {"stdId":4,"stdAddress":"testLoc","stdran":[]}
5 {}
6 {"stdId":5}

Can anyone help me on this, please?

Upvotes: 1

Views: 246

Answers (3)

Indra Kumar S
Indra Kumar S

Reputation: 2934

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

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

SELECT
    *
FROM
    `student`
WHERE
    JSON_CONTAINS(JSON_EXTRACT(`val`, '$.stdran'), '1') 
    OR JSON_EXTRACT(`val`, '$.stdran') IS NULL 
    OR JSON_EXTRACT(`val`, '$.stdran') = '[]'

And result For input 1 is enter image description here

For input 4 is

enter image description here

Upvotes: 1

nbk
nbk

Reputation: 49375

Simply use Json_Contains

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

CREATE TABLE table1 (
  `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

Upvotes: 1

coco-candy
coco-candy

Reputation: 13

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

Upvotes: 1

Related Questions