regular expression to extract json values in mysql field

Question

I have a "users" table with an "assignments" field that has a list of course IDs and when then are assigned and whether they are required or optional in one json-like string (missing the top-level braces)

"BUS1077":{"startDate":"2013-09-16","hasPrerequisite":"","list":"required"},
"CMP1042":{"startDate":"2013-09-16","hasPrerequisite":"","list":"optional"},
"CMP1108":{"startDate":"2013-09-16","hasPrerequisite":"","list":"required"}

I have a another table, called "progress" that lists the course ids, like BUS1078, and whether they are completed or not.

I need a query to select the users who have completed all their required courses.

somthing like:

SELECT userid FROM users 
where (count([ids from users.assignments where list:"required"] as courseid)
      =count([extracted ids] joined using( courseid) where "complete"=1))

so there are just two tables

users (userid,assignments)
progress (id,userid,courseid,complete)

in the end I want to have selected the userids where each REQUIRED course is complete

(note, the database itself is much more complex, but this represents the gist of the problem)

Mischa · Accepted Answer · 2014-06-30 07:30:47Z

2

As of MySQL 5.1 you can do this with built-in functions of common_schema you can use for this purpose. I haven't used it myself but I've found a nice blog about how you can parse JSON stored data and do something usefull with it.

The blog: http://mechanics.flite.com/blog/2013/04/08/json-parsing-in-mysql-using-common-schema/

answered Jun 30, 2014 at 7:30

Mischa

995 bronze badges

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

3 Comments

AwokeKnowing Over a year ago

This looks good, as long as I can get the DB admin to install it!

Mischa Over a year ago

Apparently you can download common_schema as a .sql file and run it yourself to install it. Still might wanna update your DB admin :). How to install: common-schema.googlecode.com/svn/trunk/common_schema/doc/html/…

AwokeKnowing Over a year ago

yeah, I found it, but you have to have root privileges on the DB server because it creates a new database. We'll see what happens..

richardtallent · Accepted Answer · 2013-12-23 22:27:20Z

1

I'm not familiar with the RegEx implementation in MySQL, but this basic approach should work:

SELECT userid FROM users WHERE NOT EXISTS(
     SELECT NULL FROM assignments WHERE NOT EXISTS(
        SELECT NULL FROM progress WHERE
            progress.userid = users.userid
            AND REGEXMATCH(
               assignments.assignment, 
               '(^|,)"' + progress.courseid + '":.*?"list":"required"\}') >= 0
            )
        )
     )

This should find all users where there is not a required assignment that the user hasn't completed.

Given the course IDs and the word "required" are unlikely to appear out of context, the regular expression itself could likely be much more naive, such as:

  '"' + progress.courseid + '"[^}]+"required"'

I don't know about MySQL's current limitations when it comes to correlated subqueries, but the same thing could be accomplished with joins. Using EXISTS should be preferred over COUNT, since counting requires aggregation across the entire dataset rather than allowing a short-cut on the first non-match found.

answered Dec 23, 2013 at 22:27

richardtallent

35.5k14 gold badges87 silver badges126 bronze badges

5 Comments

AwokeKnowing Over a year ago

could you translate this simpler regular expression to "english" so I can verify it will work with the json strings?

richardtallent Over a year ago

After the variables from SQL are replaced, it ends up being soomethiing like "CMP1042"[^}]+"required". This translates to: find "CMP1042" in quotes followed by at least one non-} character, followed by the word "required" in quotes.

AwokeKnowing Over a year ago

ok, yes that should match, now let me translate the query into the actual database query and get back to you in a few minutes

AwokeKnowing Over a year ago

the second line says "select null from assignments". should that be select null from users? and later, users.assignment.

richardtallent Over a year ago

The first line pulls from the users (which are the records you want to return). The second line checks for assignments, so if that table is called user.assignments then yes, that is what it should be. (And if user.assignment records are specific to a user, which I didn't assume to be the case, you'll need an additional term in its WHERE clause to link back to the users table.)

Tin Tran · Accepted Answer · 2013-12-24 03:58:32Z

if your courseid is always 7 characters long and the list in assignments field can have up to maximum of 10 courses you can use this sqlFiddle

SELECT U.userId
FROM users U
WHERE NOT EXISTS
  (SELECT 1 FROM
        (SELECT users.userid,courseName,
              (Assignments REGEXP CONCAT('"',courseName,'"[^}]+(:"required"})'))as Required,
               Assignments,
               courseid,complete
         FROM
           (SELECT userid,courseName FROM
                (SELECT userid,SUBSTRING_INDEX(SUBSTRING_INDEX(assignments,'":{"startDate',course.num),'"',-1) as courseName
                 FROM users,(SELECT 1 as num 
                             UNION SELECT 2
                             UNION SELECT 3
                             UNION SELECT 4
                             UNION SELECT 5
                             UNION SELECT 6
                             UNION SELECT 7
                             UNION SELECT 8
                             UNION SELECT 9
                             UNION SELECT 10)course
                 )T WHERE LENGTH(courseName)=7
           )Courses
         INNER JOIN users ON users.userid = Courses.userid
         LEFT JOIN progress ON users.userid = progress.userid
                       AND Courses.courseName = progress.courseId
                       AND progress.complete = 1
        )AllCourses
   WHERE AllCourses.userId = U.userId
   AND AllCourses.Required = 1
   AND Complete IS NULL
   )

What the query does is it grabs the courseName(s) from assignment fields and see if it's required and sets required flag, then LEFT JOIN with progress and we have the Required column and Complete is NULL when the course doesn't exist in progress or when complete is not 1. We then select user id WHERE there does not EXISTS (a record in their Courses where Required = 1 AND Complete IS NULL)

In the fiddle, I have user 2 having only completed an optional course. So userId 2 is not returned.

You can just run the inner select for AllCourses subquery and see the data of all the courses for all users and whether they completed a course that is required or not.

Collectives™ on Stack Overflow

regular expression to extract json values in mysql field

3 Answers 3

3 Comments

5 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

5 Comments

Comments

Related