Search code examples
sqldatabasepostgresqlpostgresql-9.4jsonb

postgresql Jsonb ? is not working

I am using postgres 9.4 and i have a table test_table where the definition is:

id (primary key) integer, meta_data json, version integer

The Table data is as follows:

1;"{"id":99,"file_name":"test.pdf"}";0
2;"{"id":101,"nest1":{"nest_name_1":"oxford"},"file_name":"test2.pdf"}";0

I want to find all the primary keys that contain the keyword oxford. so the expected result is 2.

I am still trying to find a solution and i was trying something like:

select * from test_table where meta_data ? 'oxford'

gives me:

Error: operator does not exist: json ? unknown

thats making me confused. surely postgresql 9.4 has ? operator right? what is going on?

can someone help me please.

Thanks in advance

Solution

  • The operator ? applies to jsonb, so your query should be:

    select * from test_table where meta_data::jsonb ? 'oxford';

    Note that the query wont find any row because value 'oxford' is in nested object.