Making The Most of JSON Data In Your RDMS With JSON Table
MCLD 3038 | Fri 07 Aug 3 p.m.–3:45 p.m.
Presented by
-
Dave Stokes
@Stoker
https://stokerpostgresql.blogspot.com/
Dave Stokes is the Community Manager for DBeaver. He is a well-known contributor in the world of open-source databases.
Dave Stokes
@Stoker
https://stokerpostgresql.blogspot.com/
Dave Stokes is the Community Manager for DBeaver. He is a well-known contributor in the world of open-source databases.
Abstract
JSON is the data interchange format of choice. But it is problematic in relational databases like PostgreSQL or MySQL because it violates so many basic assumptions. But all the databases support storing JSON data. But you cannot use many SQL functions, such as aggregates or simple comparisons, with JSON data. JSON_TABLE() takes your JSON-formatted data and temporarily converts it into a relational format for use with traditional SQL functionality. We will start with a simple case, build to more complex cases with embedded JSON and missing data, and then proceed to error handling.
There are good reasons to store data in your database in JSON. And you can mitigate the violations of Codd's rules by using JSON_TABLE(), living happily ever after.
JSON is the data interchange format of choice. But it is problematic in relational databases like PostgreSQL or MySQL because it violates so many basic assumptions. But all the databases support storing JSON data. But you cannot use many SQL functions, such as aggregates or simple comparisons, with JSON data. JSON_TABLE() takes your JSON-formatted data and temporarily converts it into a relational format for use with traditional SQL functionality. We will start with a simple case, build to more complex cases with embedded JSON and missing data, and then proceed to error handling.
There are good reasons to store data in your database in JSON. And you can mitigate the violations of Codd's rules by using JSON_TABLE(), living happily ever after.