Working with JSON data in PostgreSQL

Today most common SQL-based relational database management systems (DBMS) like PostgreSQL, MySQL, MariaDB, SQL Server and Oracle offer functionality to efficiently store and query JSON data in one form or another, with varying syntax. While a standard named SQL/JSON is in the works, it is not yet fully supported by all of these DBMS. This blog post is specific to PostgreSQL.

JSON data types

In PostgreSQL there are two data types for JSON columns: json and jsonb. The former stores JSON data as-is with any formatting preserved, while the latter stores JSON in a decomposed binary format. Operations on data in jsonb format are potentially more efficient.

We’ll use the jsonb data type in the following example to store a sequence of events, for example for an event sourcing based application, in a table.

CREATE TABLE events (date TIMESTAMP NOT NULL,
                     event JSONB NOT NULL);

JSON literals look like string literals. Let’s insert some events:

INSERT INTO events (date, event) VALUES
  (NOW(), '{"type": "add_shelf", "payload": {"id": 1}}'),
  (NOW(), '{"type": "add_book", "payload": {"title": "Ulysses", "shelf": 1}}'),
  (NOW(), '{"type": "add_book", "payload": {"title": "Moby Dick", "shelf": 1}}'),
  (NOW(), '{"type": "add_shelf", "payload": {"id": 2}}'),
  (NOW(), '{"type": "add_book", "payload": {"title": "Don Quixote", "shelf": 2}}');

Querying

PostgreSQL has two operators for navigating a JSON structure: -> and ->>. The former accesses an object field by key and the latter accesses an object field as text. These operators can be used both in the SELECT clause and the WHERE clause:

SELECT event->>'type' AS type FROM events;
type
add_shelf
add_book
add_book
add_shelf
SELECT event FROM events
        WHERE event->>'type'='add_book'
          AND event->'payload'->>'shelf'='1';
event
{"type":"add_book","payload":{"shelf":1,"title":"Ulysses"}}
{"type":"add_book","payload":{"shelf":1,"title":"Moby Dick"}}

Note that in the example above the value of "shelf" is compared to a string literal ('1'). In order to treat the value as a number we have to use the CAST function, and then we can use numerical comparison operators:

SELECT event FROM events
        WHERE CAST(
          event->'payload'->>'shelf' AS INTEGER
        ) > 1;
event
{"type":"add_book","payload":{"shelf":2,"title":"Don Quixote"}}

Updating

Updating JSON object fields is a bit more complicated. It is only possible with the jsonb data type and can be done via the JSONB_SET function, which takes four arguments:

1) the original JSON,
2) a path specifying which object fields should be updated,
3) a jsonb value, which is the new value, and
4) a boolean flag that specifies if missing fields should be created.

In this example we convert all the event "type" fields from lower case to upper case:

UPDATE events SET event=JSONB_SET(
  event,
  '{type}',
  TO_JSONB(UPPER(event->>'type')),
  false
);

PostgreSQL provides a lot more operators and functions for working with JSON data. This post only covered the most basic ones. See the PostgreSQL JSON reference for more.

Recap of the Schneide Dev Brunch 2016-12-11

brunch64-borderedLast week at sunday, we held another Schneide Dev Brunch, a regular brunch on the second sunday of every other (even) month, only that all attendees want to talk about software development and various other topics. This brunch was so well-attended that we had to cramp around our conference table and gather all chairs on the floor. As usual, the main theme was that if you bring a software-related topic along with your food, everyone has something to share. Because we were so many, we established a topic list and an agenda for the event. As usual, a lot of topics and chatter were exchanged. This recapitulation tries to highlight the main topics of the brunch, but cannot reiterate everything that was spoken. If you were there, you probably find this list inconclusive:

Finland

We started with a report of one of our attendees who had studied in Finland for the last two years. He visited the Aalto university and shared a lot of cultural details about Finland and the Finnish people with us.

The two most important aspects of the report were sauna and singing. The Finnish love to visit a sauna, in fact, nearly every building has a functioning sauna. Every office building has a company sauna that will get visited often. So it might happen that your first visit of a company starts right in the sauna, naked with the bosses.

And the Finnish love singing so much that they usually start singing during the sauna session. There are open social events organized around singing together.

Alcohol plays a big role in Finland, mostly because the taxes makes it incredibly expensive to obtain a proper buzz. In the southern regions, much alcohol is imported from Russia or Estonia by ferry. There are even special ferry routes designed to be cost-neutral when shopping for alcohol. But alcohol isn’t the only thing that is made expensive with special taxes. Sugar and sugary food/drinks are heavily taxed, too. So it’s actually more expensive to eat unhealthy, which sounds like a good concept to counter some civilizational diseases.

The Finnish students often wear a special boilersuit during official events that identifies their affilition with their field of study and university. They apply patches and stickers to their suit when they have completed certain tasks or chores. It’s actually a lot like a military uniform with rank and campaign insignia. Only that the Finnish student boilersuit may not be cleaned or washed other than jumping into a body of water with you in it. And the Finnish lakes are frozen most of the year, with temperatures of -27 °C being nothing extraordinary.

As you probably have guessed right now, costs for rent and electricity are high. Our attendee enjoyed his time there, but is also glad to have the singing separated from the alcohol for the most part.

Lambdas and Concurrency

The next question revolved around the correlation between lambda expressions and concurrent execution of source code. The Vert.x framework relies heavily on lambdas and provides reactive programming patterns for Java. As such, it is event driven and non blocking. That makes it hard to debug or to reason about the backstory if an effect occurs in production. The traditional tools like stacktraces don’t tell the story anymore.

We took a deep dive into the concepts behind Optionals, Promises and Futures (but forgot to talk about the Expected type in C++). There is a lot of foggy implementation details in the different programming languages around these concepts and it doesn’t help that the Java Optional tries to be more than the C++ Optional, but doesn’t muster up the courage to be a full Monad. Whether deprecating the get()-method will make things better is open for discussion.

To give a short answer to a long discussion: Lambdas facilitate concurrent programming, but don’t require or imply it.

React.js and Tests

It was only a small step from the reactive framework Vert.x to the React.js framework in Javascript. One attendee reported his experiences with using different types of tests with the React framework. He also described the origin of the framework, mentioning the concept of Flux and Redux along the way.

Sorry if I’m being vague, but each written sentence about Javascript frameworks seem to have a halflife time of about six weeks. My take on the Javascript world is to lean back, grab some popcorn and watch the carnival from the terrace, because while we’re stuck with it forever, it is tragically unfortunate. Even presumed simple things like writing a correct parser for JSON end in nightmares.

It should be noted, though, that the vue.js framework entered the “assess” stage of the Thoughtworks Techradar, while AngularJS (or just Angular, as it should be called now) is in the “hold” stage.

Code Analysis

We also talked about source code analysis tools and plugins for the IDE. The gist of it seems to be that the products of JetBrains (especially the IntelliJ IDEA IDE) have all the good things readily included, while there are standalone products or plugins for other IDEs.

Epilogue

As usual, the Dev Brunch contained a lot more chatter and talk than listed here. The number of attendees makes for an unique experience every time. We are looking forward to the next Dev Brunch at the Softwareschneiderei in February 2017. We even have some topics already on the agenda (like a report about first-hand experiences with the programming language Rust). And as always, we are open for guests and future regulars. Just drop us a notice and we’ll invite you over next time.