CODEWITHSUNDEEP
mysqlsql
Dmitry Adonin
Dmitry Adonin

Reputation: 1204

SQL query to print part of string value

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

Upvotes: 0

Views: 400

Answers (3)

Bukse
Bukse

Reputation: 100

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.

Upvotes: 1

Raymond Nijland
Raymond Nijland

Reputation: 11602

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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522151

Don't use regex to parse JSON, instead use the JSON_EXTRACT function:

SELECT JSON_EXTRACT(json, "$.clients")
FROM yourTable;

Demo

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.

Upvotes: 4

Related Questions