CODEWITHSUNDEEP
sqljsonpostgresqlpostgresql-9.1
Ram Kumar
Ram Kumar

Reputation: 590

search a keyword in postgresql json field

I am currently using postgres 9.3.3

Following is how my table looks like - 

 Column       |           Type           |                             Modifiers                              | Storage  | Stats target | Description 
--------------------+--------------------------+--------------------------------------------------------------------+----------+--------------+-------------
 id                 | integer                  | not null default nextval('playerbase_palyerdata_id_seq'::regclass) | plain    |              | 
 date_joined        | timestamp with time zone | not null                                                           | plain    |              | 
 belongs_to_camp_id  | integer                  | not null                                                           | plain    |              | 
 belongs_to_coach_id | integer                  | not null                                                           | plain    |              | 
 json_kvps          | character varying(2000)  | not null                                                           | extended |              |

One sample data is as follows - 

id |      date_joined   | belongs_to_camp_id | belongs_to_coach_id | json_kvps

1  | 2014-03-07 18:10:45.824749+05:30 |                 1 |                  1 | {"alumnicode": "2003360009", "emailusername": "[email protected]", "altemail": "", "salutation": "Mrs", "fname": "Aaron", "mname": "V", "lname": "Schwartz", "fullname": "Aaraon M Scwartz", "programmename": "MEP", "batchyearin": "2003"}

Now I want to search the entire table, and find a user with "emailusername":"[email protected]"

As mentioned here - http://www.postgresql.org/docs/current/static/functions-json.html

I try to write a query as follows - 

SELECT * FROM playerbase_playerdata WHERE json_kvps->>'emailusername' = '[email protected]';

I was expecting a column as above, instead I got the following error - 

ERROR:  operator does not exist: character varying ->> unknown
LINE 1: ...ELECT * FROM memberbase_memberdata WHERE json_kvps->>'emailu...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Could someone please tell me, what am I missing?

Upvotes: 0

Views: 4995

Answers (4)

lrv
lrv

Reputation: 290

For PostgreSQL 9.3 or higher, one possible solution could be using a cast to json:

SELECT * FROM playerbase_playerdata WHERE json_kvps->>'emailusername' = '[email protected]';

SELECT * FROM playerbase_playerdata WHERE CAST(json_kvps AS JSON)->>'emailusername' = '[email protected]';

Upvotes: 1

Sujith S
Sujith S

Reputation: 601

select [Required Fields] from  [Table Name] WHERE  [Column Name]::text = '{[Key]}' ::text

Upvotes: 0

user1456308
user1456308

Reputation:

In case of django models you can use JsonField.

https://pypi.python.org/pypi/jsonfield.

It works fine, but if you'll use PostgreSQL version less than 9.2, it'll create character column.

Upvotes: 0

user1456308
user1456308

Reputation:

Feature like this exists only from PostgreSQL release 9.3. So, unfortunately, to make queries like this you need to update your PostgreSQL.

If you have 9.3, then you need use json column type.

Here you can see some example. It helps me, earlier: http://clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json/

Have a nice day.

Upvotes: 1

Related Questions