SQL Server OPENJSON – Map JSON to a relational table
Introduction In this article, I’m going to explain how the SQL Server OPENJSON function works and how it can help you transform a JSON object into a relational database table. When using a relational database system, it’s a very good idea to design the database schema according to the relational model. However, there are specific situations when the relational model is way too strict, and that’s when a JSON column type might be very handy. For instance, as I explained in this article, when designing an audit log table, it’s much more… Read More
How to get a JSON property value using PostgreSQL
Introduction In this article, I’m going to explain how you can get a JSON property value using the ->> PostgreSQL operator. This way, we can transform a JSON object that’s stored in a json or jsonb column into a virtual relational database table. While it’s a good idea to design the database schema according to the relational model, there are also situations when the relational model is way too strict. For instance, as I explained in this article, when implementing an audit log mechanism using database triggers, it’s very convenient to store… Read More
MySQL JSON_TABLE – Map a JSON object to a relational database table
Introduction In this article, I’m going to explain how the MySQL JSON_TABLE function works, and how you can use it to transform a JSON object into a relational database table. When using a relational database system, it’s best to design the database schema according to the relational model. However, there are certain use cases when the relational model is too strict, and we are better off storing data in a JSON column type. For instance, as I explained in this article, when designing an audit log table, it’s much more convenient to… Read More
How to map multiple JPA entities to one database table with Hibernate
Introduction In this article, we are going to see how we can map multiple JPA entities to one table with Hibernate. Using multiple entities can speed up both read and write operations. This question has been a recurring theme on StackOverflow, so I decided to explain the advantages of mapping multiple JPA entities to the same database table.
Why you should never use the TABLE identifier generator with JPA and Hibernate
Introduction From a data access perspective, JPA supports two major types of identifiers: assigned generated The assigned identifiers must be manually set on every given entity prior to being persisted. For this reason, assigned identifiers are suitable for natural keys. For synthetic Primary Keys, we need to use a generated entity identifier, which is supported by JPA through the use of the @GeneratedValue annotation. There are four types of generated identifier strategies which are defined by the GenerationType enumeration: AUTO IDENTITY SEQUENCE TABLE The AUTO identifier generator strategy chooses one of the… Read More
