How to write query in MySQL for JSON array objects

Question

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?

Indra Kumar S · Accepted Answer · 2021-06-24 02:45:46Z

1

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

For input 4 is

edited Jun 24, 2021 at 2:45

answered Jun 22, 2021 at 16:56

Indra Kumar S

2,9552 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vinodh kumar Over a year ago

Hi @Indra Kumar. I updated my question. if you can help me on this one, please?

coco-candy · Accepted Answer · 2021-06-22 16:53:29Z

1

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

answered Jun 22, 2021 at 16:53

coco-candy

134 bronze badges

1 Comment

coco-candy Over a year ago

if its still not clear you can ask me further in comments. PS: I was dealing with strings and your dealing with int values.

nbk · Accepted Answer · 2021-06-22 17:17:48Z

1

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

edited Jun 22, 2021 at 17:17

answered Jun 22, 2021 at 17:06

nbk

49.6k8 gold badges32 silver badges53 bronze badges

5 Comments

Indra Kumar S Over a year ago

val JSON or val longtext which one is right ?

nbk Over a year ago

that doesn't matter MySQL auomnatically converts it, see second example

Indra Kumar S Over a year ago

I tried both, first example SELECT * gives some kind of encrypted values. But longtext is fine. Thats why i asked

vinodh kumar Over a year ago

Thanks @nbk . Your query working good. I have a one doubt. How can we retrieve where stdran=[] (empty array). and also want one more query where stdran[] not contains 1. can you help me on this please?

nbk Over a year ago

you know the rules one question only, accept and upvote the answer and ask a new one, but as a hint the NOT JSON_COntaINS for the latter

Collectives™ on Stack Overflow

How to write query in MySQL for JSON array objects

3 Answers 3

1 Comment

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related