#Postgres json query array how to

This gets cumbersome, and usually leads to frequent database schema upgrades. Adding tables – adding one or more new tables for each new entity.Representing complex, extensible data in a relational database is a decades-old problem and many solutions have been used: Costs in performance, storage usage, and maintainability rise exponentially for every degree-of-freedom added to the dataset. Relational databases are the poster child for rigid data schemas. For this article, we’ll focus on the database storing the data behind all of this. When building a system handling complex data, technology decisions boil down to finding the right balance of flexibility, performance, and maintainability.

#Postgres json query array how to

This post will explain how to build just such a beast and we’ll do it using PostgreSQL’s powerful mix of relational and JSON capabilities! Technology Choices The final straw is that you have been tasked with the impossible job of adding a change-log to your database that tracks certain changes of certain entities, keeping both the old and new data, and consolidating it into a single, unified stream to inform downstream systems. To make matters worse, you may have new entities being discovered and added over time – like shooting scripts, image rights, embedded ad copy, etc. Essentially, each entity has its own schema. Add in Places, Things, Events, Products, and other references you don’t even know about yet, and it gets pretty complicated.Įach of these entities have different but overlapping content types, metadata types, and references. People are also involved in the Story – some involved in the making of the Story (author, editor, photographer, etc), some are referenced in the Story. The publication has articles or stories, which contain references to a mix of Copy, Images, Videos, and Ads. Let’s take a look at a fairly common use case: a Content Management System (CMS) for an online publication. I’d like to explain the decades-old issue we’re working on here. Are you looking at your stale, flat relational database and thinking: “It’s so rigid and lifeless!” Do you need polymorphism? Do you have a NoSQL database that has no obvious shape and is causing you lack-of-relationship nightmares? With JSON in PostgreSQL, you can have your cake and relationships too! What Problem Are We Trying to Solve?