MySQL REGEX to convert json to comma separated keys to use in WHERE IN

Question

I have some columns VARCHAR which contains JSON content with relation to other rows of the same table, and i need to convert this into comma separated keys to use a WHERE IN.

Example column content:

[{"manuel":"Manuel Fernandez"},{"marta":"Marta Flores"}]

And what i need is:

manuel,marta

This is part of a bigger query where this conversion should be applied to more than one column and thats why i think the best solution is doing this using Mysql REGEX

Update 1

Mysql version 5.7.25

You can't use `WHERE IN` with a comma-delimited string, you have to use `FIND_IN_SET()`. — Barmar, Jun 27 '19 at 08:30
What version of MySQL are you using? You need 8.0 to be able to do `REGEXP_REPLACE()`. — Barmar, Jun 27 '19 at 08:30
That info would be quite easy to retrieve from a regular RDBMS layout. Is there some overriding reason to store as JSON in the first place? — Rick James, Jun 28 '19 at 20:23
@RickJames unfortunately this is a legacy system and i cant modify the table structure :/ a simple id relation table will be simple and great for this scenario :) — Ricardo Albear, Jul 01 '19 at 11:00

score 1 · Answer 1 · answered Jun 27 '19 at 08:46

You need to remove [, ], {", and everything from ":" to the next } from the string.

regexp_replace(columnName, '\\[|\\]|\\{"|":"[^}]*\\}', '')

You need MySQL 8.0 to get a built-in REGEXP_REPLACE() function. If you're using an older version, see How to do a regular expression replace in MySQL?

MySQL REGEX to convert json to comma separated keys to use in WHERE IN

1 Answers1