10

Using New JSON Syntax in PostgreSQL 14 - Update - Percona Database Performance B...

 2 years ago
source link: https://www.percona.com/blog/using-new-json-syntax-in-postgresql-14-update/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client
Using New JSON Syntax in PostgreSQL 14

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:

Shell
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.

Shell
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:

Shell
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:

Why Customers Choose Percona for PostgreSQL


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK