The video of my presentation below walks you through the major features of the native JSON data type in PostgreSQL 9.3 and beyond.
This presentation covers the following topics:
What is JSON?
How is it available in PostgreSQL?
What's the difference between JSON and JSONB?
Accessing JSON values
Creating JSON from table data
Creating table data from JSON
Crosstabs with JSON
Indexing and JSON
When to use JSON, when to use JSONB, and when neither should be used (more…)
On 12-Dec-2017, 2ndQuadrant held the first in a series of PostgreSQL webinars. The session was conducted by Andrew Dunstan, Principal Contributor of JSON functionality to PostgreSQL and Senior Developer & PostgreSQL Committer at 2ndQuadrant.
As promised, the recording of the Webinar is now available. Those who couldn't make it to the live session, can now view Introduction to JSON data types in PostgreSQL here.
While Andrew was able to address many questions live, there were several queries that couldn’t be answered due to time restrictions. Andrew Dunstan has taken time to answer those questions below.
Q1: Since PostgreSQL 9.4 JSONB supports GIN index, does that means that GIN index has a better performance on JSONB? What about JSON?
A: There is no direct indexing (more…)
This is part of a series of blogs on Java & PostgreSQL. For links to other blogs in this series, please scroll to the end of this post.
Starting v9.2, PostgreSQL is providing native data type support for JSON objects. Subsequent releases introduced JSONB (binary formatted JSON objects) and many data manipulation functions for JSONs, making it a very powerful tool for NoSQL operations.
String manipulation and parsing are very expensive operations in a database, so although you could have potentially stored JSON objects in strings in PostgreSQL before, introduction of the native data type has taken away overheads and made throughput a lot faster for JSON manipulation.
JSON & JSONB
JSONB was introduced as a native data type in v9.4 of PostgreSQL and it stores JSON objects (more…)
The `jsonb_insert` function brought in with PostgreSQL 9.6 gives more control when inserting new values into a jsonb array and/or new keys into a jsonb object. This article introduces this new function together with some examples.
PostgreSQL 9.5 has introduced new JSONB functionalities, greatly improving its already present NoSQL characteristics. With the inclusion of new operators and functions, now it is possible to easily modify the JSONB data. In this article these new modifiers will be presented together with some examples of how to use them.
During the last October's Italian PGDay and European PostgreSQL conference, my friend Marco Nenciarini and I had the pleasure to talk about a new open source plugin for PostgreSQL, called redislog.
In that presentation ("Integrating PostgreSQL with Logstash for real-time monitoring") we provided an example of our exploration/experimentation approach, with extensive and thorough coverage of testing and benchmarking activities. If you are curious to know more about that process, please refer to the slides of that talk, which are publicly available on Prezi.
For the impatient: redislog taps into PostgreSQL's logging facility and allows DBAs to ship log events into a Redis queue, directly in JSON format, and to enter the ELK stack through the first class lane.
Devops and the (more…)
PostgreSQL has json support - but you shouldn't use it for the great majority of what you're doing. This goes for hstore too, and the new jsonb type. These types are useful tools where they're needed, but should not be your first choice when modelling your data in PostgreSQL, as it'll make querying and manipulating it harder.
Some readers will be familiar with the (anti)-pattern. EAV has been a necessary evil in database schemas for a long time. It's something we all advise against using and look down on, then resort to when reality and business requirements mean not everything can be fit into rigid fields in properly modelled relations. For those who don't know it, it's a schema design where you turn a relational database into a poor-man's key/value store using a table with object_id ("entity"), key ("attribute") and value columns. Querying it usually involves lots and lots of self-joins.
json is the new EAV - a great tool when you need it, but not something you should use as a first choice.
(Most of this also applies to PostgreSQL arrays, which are great tools for building queries, but not usually an appropriate way to store authorative data).
The introduction of the JSONB data type in PostgreSQL, definitely makes the “NoSQL” side of this relational DBMS come out: this introduction meets the requirements of all those who prefer a data structure in a “key-value” array, dictionary style (widely used in the field of development) and, at the same time, ensures all the advantages of a relational database.