I love using json columns in PostgreSQL, but I'm wondering if there are scenarios where jsonb would make more sense.

For example, we store a "seen by" state in our messages table and need to use several ::jsonb casts to execute our query:

UPDATE messages
SET seen_by = seen_by::jsonb || '1'::jsonb
WHERE conversation_id = 123
AND NOT seen_by::jsonb @> '1'::jsonb;

Should we be storing seen_by as jsonb and are there any downsides to it?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.