端端舝

JSONB倰匹Postgres
毛NoSQL勻弔仁妏丹
@tacke_jp
Mixi, Inc.
Nohana, Inc.
2015.10.16 菴10隙�忒Web巨件斥瓦失蝠霜頗 LT

$ whoami
? @tacke_jp
? 絁宒頗扦立弁扑奴 / 絁宒頗扦用甩瓜
? 扔奈田奈扔奶玉午井生奶氾奴皮失皿伉午井犯奈正
賤昴午井伎〝支勻化月け蚚�椰巨件斥瓦失
? 郔輪反PostgreSQL匹劐颷弁巨伉毛𤩸仁仇午卞?
甩穴勻化中引允

(AD)We re Hiring!!
? 絁宒頗扦用甩瓜匹反☆模逜及�觳毛賤𢜪
允月★午中丹立永扑亦件卞僕覜仄化珨髯
卞�P中化仁木月笯嶲毛躁摩仄化中引允
? 政婓反﹜゜掛笢及云赽今氏毛厥勾仍模穸
匹﹜旦穴石卞湧曰凶戶凶赽鼎及迡淩毛倛
卞紹允仇午及云忒�中毛仄化中引允
? 仍鶸庤互丐木壬白伉奈玄奈弁媆卞云汒互
仃仁分今中!!
模逜毛虷�卞仄引仁勻化仁木月旦穴石失皿伉巨件斥瓦失今氏卞頗中凶中ㄐ
https://www.wantedly.com/projects/25788

Postgres Meets Schemaless
旦平奈穴伊旦及丟伉永玄
? 穴奶弘伊奈扑亦件卅仄卞
白奴奈伙玉毛袚樓匹五月
? ⻌木赽左皮斥尼弁玄及方丹
卅躂�婖及犯奈正互㜳尹月
RDB及丟伉永玄
? 豫木化月
? 玄仿件扒弁扑亦件互妏尹月
? JOIN互妏尹月
? 頄蜓卅憝杅�
Postgres↗反JSON毛�午仄化市仿丞卞中木月方丹卞允月仇午匹﹜
�I氪及中中午仇龰曰毛仄凶
(↗ JSON倰反Postgres9.3眕蔥﹜JSONB倰反9.4眕蔥匹扔禾奈玄)

斑釦倏捧白央奶伙及勳鳥梯棗娶喧

Line-delimited JSON ?le
// posts.json
{※id§:1,"tags":["book","haskell"],"title":"允仍中H掛掂氏
分","created_at":"2015-08-16T14:10:02Z"}?
{"id":2,"tags":["docker"],"title":"Docker互升勻井奈卞中勻
凶璃","options":{"private":true},"created_at":"Wed Oct
14 13:50:09 +0000 2015"}?
{"id":3,"tags":["lisp","scheme"],"title":"Scheme井歹中中
方Scheme","created_at":"2015-10-16T14:10:02+09"}?

COPY恅匹import
create table post_json (
obj jsonb
);
-- JSONB倰及市仿丞及心毛厥勾氾奈皮伙毛蚚砩
copy post_json
from '/path/to/json/file/posts.json'
with csv quote e'x01' delimiter e'x02'
;
(http://adpgtech.blogspot.jp/2014/09/importing-json-data.html)

Bulk Insert
create table comment_json (
obj jsonb
);
insert into comment_json
values('{"id":1,"post_id":1,"comment":"歹井月"}')
,('{"id":2,"post_id":2,"comment":"它弗月"}')
,('{"id":3,"post_id":3,"comment":"朸"}')
;

斑釦倏捧詁倰市仿丞尺及弁巨伉

左皮斥尼弁玄及笢旯毛統桽
select (obj->>'id')::integer as id
, obj->>'title' as title
, obj->>'created_at' as created_at
, obj->'tags'->>0 as first_tag
from post_json
;
-- 栳呾赽 -> , ->> 匹左皮斥尼弁玄支饜蹈及笢旯
卞失弁本旦

JOIN
select post_json.obj->>'title'
, comment_json.obj->>'comment'
from post_json
, comment_json
where true
and post_json.obj->>＊id'
= comment_json.obj->>'post_id'
;

JOIN
?column? | ?column?
----------------------------+----------
允仍中H掛掂氏分 | 歹井月
Docker互升勻井奈卞中勻凶璃 | 它弗月
Scheme井歹中中方Scheme | 朸
(3 rows)

B-Tree Index毛公及引引�月及反NG
create index post_json_obj
on post_json using btree (obj);
create index comment_json_obj
on comment_json using btree (obj);
-- obj�极毛 = 匹掀廌允月�磁仄井index互妏歹木卅中

宒Index
create index post_json_id
on post_json using btree ((obj->>'id'));
create index comment_json_post_id
on comment_json using btree ((obj->>'post_id'));

Key靡互𢜪引日卅中卅日GIN↗毛
妏云丹
create index post_json_gin
on post_json using gin (obj jsonb_path_ops);
create index post_json_tags_gin
on post_json using gin (obj->'tags');
-- 饜蹈卞�允月index手惿木月
(↗ GIN = Generalized Inverted Index)

JSON饜蹈卞�允月摩廣憝杅
select obj->>'id'
, ( select sum(length(value))
from jsonb_array_elements_text(obj->＊tags')
)
from post_json
;
-- Postgres匹反SELECT宒匹扔皮弁巨伉互妏尹月

JSON饜蹈卞�允月摩廣憝杅
?column? | sum
----------+-----
1 | 11
2 | 6
3 | 10
(3 rows)

Schemaful卅氾奈皮伙尺及INSERT
create table post (
id integer
, tags jsonb -- flat卞堤懂卅中手及反jsonb卞芼勻煋氏元扎丹
, title text
, created_at timestamptz
);
insert into post
select (jsonb_populate_record(null::post, obj)).*
from post_json
;
select *
from post;

Postgres及JSONB倰引午戶
? JSON反ㄠ勾及�午仄化㜳歹木月 (1市仿丞 = 1勾及JSON)
? B-tree Index支GIN互惿木月
? jsonb_array_elements(_text) 支 jsonb_populate_record(set) 反?
伉伊奈扑亦瓜伙卅岍賜午JSON卅岍賜午及点傾仄砢
? (切卅心卞)9.5匹反左皮斥尼弁玄支饜蹈卞猁匼毛袚樓仄凶曰祅壺
仄凶曰匹五月方丹卞卅月
(統蕉: http://www.slideshare.net/hadoopxnttdata/postgresql-95-new-features-nttdata )

仇氏卅JSON互丐曰引仄化
{"car":"T","cdr": {"car":"h","cdr": {"car":"a","cdr":
{"car":"n","cdr": {"car":"k","cdr": {"car":" ","cdr":
{"car":"y","cdr": {"car":"o","cdr": {"car":"u","cdr":
{"car":" ","cdr": {"car":"f","cdr": {"car":"o","cdr":
{"car":"r","cdr": {"car":" ","cdr": {"car":"l","cdr":
{"car":"i","cdr": {"car":"s","cdr": {"car":"t","cdr":
{"car":"e","cdr": {"car":"n","cdr": {"car":"i","cdr":
{"car":"n","cdr": {"car":"g","cdr": {"car":"!","cdr":
{"car":"!","cdr":{} }
}}} }}} }}} }}} }}} }}} }}} }}}

仇氏卅弁巨伉毛灍俴允月午
with recursive eval as (
select obj->>'car' as car
, obj->'cdr' as cdr
, 1 as depth
from lisp
union all
select cdr->>'car' as car
, cdr->'cdr' as cdr
, depth + 1 as depth
from eval
where (cdr->'cdr') is not null
)
select string_agg(car, '' order by depth) as msg
from eval
;
(https://gist.github.com/yuki-takeichi/c4812f7af46f7f84e958)

丐曰互午丹仍介中引仄凶ㄐ
msg
---------------------------
Thank you for listening!!
(1 row)

端端舝

斑釦倏捧詁倰化?梯棗莽喧眶娶梗莽毛捧棗釦紮郭勻幻?仁妏丹

Recommended

More Related Content

What's hot (20)

Similar to 斑釦倏捧詁倰化?梯棗莽喧眶娶梗莽毛捧棗釦紮郭勻幻?仁妏丹 (20)

Recently uploaded (11)

斑釦倏捧詁倰化?梯棗莽喧眶娶梗莽毛捧棗釦紮郭勻幻?仁妏丹