As previously mentioned here on the blog, PostgreSQL 14 was just released. Need a primer? Umair Shahid recently put together a great overview, PostgreSQL 14 – Performance, Security, Usability, and Observability.
I recently did a deep-dive into using JSON from within PostgreSQL in a two blog post series titled Storing and using JSON within PostgreSQL, While all the information from those two blogs still is relevant with the release of PostgreSQL 14 (PG14), we need to provide a quick update to show off some improvements.
Keeping this brief: Prior to PG14, pulling fields out of JSON looked something like the following:
|
1 2 3 4 5 |
movie_json_test=# select jsonb_column->>'title' as title, jsonb_column->>'imdb_rating' as rating, jsonb_column->>'imdb_id' as imdb_id from movies_jsonb where (jsonb_column->>'title')::varchar = 'Avengers: Endgame (2019)'; title | rating | imdb_id --------------------------+--------+----------- Avengers: Endgame (2019) | | tt4154796 (1 row) |
While this worked, it is not terribly intuitive for developers who may be used to interacting with JSON in code. Starting in versions beyond 14 you can use a much more natural and user-friendly syntax to access data.
|
1 2 3 4 5 |
movie_json_test=# select jsonb_column['title'] as title, jsonb_column['imdb_rating'] as rating, jsonb_column['imdb_id'] as imdb_id from movies_jsonb where jsonb_column['title'] = '"Avengers: Endgame (2019)"'; title | rating | imdb_id ----------------------------+--------+------------- "Avengers: Endgame (2019)" | null | "tt4154796" (1 row) |
Not only can you access top-level attributes, but you can also access nested arrays and lists as well:
|
1 2 3 4 5 |
movie_json_test=# select jsonb_column['title'] as title, jsonb_column['imdb_rating'] as rating, jsonb_column['imdb_id'] as imdb_id, jsonb_column['cast'][0] from movies_jsonb where jsonb_column['title'] = '"Avengers: Endgame (2019)"'; title | rating | imdb_id | jsonb_column ----------------------------+--------+-------------+----------------------------------------------------------------------------------------------------- "Avengers: Endgame (2019)" | null | "tt4154796" | {"id": "nm0000375", "name": "Robert Downey Jr.", "character": "Tony Stark / Iron Man"} (1 row) |
Overall this syntax still will make use of all the same indexes, JSON functions, etc., that were previously available. While this is a relatively minor change, it actually should make things much more natural for developers and hopefully drive more adoption and usage
Read Our New White Paper:
Leave a Reply