Ruby Rails, JSON - match results if key exists

Question

The set up is ruby on rails, in a postgres database. The table is called line_sources and a JSON column is called names. I want to return all rows where the names column contains a key called away_names. I'm trying this but they fail:

LineSource.where("names -> 'away_names'")

and

LineSource.where("names ->> 'away_names' = '%'")

score 31 · Accepted Answer · answered Aug 15 '15 at 21:24

31

Try this :

where("(names->'away_names') is not null")

answered Aug 15 '15 at 21:24

Nicolas Maloeuvre

3,069
24
42

that works. how did you know parenthesis are needed around names->'away_names' – appleLover Aug 15 '15 at 21:28
googling "postgresql json key exist" then using things from http://stackoverflow.com/questions/23911740/get-records-where-json-column-key-is-null – Nicolas Maloeuvre Aug 15 '15 at 21:30

score 5 · Answer 2 · answered Jun 06 '18 at 10:00

You can use #> to get the JSON object at that path.

where("(names #>'{away_names}') is not null")

Basic key operators to query the JSON objects :

#> : Get the JSON object at that path
->> : Get the JSON object at that path as text
{obj, n} : Get the nth item in that object

Ruby Rails, JSON - match results if key exists

2 Answers2