June 10, 2019
I didn’t know what the underscore meant, and looking into the data types didn’t immediately yield the confirmation I sought, so while I felt like it was an Array, I couldn’t be sure.
Fortunately, I had access to the
create table script and could eventually confirm my suspicion that it was in fact an Array.
create table if not exists metadata_fields ( id text ... , groups_reso text ... );
Now, that I had that information, I could think about how to access specific properties.
Property is always in the first position of a collection if it’s present, so, let’s say I wanted to only select rows which included that property, I would do:
SELECT id, groups_reso FROM public.metadata_fields where groups_reso = 'Property';
Or, the inverse - all records where the first property is not equal to
SELECT id, groups_reso FROM public.metadata_fields where groups_reso <> 'Property';
Attentive readers may notice that the index used here is
 to refer to the first property. That’s because “[b]y default, PostgreSQL uses one-based numbering for array elements.” ¹
Lots more to learn, but at least I can now move forward.
Written by Stephen Weiss who lives in Chicago with his wife, Kate, and dog, Finn. Follow him on Twitter!