I can select records that match a certain json value like where properties->>'foo' = 'bar', but what if the key 'foo' has not yet been set? I've tried where properties->>'foo' IS NULL but I get an error
No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT "merchants".* FROM "merchants" WHERE (properties->>'foo' IS NULL)
It's an operator precedence issue. IS NULL binds more tightly than ->>, so your code is being read as properties ->> ('foo' IS NULL). Add parentheses - (properties ->> 'foo') IS NULL.
regress=> SELECT '{"a":1}' ->> 'a';
?column?
----------
1
(1 row)
regress=> SELECT '{"a":1}' ->> 'b';
?column?
----------
(1 row)
regress=> SELECT '{"a":1}' ->> 'b' IS NULL;
ERROR: operator does not exist: unknown ->> boolean
LINE 1: SELECT '{"a":1}' ->> 'b' IS NULL;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
regress=> SELECT ('{"a":1}' ->> 'b') IS NULL;
?column?
----------
t
(1 row)
