Postgres json select not ignoring quotes

Question

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?

Jorge Campos · Accepted Answer · 2015-10-12 01:55:09Z

7

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

answered Oct 12, 2015 at 1:55

Jorge Campos

23.9k7 gold badges65 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres json select not ignoring quotes

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related