parse field regex in mysql

Question

I've a big table in my mysql db. In this table one column contain the metadata of all the content I've. the value is quite long and I need to extract the duration of all the video. this is an extract of the field:

{"progress":100,"id":"10","duration":4285,"audio":[],"status":"published" .....

I need to take care of the value after the "duration":

The value can start to 1 and be up to 10000.

How can I do?

Roberto Conde · Accepted Answer · 2015-09-30 13:00:03Z

1

Try with something like this:

SELECT SUBSTRING(
 substr, 
 LOCATE(':', substr) + 1,
 LOCATE(',', substr) - LOCATE(':', substr) -1) as duration FROM 
(SELECT SUBSTRING(column, LOCATE('"duration":', column)) substr
 FROM log_debug WHERE column REGEXP '\"duration\":')  as foo;

The inner select gets a substring that starts at 'duration' and spans to the end of the whole string, for all rows that have the duration field (add more conditions in the where if you want).

The outer select then gets a substring starting after the first ':' and ending before the ','

answered Sep 30, 2015 at 13:00

Roberto Conde

3,3473 gold badges33 silver badges32 bronze badges

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

3 Comments

Nick Over a year ago

Hi Mamuso, thank you for your reply. Trying to run it (I have to add another where clause because of the huge quantity of rows) doesn't return nothing. then, once retrieved the value is it possible to sum it? thank you very much

Roberto Conde Over a year ago

Please update your question with the query you are making to see what may be wrong. To sum the durations, you only have to do SELECT SUM( SUBSTRING ... ) as total_duration FROM ...

Nick Over a year ago

Hi, I did it and worked like a charm. thank you very much.

Collectives™ on Stack Overflow

parse field regex in mysql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related