how to add parameterized string to a query with regex

Question

I have a JSON column that might contain the string "attribute":"my_value" (among other JSON properties), this is the query I have to search for it:

SELECT 1 
FROM TABLE 
WHERE JSON_COLUMN REGEXP '"attribute":"?"'

The execQuery function adds an apostrophe to the parametrized string and this is the query that runs:

SELECT 1 
FROM TABLE 
WHERE JSON_COLUMN REGEXP '"attribute":"'my_value'"'

This is not a valid SQL query, how can I search for this parameter to get the rows that match this value?

I am using orm package for mysql (5.6) in nodejs, this is how I use it:

execQuery(MY_QUERY, [param1], (err, data) => {
            if (err) {
                return reject({
                    error: 'cannotFetch',
                    message: err
                });
            } else {
                return resolve(data);
            }
        })

Gordon Linoff · Accepted Answer · 2019-09-29 11:32:03Z

2

If you have a JSON column, why wouldn't you just extract the value?

WHERE JSON_COLUMN->'$.attribute' = 'myvalue'

answered Sep 29, 2019 at 11:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Ali Ghaini · Accepted Answer · 2019-10-02 08:40:44Z

2

you can use JSON_EXTRACT to use JSON column in where clause

WHERE JSON_EXTRACT(JSON_COLUMN, "$.attribute") = 'myvalue'

edit:

if you use mysql 5.6 or less you must use REGEXP

WHERE  `JSON_COLUMN` REGEXP  '"attribute":[^}]*"my_value"[^}]*}'

db-fiddle

edited Oct 2, 2019 at 8:40

answered Sep 29, 2019 at 13:18

Ali Ghaini

9056 silver badges14 bronze badges

6 Comments

Anna Over a year ago

i checked my mysql version and i have 5.6.34 so it seem i cannot use that

Anna Over a year ago

my problem is that the parametized string is being sent with apostrophes so it sends '"attribute":[^}]*" 'my_value' "[^}]*}' , do you know if there is there any way to remove it?

Ali Ghaini Over a year ago

@Anna24 you must use backslash before quote (\')

Anna Over a year ago

it does make the query valid ,however now it won't match the regex query [db-fiddle.com/f/bW2HFSfEh4DQJGNqL8n8pX/3] i think i will send the parameter the whole regex string instead of trying to use it only on the value , perhaps there is no neat way of doing that in versions before 5.7 thanks for your help

Ali Ghaini Over a year ago

@Anna24 In the fiddle you sent, the table contents there is no quotes you entered in the query quotes, see this fiddle

|

Anna · Accepted Answer · 2019-10-02 11:54:06Z

1

I ended up using the following query instead:

const MY_QUERY = `SELECT 1 FROM TABLE WHERE JSON_COLUMN REGEXP ?`;

And took care of the dynamic value in the nodejs part :

const getAttributeUsageTemplate = value => (`"attribute":"${value}"`);

execQuery(MY_QUERY, [getAttributeUsageTemplate(param1)], (err, data) => {
})

this is the only solution i found for my version of mysql
the driver still surrounds the parameter with apostrophes but they don't get in the way of the regular expression

answered Oct 2, 2019 at 11:54

Anna

1631 gold badge1 silver badge9 bronze badges

Collectives™ on Stack Overflow

how to add parameterized string to a query with regex

3 Answers 3

Comments

edit:

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

edit:

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related