logo CoolAJ86

Snippets for Working with JSON in PostgreSQL


 Hey! I'm available for hire (and I appreciate tips)  



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

Categories


Tags