Postgres: How to string pattern match query a json column?

Question

I have a column with json type but I'm wondering how to select filter it i.e.

select * from fooTable where myjson like "orld";

How would I query for a substring match like the above. Say searching for "orld" under "bar" keys?

{ "foo": "hello", "bar": "world"}

I took a look at this documentation but it is quite confusing.

https://www.postgresql.org/docs/current/static/datatype-json.html

Answer 1

Use the ->> operator to get json attributes as text, example

with my_table(id, my_json) as (
values 
    (1, '{ "foo": "hello", "bar": "world"}'::json),
    (2, '{ "foo": "hello", "bar": "moon"}'::json)
)

select t.*
from my_table t
where my_json->>'bar' like '%orld'

 id |              my_json              
----+-----------------------------------
  1 | { "foo": "hello", "bar": "world"}
(1 row)

Note that you need a placeholder % in the pattern.