3

I have this JSON stored in 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 this moment Im doing it this way, but only return a complete phrase:

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

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

Updated:

I'm using MySQL Thanks

SQB
  • 3,926
  • 2
  • 28
  • 49
Ele
  • 33,468
  • 7
  • 37
  • 75
  • 3
    You should store that data in a table rather than a column. – Musa Jul 16 '13 at 23:01
  • 1
    What DBMS are you using? There may be tools which could help with this (e.g. recent versions of Postgres include a JSON datatype). However, as Musa says, the need to perform this kind of query does suggest that normalising that data into a full table of its own would be a good idea. – IMSoP Jul 16 '13 at 23:17
  • `categories with "Category" word` or `categories with "Category word"`, which will it be? – Erwin Brandstetter Jul 17 '13 at 00:15
  • Im using MySQL, and as Musa said the best way is using a Table. But, in this moment I have to store data as JSON. – Ele Jul 17 '13 at 00:47

4 Answers4

5

While undeclared this looks like a Postgres question.

There are hardly any JSON-processing tool in the current version 9.2. But a whole set of tools will be shipped with the upcoming Postgres 9.3 currently in beta.

I interpret your question as:

Find all rows where the json column contains one or more fields named 'category' holding a value that contains the string 'Category'.

One ore more? Not sure if Postgres enforces uniqueness, I don't have a 9.3 installation at hand.

With Postgres 9.3, your query could look like this:

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

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

Use ILIKE for a case insensitive search.

More in this related answer:
How do I query using fields inside the new PostgreSQL JSON datatype?

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I didnt know that. Im using MySQL right now. In any case, +1 for a helpful answer. – Ele Jul 17 '13 at 00:49
2

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

Idan Arye
  • 12,402
  • 5
  • 49
  • 68
2

Maybe I asked a really bad question, because I could make the search using Regexp.

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

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

Thanks

Ele
  • 33,468
  • 7
  • 37
  • 75
0

I hope this helps.

select * from table where json @> '{"category":"Category Example"}';
Chris Catignani
  • 5,040
  • 16
  • 42
  • 49
Nagendra Batte
  • 1