How do you check if a json field in Postgres has a certain element?
I have tried with json->>'attribute' is not null and doesn't work.
Add a comment
|
4 Answers
use ->:
where (json->'attribute') is not null
6 Comments
Chad
When I try this, I get the error
cannot extract element from a scalar
maennel
@Chad you probably have to cast your content to the right type doing
where (myfield::json->'attribute') is not null.
sage88
FYI The parens aren't necessary.
Brendan Metcalfe
exists != is not null
Ether
@Brendan
column->'attribute' will only yield null if the json path does not exist. column->>'attribute' will give null if it does not exist, or if it does exist and the value is null, e.g. column = '{"attribute":null}'. |
While this works. It is better to use special operator ?:
WHERE your_column_name::jsonb ? 'attribute'
NOTE: Only for jsonb type.
6 Comments
sage88
If the column is already type jsonb, then the cast is unnecessary. For jsonb columns this is a cleaner answer.
roman
that's indeed much cleaner for jsonb
Ether
Note the
? operator only works for top-level attributes. If you want to check if a path exists lower down in the json structure, do .. where column->'foo'->'bar'->'baz' is not null, as per Roman's answer.
Mariano Ruiz
The expression
.. WHERE column->'foo'->'bar'->'baz' IS NOT NULL doesn't work at least in Postgres 9.4, you need to surround the left part with ( ), so to make the expression works: .. WHERE (column->'foo'->'bar'->'baz') IS NOT NULL
Eugen Konkov
@RocketAppliances: When you use
-> it will check existing of value foryour_column_name.attribute, but ? will check existing of attribute. That is. For example: obj.field IS NULL will return true, but obj ? 'field' will return false |
This also works if your_column_name is json type:
SELECT * FROM table_name WHERE your_column_name->>'attribute' is not NULL;
Comments
I used the if exists condition with the ? character, but it gave me an error:
"IllegalArgumentException: At least 1 parameter(s) provided but only 0 parameter(s) present in query."
So instead of using the ? operator, I switched to using the IS NOT NULL condition.
@Query(value = "select app.app_body ->> 'objectName' " +
" from application app " +
" where app.app_type = 'common' " +
" and app.is_deleted=false" +
" and app.app_body ->> 'objectName' is not null", nativeQuery = true)
List<String> getOrganizationsList();
