Mysql – Using Regex in WHERE Clause in Mysql 5.7

jsonMySQLmysql-5.7regex

I need a regular expression which will give me only those records which match a pattern.Below is table structure

auto_id (Auto-increment)  | json_string (Text)

Sample records are:

auto_id | json_string 
--------+-----------------------------------------------------------------------------------------------
      1 | {"12":"1774","13":"V.","14":"2","16":"9","17":"39","18":"59","19":"88","20":"144","21":"236"}
      2 | {"12":"1774","13":"Y.","14":"2","16":"7","17":"39","18":"59","19":"88","20":"144","21":"236"}
      3 | {"12":"1774","13":"V.","14":"2","16":"9","17":"39","18":"59","19":"88","20":"144","21":"236"}

My Query is :

SELECT * 
FROM tbl_json 
WHERE json_data REGEXP '"14":"[1|2]"' 
AND json_data REGEXP '"16":["39|7"]' 
LIMIT 3;

This should return only record with id 2. And not 1 and 3 as "16":"39' or "16":"7" is not part of the json_string.

Does anybody have any ideas what I am doing wrong?

Best Answer

You have mixed the REGEXP syntax for alternatives (first|second) and for symbol classes [abcd]. Your query should look like that:

SELECT * 
  FROM tbl_json 
 WHERE json_data REGEXP '"14":"(1|2)"' 
   AND json_data REGEXP '"16":"(39|7)"' 
 LIMIT 3;

Related Solutions

Mysql – How to use SUBSTRING using REGEXP in MySQL

This would need to use the LOCATE and SUBSTRING syntax to get the information out of the string. The basic locate syntax you would need is explained here.

LOCATE (search str, str, [position])

search str = A string which will be searched for.

str =A string which is going to be searched.

position (optional)= Position from where (within the second argument) the searching will start .

While the substring function you need is explained here

SUBSTRING(str, pos, len)

str= A string.

pos= Starting position.

len= Length in characters.

The easier way of viewing this is to think of substring as the following SUBSTRING(str FROM pos FOR len)

The sytax I used to get the second word is below, I took advantage of the spaces that are constantly around the second word you are trying to extract.

declare @String varchar(50) ='Lorem D9801 ipsum dolor sit amet'

SUBSTRING
(
@String,
LOCATE(' ', @String),
LOCATE(' ', @String, (LOCATE(' ', @String) + 1)) - LOCATE(' ', @String)
)

PostgreSQL – Indexing for Attribute Combinations in JSON Array

Why?

The expressed requirement that artist and title must match in the same element of the JSON array is not reflected in your query, which finds all rows where at least one element matches the artist and another (possibly a different one) matches the title.

The example data for your first case was inconclusive, since the query cannot fail this way for a single array element. Your second example demonstrates the case well, though.

Solution in Postgres 9.3

There are various ways to fix this. One way would be to translate each json array element to an SQL array of composite type consisting of artist and title and match the whole type as one.

Another way would be to keep indexes like you have now (or even a single composite spanning both expressions: (json2arr(data, 'artist'), json2arr(data, 'title')). Your current query identifies possible matches. Unnest the json array data for all identified candidates and check whether both artist and title match on the same element. May or may not be efficient enough.

Better use `jsonb` in n Postgres 9.4

I am not going into detail for json / pg 9.3, because the release of jsonb in Postgres 9.4 with advanced indexing capabilities mostly obsoleted the problem. This can be implemented much simpler and more efficient with a native GIN index on a jsonb column. There are various options. To optimize for the presented case:

CREATE TABLE tracks (id serial, data jsonb);

INSERT INTO tracks (id, data)  VALUES
  (1, '[{"artist": "Simple Plan",    "title": "Welcome to My Life"}]')
, (2, '[{"artist": "Another Artist", "title": "Welcome to My Life"},
        {"artist": "Simple Plan",    "title": "Perfect"}]');

Index:

CREATE INDEX tracks_data_gin_idx ON tracks USING gin (data jsonb_path_ops);

Query:

SELECT * FROM tracks
WHERE  data @> '[{"artist": "Simple Plan", "title": "Welcome to My Life"}]';

Sequence of attributes in the JSON value and insignificant whitespace don't matter for jsonb. I added detailed information for jsonb to the referenced answer on SO:

Index for finding an element in a JSON array