Snippets for Working with JSON in PostgreSQL
Find rows where foo_text_property
is not an empty string
-- more reliable
SELECT foo_json_field FROM foo_table WHERE LENGTH((foo_json_field->'foo_text_property')::text) >= 1;
-- more conventional
SELECT foo_json_field FROM foo_table WHERE (foo_json_field->'foo_text_property')::text <> '';
Find rows where foo_array_property
is not empty
-- probably better
SELECT foo_json_field FROM foo_table WHERE json_array_length(foo_json_field->'foo_array_property') > 0;
-- also finds null and non-array values
-- especially useful in the case that you need to find old data to convert
SELECT foo_json_field FROM foo_table WHERE (foo_json_field->'foo_array_property')::text <> '[]';
By AJ ONeal
Was this useful to you? Share it!
Also, you can give me a tip or hire me.Published
2014-9-30