Dockerコンテナ †
文字コードを SQL_ASCII から UTF-8 に変更する †
- Postgresql 9.4 のデフォルトは SQL_ASCII
- http://www.postgresql.org/docs/9.4/static/multibyte.html
docker@1aedc1ccdf37:~$ psql -l
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LANGUAGE = (unset),
LC_ALL = (unset),
LC_PAPER = "en_US.UTF-8",
LC_ADDRESS = "en_US.UTF-8",
LC_MONETARY = "en_US.UTF-8",
LC_NUMERIC = "en_US.UTF-8",
LC_TELEPHONE = "en_US.UTF-8",
LC_IDENTIFICATION = "en_US.UTF-8",
LC_MEASUREMENT = "en_US.UTF-8",
LC_TIME = "en_US.UTF-8",
LC_NAME = "en_US.UTF-8",
LANG = "en_US.UTF-8"
are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+-----------+---------+-------+-----------------------
docker | docker | SQL_ASCII | C | C |
postgres | postgres | SQL_ASCII | C | C |
template0 | postgres | SQL_ASCII | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | SQL_ASCII | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
- UTF-8 で作り直す
# make encoding UTF-8 (default is damn SQL_ASCII)
RUN pg_dropcluster --stop 9.4 main
RUN pg_createcluster --start -e UTF-8 9.4 main
docker@891fbc133dec:~$ psql -l
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LANGUAGE = (unset),
LC_ALL = (unset),
LC_PAPER = "en_US.UTF-8",
LC_ADDRESS = "en_US.UTF-8",
LC_MONETARY = "en_US.UTF-8",
LC_NUMERIC = "en_US.UTF-8",
LC_TELEPHONE = "en_US.UTF-8",
LC_IDENTIFICATION = "en_US.UTF-8",
LC_MEASUREMENT = "en_US.UTF-8",
LC_TIME = "en_US.UTF-8",
LC_NAME = "en_US.UTF-8",
LANG = "en_US.UTF-8"
are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
docker | docker | UTF8 | C | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
JSONBを格納するテーブルを作る †
$ psql
psql (9.4beta2)
Type "help" for help.
docker=# CREATE DATABASE sample;
CREATE DATABASE
docker=# \c sample
You are now connected to database "sample" as user "docker".
sample=# CREATE TABLE articlej_tbl (
sample(# id bigserial primary key,
sample(# onCreate timestamp default NULL,
sample(# onUpdate timestamp default NULL,
sample(# contents jsonb NOT NULL
sample(# );
CREATE TABLE
sample=# CREATE INDEX articlej_tbl_idx_contents ON articlej_tbl USING gin (contents);
CREATE INDEX
sample=#
- id : 人工キー (PK)
- onUpdate : 楽観ロック用
- contents : JSON で記事を格納する
- JSONB列に、GIN (転置インデックス) を設定するのがミソ
- GIN は、本の索引のようなもの。
- データの値から、データ構造体をひける索引
- 全文検索などでよく使われる。単語からその単語が使われているページのリストをひける表を作っておいて、全文検索時には表を見る
- JSONB では、JSONの項目の値から JSON をひけるような表を作っている
- Postgresql の JSONB の製作者(オレグさん@モスクワ大)が講演で、JSONB は、B-Tree を使っている MongoDB よりも数倍速いっていってた
JSONBの格納 †
docker@42649b72cb9c:~$ psql -n -d sample
psql (9.4beta2)
Type "help" for help.
sample=# \d
List of relations
Schema | Name | Type | Owner
--------+---------------------+----------+--------
public | articlej_tbl | table | docker
public | articlej_tbl_id_seq | sequence | docker
(2 rows)
sample=# INSERT INTO articlej_tbl (onCreate,onUpdate,contents) VALUES (
now(),
now(),
'{"title" : "ポスト「京」",
"category" : ["Computer","Science"],
"format" : "HTML",
"author" : "浅川 直輝",
"publisher" : "日経コンピュータ",
"date" : "2014/10/02",
"url" : "http://itpro.nikkeibp.co.jp/atcl/news/14/100201173/",
"keywords" : ["理研","京","富士通","SPARC","64","X","TMSC"],
"summary" : "理化学研究所は2014年10月1日、理研のスパコン「京」の後継となるエクサ級（1エクサFLOPS前後）のスパコンについて、
基本設計を共同で行う事業者として富士通を選定したと発表した。",
"memo" : ""}'
);
INSERT 0 1
sample=# INSERT INTO articlej_tbl (onCreate,onUpdate,contents) VALUES (
now(),
now(),
'{"title" : "火星ミッション模擬実験、参加者に聞く",
"category" : ["Space","Planet"],
"format" : "PDF",
"author" : "Kelly McMillan",
"publisher" : "National Geographic",
"date" : "2014/10/16",
"url" : "http://www.nationalgeographic.co.jp/news/news_article.php?file_id=20141016003",
"keywords" : ["火星","ミッション","NASA"],
"summary" : "NASAの支援により、ハワイ大学マノア校が実施する宇宙探査模擬実験プロジェクト「HI-SEAS」（Hawaii Space Exploration Analog and Simulation）の一環で、
火星の宇宙ステーションでの生活を模した実験が始まる。",
"memo" : ""}'
);
INSERT 0 1
sample=#
検索 †
select * from articlej_tbl; †
- キーが同じ順番にソートされているのと、改行やスペースがなくなっていることがわかる
select id from articlej_tbl where contents @> '{"format" : "HTML"}'; (JSONの要素で絞込) †
sample=# select id from articlej_tbl where contents @> '{"format" : "HTML"}';
id
----
1
(1 row)
sample=# select id from articlej_tbl where contents @> '{"format" : "PDF"}';
id
----
2
(1 row)
- where '{"format" : "HTML"}' <@ contents; とも書ける
select '{"a":1, "b":2}'::jsonb ? 'b'; (Key を含むか?) †
sample=# select '{"a":1, "b":2}'::jsonb ? 'b';
?column?
----------
t
(1 row)
sample=# select '{"a":1, "b":2}'::jsonb ? 'c';
?column?
----------
f
(1 row)
select '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd']; (b, d いずれかの key を含む) †
sample=# select '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd'];
?column?
----------
t
(1 row)
select '{"a":1, "b":2, "c":3}'::jsonb ?& array['b', 'd']; (b, d どちらの key を含む) †
sample=# select '{"a":1, "b":2, "c":3}'::jsonb ?& array['b', 'd'];
?column?
----------
f
(1 row)
現時点では、JSONB の比較演算子 (Operator) は、@>、<@、?、?|、?& だけ †
- 将来的には、JSQuery が使えるようになるはず
- JSONB の演算子は少ないので、次章の要素取り出しを利用して副問い合わにして、複雑な検索を実現する ... というか、そんなことしたら実用的な速度がでないので、@> だけで済むような要件のデータストアに JSONB を使う
JSONからの要素の取り出し †
select contents->'keywords' from articlej_tbl; (JSON から JSON オブジェクトを取り出す) †
sample=# select contents->'keywords' from articlej_tbl;
?column?
------------------------------------------------------------
["理研", "京", "富士通", "SPARC", "64", "X", "TMSC"]
["火星", "ミッション", "NASA"]
(2 rows)
select contents->'keywords'->1 from articlej_tbl; (JSON 配列から n 番目の要素オブジェクトを取り出す) †
sample=# select contents->'keywords'->1 from articlej_tbl;
?column?
-------------------
"京"
"ミッション"
(2 rows)
select contents->>'keywords' from articlej_tbl; (JSON から TEXT を取り出す) †
sample=# select contents->>'keywords' from articlej_tbl;
?column?
------------------------------------------------------------
["理研", "京", "富士通", "SPARC", "64", "X", "TMSC"]
["火星", "ミッション", "NASA"]
(2 rows)
sample=# select pg_typeof(contents->>'keywords') from articlej_tbl;
pg_typeof
-----------
text
text
(2 rows)
sample=# select pg_typeof(contents->'keywords') from articlej_tbl;
pg_typeof
-----------
jsonb
jsonb
(2 rows)
- contents->>'keywords' は TEXT なので、contents->>'keywords'->2 はエラーになります
select contents->'keywords'->>1 from articlej_tbl; (JSON 配列から n 番目の TEXT を取り出す) †
sample=# select contents->'keywords'->>1 from articlej_tbl;
?column?
-----------------
京
ミッション
(2 rows)
select contents#>'{keywords,1}' from articlej_tbl; (JSON からパス指定で JSON オブジェクトを取り出す) †
sample=# select contents#>'{keywords,1}' from articlej_tbl;
?column?
-------------------
"京"
"ミッション"
(2 rows)
select contents#>>'{keywords,1}' from articlej_tbl; (JSON からパス指定で TEXT を取り出す) †
sample=# select contents#>>'{keywords,1}' from articlej_tbl;
?column?
-------------------
"京"
"ミッション"
(2 rows)
Computer
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
select.png