Extracting a value from an Array using mysql-CodePudding

I have a column that has brand names in an array format as below:

I want to extract information associated with Brand4 for example 'price'.

I tried using the below, but that's a psql query. How can I extract this information using MySQL in GCP.

SELECT Brand_name, price
FROM table_name
Where 'Brand4'=Any(Brand_name)

CodePudding user response：

This should work in MySQL (using a string function as mention here):

SELECT * 
FROM brands 
WHERE FIND_IN_SET('Brand4',brand_name);

see: DBFIDDLE

CodePudding user response：

Provided SQL query will work in MySQL, if you will make a subquery within the parentheses, or use FIND_IN_SET instead of using ANY.
But, as stated in the MySQL documentation:

This function does not work properly if the first argument contains a comma (,) character.

So, as an alternative, you could use LIKE (simple pattern matching).
Your SQL code then would be:

SELECT `brand_name`, `price`
FROM `test`
WHERE `brand_name` LIKE "%Brand4%"

See SQLFiddle for live example.

Also, you could use LOCATE.
Or any other alternative solution.

But, I must say that storing list data in the way you do, - it's not the best practice out there.
There are plenty of ways this can be done better.
For example, using M:M (many-to-many) relationship.

CodePudding user response：

In case you made this design you really have to reconsider/redesign. Databases have there own data structures and sql is not an imparative language but a declaritve one.

If when you didn´t desing you should consider create a table out of the one column. Perhaps this is what you try.

If it is just locating a specific string in the values of a field use like

SELECT Brand_name, price
FROM table_name
Where brand_anme like '%Brand4%'

But realize this is will not always yield accurate results.

CodePudding user response：

First, the explanation for your error message is that in MySQL, ANY() accepts a subquery, not just a single column or expression. See https://dev.mysql.com/doc/refman/8.0/en/any-in-some-subqueries.html

MySQL does not have an array type. Your Brand_name column is not an array, it's a string. It happens to contain commas and square brackets, but these are just characters in a string.

So your solutions are to use various string-search functions or expressions, as other folks have suggested.

The downside to all the string-search functions is that they cannot be optimized with a conventional index. So every search will be expensive, because it requires a table-scan.

Another solution I did not see yet is to use a fulltext index.

alter table brands add fulltext index (brand_name);

select * from brands 
where match(brand_name) against ('Brand4' in boolean mode);

This may require some special handling if the brand names contain spaces or punctuation, but if they are plain words, it should work.

Read https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html to understand more about fulltext indexes.

The best solution would be to eliminate this fake "array" column by normalizing the schema to store one brand per row in another table. Then you can match strings exactly and optimize with a conventional index. But I understand you said that the table structure is not up to you.