JSON field filtering query in MySQL？

Question

I now have a data table with the following fields: ID, home, away, status。
JSON data is stored in the home and away fields

id	home	away	status
1	`{"id":10, "value":0}`	`{"id":11, "value":0}`	3
2	`{"id":11, "value":0}`	`{"id":10, "value":0}`	3
3	`{"id":10, "value":0}`	`{"id":20, "value":0}`	3
4	`{"id":40, "value":0}`	`{"id":10, "value":0}`	2

Now I can use the following statement to query the expected data.
If I want to filter out the data with ID 11 in home and away during query, how can I modify the SQL?

SELECT 
    * 
FROM 
    table_name 
WHERE 
    status = '3' 
    AND JSON_EXTRACT( home, '$.id' ) = 10 
    OR JSON_EXTRACT( away, '$.id' ) = 10 
ORDER BY 
    id DESC 
    LIMIT 10

Expected data:

id	home	away	status
3	`{"id":10, "value":0}`	`{"id":20, "value":0}`	3

Your query refers to non-existing column status_id, because it's status — Justinas
– Justinas, Commented Aug 18, 2021 at 7:43

nachospiu · Accepted Answer · 2021-08-18 09:56:35Z

4

You need to add two more restrictions into WHERE clause:

Return only rows with status equal to 3, and home->id or away->id equal to 10 and home->id not equal to 11 and away->id not equal to 11.

SELECT * 
FROM table_name 
WHERE 
    status = '3' 
    AND (home->"$.id" = 10 OR away->"$.id" = 10)
    AND home->"$.id" != 11 
    AND away->"$.id" != 11
ORDER BY id DESC;

Be careful with the use of AND and OR in WHERE clauses: this status = '3' AND home->"$.id" = 10 OR away->"$.id" = 10 is like (status = '3' AND home->"$.id" = 10) OR away->"$.id" = 10 not like status = '3' AND (home->"$.id" = 10 OR away->"$.id" = 10). Sometime you need to use parenthesis.

answered Aug 18, 2021 at 9:56

nachospiu

2,0792 gold badges10 silver badges13 bronze badges

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

Comments

Slava Rozhnev · Accepted Answer · 2021-08-18 09:26:00Z

1

In this case you can use JSON_CONTAINS function as approach:

select * 
from tbl
where 
    status = 3 and 
    not json_contains(home, '11', '$.id') and 
    not json_contains(away, '11', '$.id');

Test MySQL query here

answered Aug 18, 2021 at 9:26

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

Collectives™ on Stack Overflow

JSON field filtering query in MySQL？

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related