CODEWITHSUNDEEP
postgresql
Kartik
Kartik

Reputation: 9853

Postgres json select not ignoring quotes

I have the following table and setup

create table test (
  id serial primary key,
  name text not null,
  meta json
);

insert into test (name, meta) values ('demo1', '{"name" : "Hello"}')

However, when I run this query, this is the result

select * from test;
 id | name  |        meta
----+-------+--------------------
  1 | demo1 | {"name" : "Hello"}
(1 row)

but 

select * from test where meta->'name' = 'Hello';
ERROR:  operator does not exist: json = unknown
LINE 1: select * from test where meta->'name' = 'Hello';
                                              ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

-

select * from test where cast(meta->'name' as text) = 'Hello';
 id | name | meta
----+------+------
(0 rows)

and this works

select * from test where cast(meta->'name' as text) = '"Hello"';
 id | name  |        meta
----+-------+--------------------
  1 | demo1 | {"name" : "Hello"}
(1 row)

Can anyone tell me what the relevance of this quote is and why it's not doing a simple string search/comparison? Alternatively, does this have something to do with the casting?

Upvotes: 3

Views: 1626

Answers (1)

Jorge Campos
Jorge Campos

Reputation: 23361

That's because the -> gets a field not a value, so you need to add the cast to say to postgresql which data type you are after.

So to run your query like you want you need to use the ->> which gets the json element as text see it here on the docs JSON Functions and Operators

So your query should looks like:

select *
  from test 
 where meta->>'name' = 'Hello';

See it working here: http://sqlfiddle.com/#!15/bf866/8

Upvotes: 7

Related Questions