Contents

1 JSON 一危
2 json vs jsonb
3 譟壱
4 JSON れ 碁煙
5 谿瑚襭


1 JSON 一危 #

CREATE TABLE posts
(
  id serial NOT NULL,
  meta JSON
);

レ valid? well-formed? json 伎伎 ル.
INSERT INTO posts (id, meta)
VALUES (
  1, 
  '{ 
    "author": "outsider", 
    "authorId": 43434,
    "sns": { 
      "facebook": "https://facebook.com",
      "twitter": "https://twitter.com"
    },
    "createAt": "2014-06-14", 
    "category": ["pg", "tech", "dev"]
  }'
);

2 json vs jsonb #

  • json 襭 9.2 覯, ル 蠏 襭 蠏碁襦
  • jsonb 襭 9.4 覯, 覩碁(key-value 蠍一)襦 豌襴襯 伎 ロ覩襦 ル .
    • 螻給葦覓語襯 覓伎覃,
    • value 螳 麹 覲 覃,
    • 蠏 key 企 譴覲給 蟆曙磯 襷讌襷 蟆 覃,
    • key 覃,
    • @> 炎骸 螳 jsonb 一危 襷 覿螳 一一れ .


3 譟壱 #

SELECT meta->'author' FROM posts; 
-- "outsider"
 
SELECT meta->'sns'->'facebook' FROM posts;
-- "https://facebook.com"
 
SELECT meta->'sns'->>'facebook' FROM posts;
-- https://facebook.com
 
SELECT meta#>'{category, 2}' FROM posts;
-- "dev"

  • -> json 覦一伎企 螳豌企ゼ 覦
  • ->> 覓語 覦(meta->'sns'->>'facebook' 螳, meta->>'sns'->'facebook' 覿螳)
  • #> 覦一願 覦(meta#>'{category, 2}' category れ 3覯讌瑚)
  • 企 一一れ where column1 ->>'hostname' = 'myhost' 螻 螳 蠏碁襦 螳

4 JSON れ 碁煙 #

蟯瑚 2螳 れ願 蟆 譯殊 蟾蟆 覺. 覓碁企.
CREATE INDEX posts_idx1 ON posts((meta->>'authorId'));
CREATE INDEX posts_idx2 ON posts((meta->'sns'->>'facebook'));

5 谿瑚襭 #