Matching with JSON type column using JSON_SEARCH in MySQL-Compatible Databases

Company names are never as clean as they look in a spec. The same business gets registered with a legal Ltd/PLC name (Tesco Stores Ltd), traded under a completely different short or brand name (Tesco), referred to by an abbreviation (RAC), and shows up on invoices, emails, and API payloads under whatever spelling the sender happened to type — ERAC UK LTD, Enterprise Rent-A-Car, Enterprise Rent-A Car. A single name column and a LIKE clause will miss most of those, because you’d need to know in advance which variant the user typed.

The fix doesn’t need a separate aliases table or a search engine. MySQL’s JSON_SEARCH function, combined with a JSON column, gets you there with one extra OR in the query — store every known variant (legal name, short name, abbreviation, previous trading name) as a JSON array next to the canonical name, and match against all of them at once.

1. The Schema

Store the canonical name as normal, and keep every known variant — Ltd name, short name, abbreviation, old trading name, whatever the business is known by — in a JSON array column:

CREATE TABLE companies (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    name    VARCHAR(255) NOT NULL,
    aliases JSON NULL
);

Which gives you rows like:

RAC shows the abbreviation-and-trading-name case — a short, everyday name with the fuller legal and marketing names as aliases. Tesco shows the mirror case — a well-known short name as the canonical entry, with the registered Ltd name, the PLC name, and an all-caps variant as aliases. Same column, same query, either direction.

2. Writing the Aliases

JSON_ARRAY() builds the array directly in SQL — no need to hand-encode JSON in application code:

UPDATE companies
SET aliases = JSON_ARRAY('ERAC UK LTD', 'Enterprise Rent-A-Car', 'Enterprise Rent-A Car')
WHERE id = 1;

UPDATE companies
SET aliases = JSON_ARRAY('Tesco Stores Ltd', 'Tesco PLC', 'TESCO')
WHERE id = 2;

3. Searching Both Columns at Once

This is the part that actually solves the problem — match on the canonical name or any alias, in a single query:

SELECT * FROM companies
WHERE name LIKE '%ERAC UK LTD%' OR JSON_SEARCH(aliases, 'one', '%ERAC UK LTD%') IS NOT NULL;

That query finds row 1 even though name is just RAC — ERAC UK LTD only exists in aliases. It works the other way round too, against row 2:

-- Someone searches the full legal name, but the canonical `name` is just "Tesco"
SELECT * FROM companies
WHERE name LIKE '%Tesco Stores Ltd%' OR JSON_SEARCH(aliases, 'one', '%Tesco Stores Ltd%') IS NOT NULL;

It doesn’t matter which variant — Ltd name, short name, abbreviation — ends up in name and which end up in aliases. The OR covers both, so the search works regardless of which name the row was originally created under.

JSON_SEARCH(json_doc, one_or_all, search_str) walks the JSON document looking for a string value matching search_str, and returns the path to it (e.g. "$[0]") — or SQL NULL if nothing matches. Since we only care whether something matched, not where, wrapping it in IS NOT NULL turns it into a plain boolean check that slots neatly into the OR.

In our filter-driven query, that turns into something like:

name LIKE '%{filter[name]}%' OR JSON_SEARCH(aliases, 'one', '%{filter[name]}%') IS NOT NULL

— the same {filter[name]} value reused on both sides, so one search box matches against the canonical name and every alias in one pass.

4. The Gotcha: No Wildcard Means Exact Match

This is the bit that catches people out the first time. search_str in JSON_SEARCH follows LIKE semantics — which means without a %, it’s an exact match, not a substring search.

-- Matches only an alias that is EXACTLY "ERAC UK LTD"
JSON_SEARCH(aliases, 'one', 'ERAC UK LTD')

-- Matches any alias that CONTAINS "ERAC UK LTD"
JSON_SEARCH(aliases, 'one', '%ERAC UK LTD%')

Drop the % and a perfectly reasonable-looking query will silently return nothing for partial matches, with no error to point you at why. If the search term comes from user input — a filter box, an autocomplete field — wrap it in %...% yourself before it reaches the query, exactly as the snippet above does.

One side effect worth knowing: because % and _ are wildcards, a search term that happens to contain a literal % or _ (a company name with an underscore, say) will be interpreted as a wildcard unless you escape it. JSON_SEARCH supports a custom escape character as a fourth argument if that’s a real risk for your data.

5. 'one' vs 'all'

The second argument controls how much work JSON_SEARCH does:

• 'one' — stops at the first match and returns a single path. Cheaper, and all we need for an existence check.
• 'all' — finds every matching path and returns them as a JSON array. Useful if you actually need to know which alias matched, not just that one did.

For a filter/search query where you only branch on IS NOT NULL, 'one' is the right choice — there’s no reason to make MySQL keep scanning the array after it already knows the row qualifies.

6. A Note on Performance

JSON_SEARCH scans the JSON document row by row — it isn’t index-accelerated the way a plain column comparison is, even with a functional index on the JSON path, because the wildcard search defeats equality-based indexing. For a companies table with a few thousand rows, this is a non-issue. If you’re doing this kind of alias matching against millions of rows, it’s worth reaching for either:

• A proper company_aliases join table with an indexed alias column, or
• MySQL 8’s full-text search on a generated column that flattens the JSON array into text.

For most day-to-day company lookups, though, JSON_SEARCH on a small JSON array is simpler than either of those and fast enough not to matter.

7. Where This Works

JSON_SEARCH and the JSON column type have been available since MySQL 5.7.8, and carry through unchanged in MariaDB, Amazon RDS for MySQL, and Amazon Aurora MySQL — so this pattern works without modification across the MySQL-compatible databases most AWS-based stacks already run on.

Takeaways

• Keep known aliases as a JSON array column next to the canonical name — no extra table needed for a handful of aliases per row.
• Combine name LIKE '%...%' with JSON_SEARCH(aliases, 'one', '%...%') IS NOT NULL to search both in one query.
• Always wrap your search term in %...% — JSON_SEARCH without wildcards is an exact match, not a substring search, and it fails silently.
• Use 'one' for existence checks, 'all' only when you need to know which alias matched.
• Reach for a proper join table or full-text search once the table — or the aliases per row — gets large enough that a full JSON scan actually shows up in your query times.