PostgreSQLのJSONBを使う

目標

業務でIPアドレスの管理台帳を作成することになり、機器テーブルにIPアドレスのフィールドを設けて管理しようとしています。
しかし、ノートPCでは有線と無線の2つを登録しなければならず、当初は機器IDとIPアドレスおよびMACアドレスの組み合わせの別テーブルを作ろうとしましたが、PostgreSQLではJSON形式のデータが取り扱えるとのことで、JSONデータにしておけば今後の拡張も自在になる(かも知れない)ことを期待してJSON型利用に方針変更です。

テーブル設計

機器の基本情報に追加してdetailsをNIC情報と配置履歴情報をJSONBで定義しています。PostgreSQLではJSON型とJSONB型があり、JSON型のほうはtextで扱いやすいらしいですが、JSONBのほうが検索スピートが早いというだったのでJSONBを利用しました。テーブルのほうは、書き込まれるJSONデータの内容にはまったく感知せず、タイプをJSONBとしておけばJSONの文法に従った記述を行うことで、key-valueの組み合わせでデータを保存できます。

create table equip_books (
    id serial ,
    name varchar(20) not null unique,
    model_name varchar(30) ,
    maker varchar(20) ,
    equip_class varchar(20) not null ,
    details jsonb,
    operator_code varchar(10) ,
    created timestamp default current_timestamp,
    modified timestamp ,
primary key(id));
comment on table equip_books is '機器台帳';
comment on column equip_books.id is '管理番号';
comment on column equip_books.name is '名称';
comment on column equip_books.model_name is 'モデル名';
comment on column equip_books.maker is 'メーカ';
comment on column equip_books.equip_class is '機器分類';
comment on column equip_books.details is '明細';
comment on column equip_books.operator_code is '操作者コード';
comment on column equip_books.created is '初回登録日';
comment on column equip_books.modified is '最終更新日';

テストのためにデータを登録してみます。

insert into equip_books (name,maker,model_name,equip_class,details,operator_code,created)
 values ('PC001','NEC','Mate J','デスクトップPC','{
"nics":[
{"ip_address":"172.168.1.1","mac_address":"21:22:23:24:25:26","wifi":false,"dhcp":"static"}],
"allocations":[{"alloc_date":"2021-09-20","section":"総務課","room":"総務1"}
]}','system',now());
INSERT 0 1

某メーカーのデスクトップPCを総務課に導入したケースです。detailsの内容はJSON形式で記述します。ここでは、ネットワークが複数登録される可能性があるのでnicsというキーの中にネストしてIPアドレスMACアドレスWiFiの別、DHCPの区分を登録しています。IPアドレスの設定では手動か自動(DHCP)かの区分になりますが、DHCPでスタティックなIPアドレスを配布する場合もありますので、DHCPに'dynamic'とある以外は手動でも自動でもIPアドレスはip_addressの値が割り当てられるとルールで記述しています。dynamicと記述されていれば、ip_addressに設定されているIPアドレス含むセグメントの重複しないIPアドレス(場合によっては取得できないかも)が割り当てられる想定です。

ほとんどの場合、パソコンにはNIC1枚しかついてませんので、nicsへの登録も1件だけです。1件でも配列形式にしておかないと、参照時に苦労します。(というか、やらかしました)

allocationsには、配置日付、管理部署、配置位置を記述します。これも配置替えや部署の分割統合などで変化する可能性があるため、履歴として複数記述できるようにしておきます。

insert into equip_books (name,maker,model_name,equip_class,details,operator_code,created) 
values ('PC002','NEC','VersaPro J','ノートPC','{
"nics":[
{"ip_address":"192.168.1.103","mac_address":"11:12:13:14:15:17","wifi":false,"dhcp":"static"},{"ip_address":"192.168.10.1","mac_address":"21:22:23:24:25:27","wifi":true,"dhcp":"static"}],
"allocations":[{"alloc_date":"2021-09-20","section":"総務課","room":"総務1"}]}','system',now());
INSERT 0 1

2つ目はノートPCでNICは有線用とWiFi用の二種を登録するという想定です。

SQLを使って登録の内容を見てみます。

select * from equip_books;
 id | name  | model_name | maker |  equip_class   |                                                                                                                                                  details                                                                                                                                                  | operator_code |          created           | modified 
----+-------+------------+-------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+----------------------------+----------

 1 | PC001 | Mate J     | NEC   | デスクトップPC | {"nics": [{"dhcp": "static", "wifi": false, "ip_address": "192.168.1.102", "mac_address": "11:12:13:14:15:16"}], "allocations": [{"room": "総務1", "section": "総務課", "alloc_date": "2021-09-20"}]}                                                                                                     | system        | 2021-09-24 11:00:14.881681 | 
  2 | PC002 | VersaPro J | NEC   | ノートPC       | {"nics": [{"dhcp": "static", "wifi": false, "ip_address": "192.168.1.103", "mac_address": "11:12:13:14:15:17"}, {"dhcp": "static", "wifi": true, "ip_address": "192.168.10.1", "mac_address": "21:22:23:24:25:27"}], "allocations": [{"room": "総務1", "section": "総務課", "alloc_date": "2021-09-20"}]} | system        | 2021-09-24 11:05:22.834942 | 
(2 行)

IPアドレスのデータはdetailsの中のnicsというキーの中に入っています。

select details->'nics' from equip_books;

                 ?column?                                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 [{"dhcp": "static", "wifi": false, "ip_address": "192.168.1.102", "mac_address": "11:12:13:14:15:16"}]
 [{"dhcp": "static", "wifi": false, "ip_address": "192.168.1.103", "mac_address": "11:12:13:14:15:17"}, {"dhcp": "static", "wifi": true, "ip_address": "192.168.10.1", "mac_address": "21:22:23:24:25:27"}]
(2 行)

このnics配列データにアクセスするには、次のようにします。

select nic.ip_address, nic.mac_address
from equip_books,
   jsonb_to_recordset(details->'nics') AS nic(ip_address text, mac_address text);  
  ip_address   |    mac_address    
---------------+-------------------
 192.168.1.102 | 11:12:13:14:15:16
 192.168.1.103 | 11:12:13:14:15:17
 192.168.10.1  | 21:22:23:24:25:27
(3 行)

レコードとしては2件しか登録されていませんが、jsonの内容から3行を表示することになります。

ここまでくれば、端末をMACアドレスIPアドレスから楽に検索できるようになります。

select name, nic.ip_address, nic.mac_address
from equip_books,                                         
   jsonb_to_recordset(details->'nics') AS nic(ip_address text, mac_address text) where nic.ip_address = '192.168.1.102';
 name  |  ip_address   |    mac_address    
-------+---------------+-------------------
 PC001 | 192.168.1.102 | 11:12:13:14:15:16
(1 行)

select name, nic.ip_address, nic.mac_address
from equip_books,
   jsonb_to_recordset(details->'nics') AS nic(ip_address text, mac_address text) where nic.ip_address = '192.168.10.1';
 name  |  ip_address  |    mac_address    
-------+--------------+-------------------
 PC002 | 192.168.10.1 | 21:22:23:24:25:27
(1 行)

select name, nic.ip_address, nic.mac_address
from equip_books,
   jsonb_to_recordset(details->'nics') AS nic(ip_address text, mac_address text) where nic.ip_address like '192.168.1.%';
 name  |  ip_address   |    mac_address    
-------+---------------+-------------------
 PC001 | 192.168.1.102 | 11:12:13:14:15:16
 PC002 | 192.168.1.103 | 11:12:13:14:15:17
(2 行)

これなら複数テーブルをJOINしながら使うよりは簡単に利用できそうです。