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 <> '[]';

Buy me a coffee

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