Search code examples
mysqlregexp-replacejson-table

CROSS JOIN JSON_TABLE with REGEXP_REPLACE and LOCATE not giving the correct result

I have a table with a column named tags. In this column, I have a comma-separated list like отказвам резервацията, отмяна на резервацията, etc.

My query looks like that:

SELECT * FROM products
CROSS JOIN JSON_TABLE(CONCAT('["', REGEXP_REPLACE(tags, ', *', '","'), '"]'),
  '$[*]' COLUMNS(tag VARCHAR(20) PATH '$')
) AS j
WHERE LOCATE(j.tag, 'отказвам резервацията');

In the example in the dbfiddle I have two records with one keyword.

INSERT INTO products VALUES
('отказвам резервацията'),('резервация');

However, when I try to locate отказвам резервацията it matches the record with the word резервация

EXAMPLE: https://dbfiddle.uk/52713UfH

Any ideas about what might be wrong?

Solution

  • You get short varchar(20) for json_table.
    Try varchar(120) or other value, sufficient for column tags. Otherwise, JSON_TABLE will return a null for 'отказвам резервацията'.

    SELECT * FROM products
CROSS JOIN JSON_TABLE(CONCAT('["', REGEXP_REPLACE(tags, ', *', '","'), '"]'),
  '$[*]' COLUMNS(tag VARCHAR(120) PATH '$')
) AS j
WHERE LOCATE('отказвам резервацията',j.tag)>0;

    And see LOCATE parameters.