SQL LIKE operator to find words in stored JSON

Question

I have this JSON stored in a MySQL DB, column name: json

'{"brand":"1","year":"2008","model":"2","price":"2001212","category":"Category Example"}'
'{"brand":"1","year":"2008","model":"2","price":"2001212","category":"Category Example2"}'

I want to make a search using LIKE operator to find all categories with "Category" word:

At the moment I'm doing it this way, but it only return a complete phrase:

select * from table where json like '%"category":"Category Example"%';

How can I build a query that returns all categories containing the word "Category"?

Answer 1

Looked like a Postgres question before it was later tagged mysql.

I interpret the question as:

"Find all rows where the JSON column contains a field named 'category' holding a value that contains the string 'Category'."

Postgres 12+ and data type jsonb

SELECT *
FROM   tbl
WHERE  jsb_col @? '$.category ? (@ like_regex "Category")';

Make this fast with a generic GIN index like:

CREATE INDEX tbl_jsb_col_path_ops_idx ON tbl USING gin (jsb_col jsonb_path_ops);

fiddle

See:

• Pattern-matching for JSON values
• Find rows containing a key in a JSONB array of records

Postgres 9.3+

SELECT *
FROM   tbl
WHERE  json->>'category' LIKE '%Category%'

->> .. "Get JSON object field as text"

Works for data types json and jsonb alike.

Use ILIKE for a case insensitive search.

See:

• How do I query using fields inside the new PostgreSQL JSON datatype?

Make this fast with a tailored trigram index on just the one JSON key:

CREATE EXTENSION pg_trgm;  -- once

CREATE INDEX tbl_jsb_col_trgm_ids ON tbl USING gin ((jsb_col->>'category') gin_trgm_ops);

fiddle

See:

• PostgreSQL LIKE query performance variations