JSONB型カラムでPostgreSQLをNoSQL風にスキーマレスに使う方法

PostgreSQL9.2以降のバージョンではjson型/jsonb型カラムが使える。

ちょっと特殊な記法を使うけど、

json/jsonbデータ内のキーをDBのカラムと同様に使えるようになるので、

スキーマレスDBのように同じテーブル内のレコードで

異なるデータ構成を持たせることができるようになる。

役に立ちそうなので触ってみた。

環境はPostgreSQL9.5。

公式ドキュメントのjson/jsonb型カラムに関する情報は下記のページに記載されている。

8.14. JSONデータ型

9.15. JSON関数と演算子

9.20. 集約関数

JSONB型カラムを持つテーブルを作成する

サンプルとして商品情報を登録するテーブルを作成してみる。

JSON系のカラム型はJSON型とJSONB型カラムがあるけど、

公式ドキュメントによると、登録するJSONデータのマップキーの順序を

どうしても守りたい事情でもない限り、JSONB型カラムを使うのが良さそうなので、

JSONB型カラムを使う。

drop table if exists jsonb_test_normal_items;                                                         
create table jsonb_test_normal_items(                                                                                                                                                  
  id serial not null,
  item_cd varchar(100),
  name varchar(100),                                                                                                                                                                   
  price numeric(19,4),
  is_enabled boolean,
  created timestamp default current_timestamp,
  primary key(id)
);


drop table if exists jsonb_test_jsonb_items;
create table jsonb_test_jsonb_items(
  id serial not null,
  js jsonb,
  primary key(id)
);

比較用に、通常のカラムを利用する商品テーブルとJSONB型カラムを使う

商品テーブルを両方作っておいた。

JSONB利用テーブルでは

item_cd, name, price, is_enabmed, createdがjsというJSONB型カラムに入る想定。

続いて、データを登録する。

速度も確認したいので、generate_series関数を使って10万件ずつデータを入れてみる。

delete from jsonb_test_normal_items;
insert into jsonb_test_normal_items(item_cd, name, price, is_enabled, created)                                                                                                         
select
  to_char(i, 'FM0000000000'),                                                                                                                                                          
  format('テスト商品%s', i),                                                                                                                                                           
  i,
  true,
  clock_timestamp()
from
  generate_series(1, 100000) as i
;

delete from jsonb_test_jsonb_items;
insert into jsonb_test_jsonb_items(js)
select
  jsonb_build_object(
    'item_cd', to_char(i, 'FM0000000000'),
    'name', format('テスト商品%s', i),
    'price', i,
    'is_enabled', true,
    'created', clock_timestamp()
  )
from
  generate_series(1, 100000) as i
;

JSONB型カラムにはjsonb_build_objectという関数を使って

生成したJSONBデータを登録してる。

下記のような直接JSONデータを書く書き方でも登録できるのだけど、

insert into jsonb_test_jsonb_items(js)
select
  ('{'
    || '"item_cd", "' || to_char(i, 'FM0000000000') || '",'
    || '"name", "' || format('テスト商品%s', i) || '",'
    || '"price", ' || i || ','
    || '"is_enabled", true,'
    || '"created", ' || clock_timestamp()
  || '}')::jsonb
  )
from
  generate_series(1, 100000) as i
;

PostgreSQLの関数を使ったりする時は

jsonb_build_object関数を使う方がスッキリ書ける。

10万件のデータを登録するくらいでは

速度面では通常カラム版とJSONBカラム版に大きな違いはなかった。

（ちゃんと測ってない）

検索

ここからはJSONB型テーブルで色々試してみる。

まずはJSONB型カラム内のテキスト、数値、boolean型のデータで

それぞれ検索する方法。

select * from jsonb_test_jsonb_items where js->>'name' like '%200%' limit 10;                                                                                                          

select * from jsonb_test_jsonb_items where (js->>'price')::numeric > 20000 limit 10;                                                                                                   

select * from jsonb_test_jsonb_items where (js->>'is_enabled')::boolean = true limit 10;

JSONB型カラムで不便なのが、

データの取得をJSONB型かテキスト型でしか直接取得できないこと。

JSONB型でデータを取得する場合は

カラム名->'キー名'

と書き、

テキスト型で取得する場合は

カラム名->>'キー名'

と書く。大なり記号が1つか2つかのちがい。

基本的にJSONB型での取得は、そのキーの下にさらに配列やマップがある場合に利用し、

直接文字列等のデータを取得する場合に文字列型での取得を利用するものと思われる。

なので、取得したデータを数値やboolean型として利用するには、

文字列として取得してから型変換する必要がある。

続いて、インデックスがどう使われるかを試してみる。

違いが出そうな数値型データ（ここではpriceデータ）を使って確認する。

下記のようなインデックスを作成した：

drop index if exists idx_jsonb_test_price;                                                            
create index idx_jsonb_test_price                                                                                                                                                      
on jsonb_test_jsonb_items((js->'price'))                                                                                                                                               
;

drop index if exists idx_jsonb_test_price_text;
create index idx_jsonb_test_price_text 
on jsonb_test_jsonb_items((js->>'price'))
;

drop index if exists idx_jsonb_test_price_numeric;
create index idx_jsonb_test_price_numeric 
on jsonb_test_jsonb_items(((js->>'price')::numeric))
;

price列に対してJSONB、テキスト、numeric型変換版の

3種のインデックスを登録した。

そして、下記のようにprice列をorder by句で使用する

クエリをexplain analyzeしてみる。

-- #1 JSONB型でorder by
explain analyze select * from jsonb_test_jsonb_items order by js->'price' asc limit 10;

-- #2 テキスト型でorder by
explain analyze select * from jsonb_test_jsonb_items order by js->>'price' asc limit 10;

-- #3 numeric型でorder by
explain analyze select * from jsonb_test_jsonb_items order by (js->>'price')::numeric asc limit 10;

結果、

1のクエリはインデックスが使用されず、

2のクエリは「idx_jsonb_test_price_text」を使用し、

3のクエリは「idx_jsonb_test_price_numeric」を使用してた。

並び順は#2が文字列の辞書順、#1と#3が数値順でソートされるので、

数値型キーをインデックス登録する際は#3のようにnumeric型だと

明示してインデックスを登録しておく必要がありそう。

データを更新する

続いて、JSONB型カラムのデータを更新してみる。

INSERT方法はテーブル作成のところで試したし、

DELETEは個々のカラムには関係ないので、

既存データのUPDATE方法について調べる。

基本的にJSONB型カラムはテーブルカラムとしては1つのカラムなので、

JSONデータ内の個別更新ではなく、一括更新が基本となっている。

一応「jsonb_set」という、キーを指定して値を更新できる関数は用意されているが、

このキーとこのキーとこのキーの値を更新する、みたいな時には使いづらそう。

とはいえ、いちいち元のデータを取得してから必要な値を変更して更新するのも

面倒なので、変更したいキーと値のセットを渡すだけで他のキーの値を維持して

更新できるマージ方法はないか、と探してみたら「||」演算子があった。

「||」演算子はJSONB型データを結合し、同じキーを持ったJSONを結合すると

後で渡した値を優先してマージしてくれる。

下記のようなUPDATE文を書くのが一番ラクそう。

update jsonb_test_jsonb_items
set js = js || jsonb_build_object('name', '新しい名前')
where js->>'item_cd' = '0000000001'
;

こうすると、元のjsの「name」キーの値が「新しい名前」で上書きされる。

下記のように存在しないキーを指定すると、キーが追加される。

update jsonb_test_jsonb_items
set js = js || jsonb_build_object('name2', '商品名2-2')
where js->>'item_cd' = '0000000001'
;

指定したキーを削除する場合は、「- ＜キー名＞」で削除できる。

update jsonb_test_jsonb_items
set js = js - 'name2'
where js->>'item_cd' = '0000000001'
;

これで上で追加した「name2」キーが削除される。

おわり

まずはここまで押さえておけばJSONB型カラムを通常のカラムと

同じように使えるかな？

分ける必要のないデータを1つのテーブルにまとめたり、

色々ためしたい。

SQLアンチパターン