SQL Query to filter from an Array

Question

I wasnt sure how to title this question.

I have a database of suppliers, a simplified version is shown below.

Company	Vehicles
X	["Van", "Luton", "Artic"]
Y	["Van", "Luton", "Rigid"]
Z	["Luton", "Rigid", "Artic"]
A	["Flatbed", "Hiab", "Artic"]

I am looking for a way to write a query which will pull items based on the vehicle I am searching for...

I have a page where I can select a vehicle type from the a dropdown and the intention is to populate a table to show which companies have this vehicle but the way that it is saved to the DB ["x", "y", "z"] is trippig me up slightly as I cant seem to use

SELECT * From Table WHERE Vehicle = "input"

Is there a way that I should write the query so that it can filter words from the vehicle string?

Answer 1

Sorry, but this is a classic Data Normalisation failure.
You want to search by vehicle, but you've "buried" the vehicle data inside an array structure inside each record. Sounds great but, as you've discovered, not so easy to do in the "Relational World".

• Relational Database are generally very good at finding little "bits" of stuff and bolting them together.
• They are generally pretty rubbish at finding big blocks of stuff and pulling them apart.

Short answer: Normalise your data which, in this case, means splitting these arrays out into separate rows in a new table:

select * 
from company_vehicles ; 

+---------+---------+
| company | vehicle |
+---------+---------+
| X       | Van     |  \
| X       | Luton   |   | This is the equivalent of your { 'X', ["Van","Luton","Artic"] } 
| X       | Artic   |  /
| Y       | Van     |
| Y       | Luton   |
| Y       | Rigid   |
| Z       | Luton   |
| Z       | Rigid   |
| Z       | Artic   |
| A       | Flatbed |
| A       | Hiab    |
| A       | Artic   |
+---------+---------+

select * 
from company_vehicles 
where vehicle = 'Artic' 
order by company ; 

+---------+---------+
| company | vehicle |
+---------+---------+
| A       | Artic   |
| X       | Artic   |
| Z       | Artic   |
+---------+---------+

Finding vehicles with combinations of vehicle gets a little more complicated, but it's all standard relational syntax. For example, companies with both 'Luton's and 'Rigid's:

select company 
from company_vehicles 
where vehicle in ( 'Luton', 'Rigid' ) 
group by company 
having count( * ) = 2 
order by company ; 

+---------+
| company |
+---------+
| Y       |
| Z       |
+---------+

Answer 2

FULLTEXT index.

ALTER TABLE table ADD FULLTEXT(vehicles);

Then use

SELECT ... FROM table
    WHERE MATCH(vehicles) AGAINST("+Van +Artic" IN BOOLEAN MODE)

(Whether you need the + and/or IN BOOLEAN MODE depends on the details of your requirements.)

Answer 3

Assuming that the vehicles field is stored as an array (MySQL JSON type), here is how the data can be queried:

CREATE TABLE supplier (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company VARCHAR(10),
    vehicles JSON
);

INSERT INTO supplier VALUES (default, "A", '[ "Van", "Luton", "Artic" ]');

You can search the vehicles field using the JSON_SEARCH function:

SELECT * 
FROM supplier 
WHERE JSON_SEARCH(vehicles, 'one', 'Luton') IS NOT NULL;

This was using MySQL 8.0.34.

---

Alternate scenario:

Assuming the vehicles is stored as a string type:

CREATE TABLE supplier2 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company VARCHAR(10),
    vehicles VARCHAR(50)
);

INSERT INTO supplier2 VALUES (default, "A", '[ "Van", "Luton", "Rigid" ]');

You can search the vehicles field using the JSON_OVERLAPS function. But, the input data must be in an array (as in the query below):

SELECT *
FROM supplier2
WHERE JSON_OVERLAPS(vehicles, JSON_ARRAY("Luton")) = true;