Explode MySQL string in Column

Question

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.

What version of mysql are you using? Does it support JSON functions? — Salman Arshad
– Salman Arshad, Commented Dec 18, 2018 at 6:11
You could extract the desired data using PHP functions and display it. — Salman Arshad
– Salman Arshad, Commented Dec 18, 2018 at 6:54

Tim Biegeleisen · Accepted Answer · 2018-12-18 06:37:28Z

1

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"';

edited Dec 18, 2018 at 6:37

answered Dec 18, 2018 at 6:20

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

6 Comments

Trevor Ackermann Over a year ago

SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '>"$.bats" FROM json_test WHERE col->"$.bats" = '0'' at line 1 */

Tim Biegeleisen Over a year ago

@TrevorAckermann The code is working in the demo. Maybe your version of MySQL does not support this JSON syntax.

Trevor Ackermann Over a year ago

Let me try upgrade MySQL, I am using WAMP and accodring to WAMP I have 5.6.12 MySQL installed

Tim Biegeleisen Over a year ago

I gave you a possible workaround which doesn't use the JSON functions. But, without JSON functions there would be no easy way to extract out certain fields.

Trevor Ackermann Over a year ago

WHen I use your second string I get a reply SELECT * FROM json_test WHERE c REGEXP '"bats":"1"'; but it gives me the whole column details not just the BATS I need

|

Vivek · Accepted Answer · 2018-12-18 06:22:38Z

1

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)

answered Dec 18, 2018 at 6:22

Vivek

8036 silver badges13 bronze badges

1 Comment

Trevor Ackermann Over a year ago

SQL Error (1305): FUNCTION roadside.json_extract does not exist */

Sachin · Accepted Answer · 2018-12-18 07:09:19Z

1

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%')

answered Dec 18, 2018 at 7:09

Sachin

7975 silver badges18 bronze badges

Collectives™ on Stack Overflow

Explode MySQL string in Column

3 Answers 3

Demo

6 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related