Update object in json column

[sunillarsson]

Hi, is there a way to update a nested json object in a supabase table (for example, add comments to a post)? I know there are some json-functions built into postgres, but without being an expert it seems a bit janky at the moment.
Is it even a good idea to use json-columns in relational databases? or is it better to use a more traditional relational database design?

[sunillarsson]

The first part of my question already seem to be somewhat answered, but I'm still interested in the second part. Is it a good practice to use json in relational databases?

[steve-chavez]

Hi @sunillarsson,

I'd recommend using jsonb over json. It has some advantages like you being able to index it and use comparison operators on it(you can't do this on json). The latter is a must for using supabase-js filters on the column.

Using jsonb is fine if you want a NoSQL/schema-less data type. You should only decide against it when you don't really have a dynamic column. In this case you'd be better normalizing the column to regular data types.

For more details, check this article: PostgreSQL anti-patterns: Unnecessary json/hstore dynamic columns

[sunillarsson]

Thanks for the great reply Steve! :)