CODEWITHSUNDEEP
sqlmysqljsonsql-like
Ele
Ele

Reputation: 33726

SQL LIKE operator to find words in stored JSON

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"?

Upvotes: 4

Views: 22213

Answers (4)

Ele
Ele

Reputation: 33726

I could make the search using Regexp.

Maybe this is not the fastest way, but does what I need:

select * from table where json regexp '"category":"([^"]*)Category([^"]*)"';

Upvotes: 2

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658472

Looked like a Postgres question before it was later tagged .

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:

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:

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:

Upvotes: 8

Nagendra Batte
Nagendra Batte

Reputation: 1

I hope this helps.

select * from table where json @> '{"category":"Category Example"}';

Upvotes: 0

Idan Arye
Idan Arye

Reputation: 12633

Can you use a library? The "common schema" library offers a function that does just what you need:

http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/extract_json_value.html

Upvotes: 2

Related Questions