#jsonb

Percona 連載到 PostgreSQL 存 JSON object 以及增加 Index 的方式了…

先前 Percona 的人在講 MySQL 存 JSON object 的方式，現在開始講在 PostgreSQL 裡存 JSON object，並且增加 index 的方式了：「Storing and Using JSON Within PostgreSQL Part One」。 這基本上就是不想用 MongoDB，但還是有需要極為彈性而選擇用 JSON object 的需求。 首先先先建立一個表格，這邊直接用 JSONB： alice=# CREATE TABLE table1 (id SERIAL PRIMARY KEY, jb JSONB); 接著拿「A dataset of English plaintext jokes」這邊的 reddit_jokes.json 來玩，我先把 JSON 裡面的內容變成 JSON Lines 格式： cat…

A Rails backend I take care of has a model with a complex JSONB column. The value of this column may contain multiple keys, some of which can be quite verbose. In one instance, retrieving 8,000 rows associated to a project resulted in 500MB of data.

The table definitely needs some cleanup, normalization and refactoring but today I was wondering if I could exclude those big keys without changing much code.

Turns out that Postgres supports the - operator on JSONB column to exclude certain keys.

To make it work in Rails, I did something similar to the following

Since JSONB is a binary representation of JSON, I expected some reduction in size versus the full text. According to my experiments, I was wrong.

To test, I created an empty database, measured the size of the /var/lib/pgsql96/data experiment, created some records, and then checked the size again.

My test data was a ~530kB JSON document with lots of nested repeated fields. Here are some statistics on the data

18633 big.json.bz 38620 big.json.gz 529018 big.json

As you can see it compresses really well but after creating 10,000 rows with that document as field and the final sizes were:

JSON: ~455MB

Postgres allows to use the row's own fields into an UPDATE, e.g.,

UPDATE test SET value=(value+1) WHERE id = 1;

This also works for merging some new data into a JSONB column, e.g.,

UPDATE test SET data=(data||'{"c":3}') WHERE id = 1;

Until ActiveRecord supports this natively, one could (unelegantly) write a method similar to the following into a model to perform the merge in place when needed

Return a range of elements from an array in Postgres json

Index for finding an element in a JSON array

How to get elements from Json array in PostgreSQL

add a temporary column with a value

order by JSON data type postgresAsk Question

kafEEne #1: Websocket & Kafka

Say hello  to kafEEne ! A blog series which will show Kafka and Java EE examples

This one is about Kafka + (Java EE) Websocket API. Code is on Github and you can refer to the README on how to get this up and running using Docker. Although the focus is on Websocket, here is a list of other Java EE specs which have been used – EJB, CDI & a bit of JSON-B (part of Java EE 8)

Websocket based dashboard

JSON-B test drive using JAX-RS

This is a quick post about JSON-B specification which is due in Java EE 8. It’s main purposes are

Provide JSON binding i.e. marshalling and un-marshalling to and from Java objects

Eclipselink will server as Reference Implementation

Supports Java object model to JSON mapping via annotations

Supports default mappingfor implicit transformations b/w Java model and JSON (without annotation/additional…