Mysql where clause query on json column has array value

Question

I have following records in my DB.

table= defense

id   Atype
1   {"Domain_name":www.pgmobile.com , "attack": true, "probability": "9.53"}
2   {"Domain_name":www.fb.com , "attack": false , "probability": "3.35"}
3   {"Domain_name":www.pub.com , "attack": true, "probability": "8.34"}

I want to make where clause condition on Atype column. Where in column Atype attack is true...

e.g: select * from defense where attack= true // here some help needed

thanks for help.

Ma3x · Accepted Answer · 2021-04-27 07:39:14Z

1

If your Atype is defined as JSON in your table, you can use this query:

SELECT *
FROM defense
WHERE JSON_EXTRACT(Atype, '$.attack') = true;

as you can see from this DBFiddle

edited Apr 27, 2021 at 7:39

answered Apr 27, 2021 at 7:23

Ma3x

5551 gold badge6 silver badges22 bronze badges

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

5 Comments

tandheel Over a year ago

Atype is defined as VARCHAR.

Ma3x Over a year ago

I see it works with VARCHAR too. I updated the DBFiddel. :)

tandheel Over a year ago

Invalid JSON text in argument 1 to function json_extract: "Invalid value." Getting this error.

Ma3x Over a year ago

Did you find a corrispondence between your table definition and data insert with my DBFiddle? Which version of Mysql are you using?

tandheel Over a year ago

what if data in column is like this {"Domain_Name": ["www.fb.com"], "attack": [false], "Probability": [0.001]}

ammalik · Accepted Answer · 2021-04-30 06:25:17Z

0

For {"Domain_Name": ["www.fb.com"], "attack": [false], "Probability": [0.001]}

query wil be use 'like'

SELECT *
FROM defense
WHERE JSON_EXTRACT(Atype, '$.attack') LIKE '[false]';

answered Apr 30, 2021 at 6:25

ammalik

506 bronze badges

Collectives™ on Stack Overflow

Mysql where clause query on json column has array value

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related