0

I have a field with JSON files like this:

"{\"names\":[\"James\",\"Bob\",\"Kate\",\"Tim\",\"Jerald\",\"Massimo\",\"Drake\",\"Ellen\"],\"country\":[\"Usa\",\"Montenegro\",\"Turkey\",\"Spain\"]}"

I want to get only those rows where this field in the country includes 'USA'. So I tried to write smth like this:

select *
from table
where json_field :: jsonb @> '{"country":"USA"}'::jsonb  == True

However, my interpreter states that message [ERROR: type "jsonb" does not exist] Maybe there is another way how to check for the value in postgres? Also tried this

json_field->'country' @> '"USA"'

But this returns this message [ERROR: operator does not exist: json @> unknown

Improve this question
11
  • Myabe you should leave out the spaces in json_field :: jsonb? (and use json_field::jsonb) ? Commented Jul 17, 2021 at 12:10
  • @Luuk doesn't help. I assume that my version of posgres just doesn't have jsonb Commented Jul 17, 2021 at 12:11
  • can you try with jsonb -> .. Commented Jul 17, 2021 at 12:13
  • @VeKe still the same mistake Commented Jul 17, 2021 at 12:16
  • 2
    A fair warning, because if you have a name in your json like Susan you also get a match... (if your database is case insensitive) Commented Jul 17, 2021 at 13:10
Related questions
79
Check if field exists in json type column PostgreSQL
277
Check if a Postgres JSON array contains a string
1
Test for item membership in Postgres json array
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.