JSONB PostgreSQL Tips

After working with PostgreSQL's JSONB fields for a while I have come to grow fairly fond of them. They allow us to handle varying data points without having to modify our overall table schema. The ability to do this while still leveraging all the standard Relational Database benefits is a game changer.

Selecting specific fields from a JSONB entry

select table.jsonb_field ->> 'SpecificField' AS "DesiredColumnName" from table

Extend JSONB List

UPDATE table SET jsonb_field = jsonb_field || 'NewValue'::jsonb

Select items in JSONB List

select * from table where 1234 in (select JSONB_ARRAY_ELEMENTS(table.jsonb_field)::integer)

Do you have another JSONB tip? If so, please use the Contact page to share it with us. Thanks for reading!