I have text field in MySQL 5.6 table which consists string representation of JSON. Such JSONs exist field "clients" with array of ID's, e.g.:
{
...
"clients":[1,2,3],
...
}
How to write SQL query which prints exactly this part of text from JSON for every record, e.g.:
clients
-----------------
"clients":[1,2,3]
"clients":[4,5]
...
Should I use some kind of regex or it could be done easier?
Thank you!
UPDATE: JSON_EXTRACT doesn't work for MySQL 5.6
Don't use regex to parse JSON, instead use the JSON_EXTRACT function:
SELECT JSON_EXTRACT(json, "$.clients")
FROM yourTable;
Using regex to parse JSON is generally a bad idea unless there is no alternative. In this case, more recent versions of MySQL offer a JSON API which can handle your requirement.
Well i strongly advice upgrading, this query is more meant for fun.
Query
SELECT
DISTINCT
SUBSTRING_INDEX(
SUBSTRING_INDEX(
records.cvs
, ','
, number_generator.number
)
, ','
, -1
) array_part
FROM (
SELECT
@row := @row + 1 AS number
FROM (
SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) row1
CROSS JOIN (
SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) row2
CROSS JOIN (
SELECT @row := 0
) init_user_params
) AS number_generator
CROSS JOIN (
SELECT
# parse out the 1,2,3 part from [1,2,3] part
SUBSTRING (
# parse out the [1,2,3] part
SUBSTRING(
records.json
, (LOCATE(':', records.json) + 1)
)
, 2
, LENGTH(
# parse out the [1,2,3] part
SUBSTRING(
records.json
, (LOCATE(':', records.json) + 1)
)
) - 2
) AS cvs
FROM (
SELECT
'"clients":[1,2,3]' AS json
) AS records
) AS records
Result
| array_part |
| ---------- |
| 1 |
| 2 |
| 3 |
see demo
1,2,3 string into real records with nested STRING_INDEX functions.Must be processed from SQL? I think it is more rational to process outside SQL server. Or you can upgrade your MySQL to 5.7.8+ and use JSON_EXTRACT. Everything else will be a BIG crutch.
