CODEWITHSUNDEEP
phpmysql
Trevor Ackermann
Trevor Ackermann

Reputation: 176

Explode MySQL string in Column

I have a MySQL Database and want to search a specific column for specific data. 

{"pump":"0","track":"0","bats":"1","acc":"1","batl":"6","gpslev":"5"}

This is the info in the column I would like to extract bats when it is 0 and then display it on my PHP screen using PHP strings and echo.

I have read about SUBSTRING_INDEX but when I use it it displays everything to the left of bats. I need a search string that can search on bats and WHERE bats=0

Any assistance will be appreciated.

Upvotes: 1

Views: 609

Answers (3)

Sachin
Sachin

Reputation: 799

If your mysql version supports json then try below query.

SELECT JSON_EXTRACT(json_data, '$.bats') FROM `json_test` WHERE JSON_SEARCH(json_data, 'bats', '0%')

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

Use the JSON functions:

SELECT col->"$.bats"    -- and maybe other columns you want here
FROM yourTable
WHERE col->"$.bats" = '0';

Demo

Edit:

If your version of MySQL does not support JSON functions, we can still try using REGEXP:

SELECT *
FROM yourTable
WHERE col REGEXP '"bats":"0"';

Upvotes: 1

Vivek
Vivek

Reputation: 803

mysql> create table json_test(c json);
Query OK, 0 rows affected (0.40 sec)

mysql> insert into json_test values('{"pump":"0","track":"0","bats":"1","acc":"1","batl":"6","gpslev":"5"}');
Query OK, 1 row affected (0.15 sec)

mysql> select * from json_test;
+----------------------------------------------------------------------------------+
| c                                                                                |
+----------------------------------------------------------------------------------+
| {"acc": "1", "batl": "6", "bats": "1", "pump": "0", "track": "0", "gpslev": "5"} |
+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_extract(c,'$.bats') from json_test;
+--------------------------+
| json_extract(c,'$.bats') |
+--------------------------+
| "1"                      |
+--------------------------+
1 row in set (0.00 sec)

mysql> select json_extract(c,'$.bats') from json_test where json_extract(c,'$.bats') = "1";
+--------------------------+
| json_extract(c,'$.bats') |
+--------------------------+
| "1"                      |
+--------------------------+
1 row in set (0.00 sec)

Upvotes: 1

Related Questions