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

If you loved this and want more like it, sign up!


Did I make your day?
Buy me a coffeeBuy me a coffee  

(you can learn about the bigger picture I'm working towards on my patreon page )



Published

2014-9-30



Buy me a coffeeBuy me a coffee


  73% of Goal Reached


Want more like this?