PostgreSQL 9.4 の JSON サポートについて ref: http://qiita.com/kumazo@github/items/9010f9dad134b9d9d16d

file0.sql

SELECT value, jsonb_typeof(value)
FROM jsonb_array_elements(
  '[1, 1.00, 10.0e-3, "1", true, "2014-01-01T12:34:56", null, "", {}, []]'
);
         value         | jsonb_typeof
-----------------------+--------------
 1                     | number
 1.00                  | number
 0.0100                | number
 "1"                   | string
 true                  | boolean
 "2014-01-01T12:34:56" | string
 null                  | null
 ""                    | string
 {}                    | object
 []                    | array
(10 rows)

file1.sql

SELECT ('{"a":1}'::jsonb->>'a') + 1;
ERROR:  operator does not exist: text + integer

SELECT ('{"a":1}'::jsonb->'a') + 1;
ERROR:  operator does not exist: jsonb + integer

SELECT ('{"a":1}'::jsonb->'a') + ('{"a":1}'::jsonb->'a');
ERROR:  operator does not exist: jsonb + jsonb

file10.sql

SELECT '{"a":1, "b":2, "c":3}'::jsonb @> '{"c":3, "a":1}';          -- TRUE
SELECT '{"a":1, "b":2, "c":3}'::jsonb @> '{"c":3, "a":100}';        -- FALSE
SELECT '{"a":1, "b":2, "c":3}'::jsonb <@ '{"c":3, "a":1}';          -- FALSE 
SELECT '{"a":{"x":1, "y":2}}'::jsonb @> '{"x":1}';                  -- FALSE 階層をたどらない
SELECT '{"a":{"x":1, "y":2}}'::jsonb @> '{"a":{"x":1}}';            -- TRUE 子階層も部分関係が評価される
SELECT '{"a":{"x":1, "y":2}}'::jsonb @> '{"a":{"x":1, "y":2}}';     -- TRUE

SELECT '[1, 2, 3]'::jsonb @> '[2, 1]';                              -- TRUE 配列要素の順序を見ない
SELECT '[1, 2, 3]'::jsonb @> '[2, 4]';                              -- FALSE
SELECT '[1, 2, 3]'::jsonb <@ '[2, 1]';                              -- FALSE
SELECT '[1, 2, 3]'::jsonb @> '[1, 1]';                              -- TRUE
SELECT '{"a":[1,2,3]}'::jsonb @> '{"a":[3]}';                       -- TRUE
SELECT '{"a":[1,2,3]}'::jsonb @> '{"a":[3,2,1]}';                   -- TRUE 配列要素の順序を見ない

file11.sql

-- object
SELECT '{"a":1, "b":2, "c":3}'::jsonb ? 'a';               -- TRUE
SELECT '{"a":1, "b":2, "c":3}'::jsonb ? 'x';               -- FALSE
SELECT '{"a":1, "b":2, "c":null}'::jsonb ? 'c';            -- TRUE
SELECT '{"a":1, "b":2, "c":{"x":3}}'::jsonb ? 'x';         -- FALSE 階層をたどらない

-- array
SELECT '["a", 1, true]'::jsonb ? 'a';                      -- TRUE
SELECT '["a", 1, true]'::jsonb ? 1;                        -- ERROR
SELECT '["a", 1, true]'::jsonb ? '1';                      -- FALSE number は判定できない
SELECT '["a", 1, true]'::jsonb ? 1::text;                  -- FALSE number は判定できない
SELECT '["a", 1, true]'::jsonb ? true;                     -- ERROR 
SELECT '["a", 1, true]'::jsonb ? 'true';                   -- FALSE boolean は判定できない
SELECT '[{"a":1},[1]]'::jsonb ? '{"a":1}';                 -- FALSE object は判定できない
SELECT '[{"a":1},[1]]'::jsonb ? '[1]';                     -- FALSE array は判定できない

-- いずれか存在する
SELECT '{"a":1, "b":2, "c":3}'::jsonb ?| ARRAY['a', 'x'];  -- TRUE
SELECT '{"a":1, "b":2, "c":3}'::jsonb ?| '{a, x}';         -- TRUE text配列のリテラル
SELECT '["a", "b", "c"]'::jsonb ?| '{a, x}';               -- TRUE
SELECT '[1, 2, 3]'::jsonb ?| '{1, 100}';                   -- FALSE number は判定できない
SELECT '[1, 2, 3]'::jsonb ?| ARRAY[1, 100];                -- ERROR

-- すべて存在する
SELECT '{"a":1, "b":2, "c":3}'::jsonb ?& ARRAY['a', 'c'];  -- TRUE
SELECT '{"a":1, "b":2, "c":3}'::jsonb ?& ARRAY['a', 'x'];  -- FALSE
SELECT '{"a":1, "b":2, "c":3}'::jsonb ?& '{a, c}';         -- TRUE
SELECT '{"a":1, "b":2, "c":3}'::jsonb ?& '{a, a, a}';      -- TRUE
SELECT '["a", "b", "c"]'::jsonb ?& '{a, c}';               -- TRUE
SELECT '["a", "b", "c"]'::jsonb ?& '{a, x}';               -- FALSE
SELECT '["a", "b", "c"]'::jsonb ?& '{a, a, a}';            -- TRUE
SELECT '[1, 2, 3]'::jsonb ?& '{1, 2}';                     -- FALSE number は判定できない


SELECT * FROM hoge
WHERE prof @> '{"gender":"Male", "class":"Programmer"}'
  AND prof->'items' ? 'Mac'
  AND prof->'skills' ?& '{PostgreSQL, Linux, Ruby, Driving, Cooking, Housekeeping}'
  AND prof->'languages' ?| '{English, 日本語, tlhIngan Hol}'

file12.sql

SELECT json_build_array(1, 2, 'Fizz', TRUE, array[1], '{"a":   1}'::jsonb, NULL);
             json_build_array
-------------------------------------------
 [1, 2, "Fizz", true, [1], {"a": 1}, null]
(1 row)

SELECT json_build_object('i', 1, 's', 'a"', 'a', array['x'], 'b', TRUE, 'c', '{"a":    "b"}'::json, 'n', NULL);
                                json_build_object

--------------------------------------------------------------------------------
--
 {"i" : 1, "s" : "a\"", "a" : ["x"], "b" : true, "c" : {"a":    "b"}, "n" : null
}
(1 row)

file13.sql

SELECT json_object(array['a', '1', 'b', 'X,Y', 'c', 'true', 'd', '{"e
":1}', 'n', NULL]);
                              json_object
-----------------------------------------------------------------------
 {"a" : "1", "b" : "X,Y", "c" : "true", "d" : "{\"e\":1}", "n" : null}
(1 row)

SELECT json_object('{a, 1, b, "X,Y", c, true, d, null}');
                    json_object
----------------------------------------------------
 {"a" : "1", "b" : "X,Y", "c" : "true", "d" : null}
(1 row)

SELECT json_object('{{a, 1}, {b, X}, {c, true}, {d, null}}');
                   json_object
--------------------------------------------------
 {"a" : "1", "b" : "X", "c" : "true", "d" : null}
(1 row)

SELECT json_object('{a, b, c, d}', '{1, X, true, null}');
                   json_object
--------------------------------------------------
 {"a" : "1", "b" : "a", "c" : "true", "d" : null}
(1 row)

file14.sql

SELECT json_object_agg(key, value::json) 
FROM (VALUES
    ('a', '1'), ('b', '"x"'), ('c', 'true')
) AS r(key, value);
          json_object_agg
------------------------------------
 { "a" : 1, "b" : "x", "c" : true }
(1 row)

file15.sql

-- jsonb から指定したフィールドを削除する。
SELECT json_object_agg( key, value )::jsonb
FROM jsonb_each( '{"a":1, "b":"x", "c":true}' )
WHERE key <> 'b';
   json_object_agg
---------------------
 {"a": 1, "c": true}
(1 row)

file16.sql

SELECT * FROM jsonb_to_record (
     '{"id":"  123", "name":"太郎", "passwd":"123456", "lastdt":"20140101", "pts":2, "tags":"{JSON, 9.4}", "premium":false}'
) AS acc (id int, name bytea, pts numeric(3,2), lastdt timestamptz, tags text[],
 premium boolean)
;
 id  |      name      | pts  |         lastdt         |    tags    | premium
-----+----------------+------+------------------------+------------+---------
 123 | \xe5a4aae9838e | 2.00 | 2014-01-01 00:00:00+09 | {JSON,9.4} | f
(1 row)

file17.sql

CREATE TABLE hoge (
  id    SERIAL PRIMARY KEY,
  name  varchar(256) NOT NULL,
  email varchar(256),
  code  int
);

INSERT INTO hoge(name, email, code)
  SELECT * FROM json_to_record(
    '{"id":100, "name":"太郎", "code":"1234"}'
  ) AS (name varchar(20), email varchar(40), code int) -- キビシめ
);

file18.sql

-- カラム値の上書き
SELECT a.* 
FROM accounts, 
     jsonb_populate_record(accounts, '{"passwd":"*****"}'::jsonb) AS a
;
  id  | name | ... | passwd  | ...
------+------+-----+---------+---
 1111 | hoge | ... | *****   | ...
 1112 | piyo | ... | *****   | ...

file19.sql

-- アプリケーション定義のデフォルト値
DROP table hoge;
CREATE TABLE hoge (
  id      int PRIMARY KEY,
  name    text NOT NULL, 
  email   text        DEFAULT 'nobody@example.jp', 
  lastdt  timestamptz DEFAULT CURRENT_TIMESTAMP, 
  interim boolean     DEFAULT TRUE
);

INSERT INTO hoge
  SELECT * FROM jsonb_populate_recordset(
    ROW (NULL, '名無しさん', NULL, date_trunc('day', CURRENT_TIMESTAMP), FALSE)::hoge,   -- set default values
    '[ 
       {"id":1,"name":"一郎", "email":"taro@example.jp", "lastdt":"2001/01/01"},
       {"id":2,               "email":"jiro@example.jp", "interim":true},
       {"id":3,"name":"三郎", "tel":"01234567689",       "interim":null} 
     ]'::jsonb
  )
;

SELECT * FROM hoge
;
id |    name    |      email      |        lastdt          | interim
----+------------+-----------------+------------------------+---------
  1 | 一郎       | taro@example.jp | 2001-01-01 00:00:00+09 | f
  2 | 名無しさん   | jiro@example.jp | 2014-08-20 00:00:00+09 | t
  3 | 三郎       |                 | 2014-08-20 00:00:00+09 |
(3 rows)

file2.sql

-- たとえば連結
SELECT '{"a":1}'::jsonb || '{"b":2}'::jsonb;
ERROR:  operator does not exist: jsonb || jsonb

-- フィールドの更新
UPDATE hoge SET
    jsonb_data->>'name' = '太郎'
WHERE id = 1;
ERROR:  syntax error at or near "->>"

UPDATE hoge SET
    jsonb_data->'name' = '"太郎"'::jsonb
WHERE id = 1;
ERROR:  syntax error at or near "->"

file20.sql

CREATE INDEX idx01 ON my_table(jb_column);  -- デフォルトは btree
CREATE INDEX idx02 ON my_table USING hash(jb_column);

file21.sql

-- jsonb をキーとして使ってみる
CREATE INDEX oauth_idx ON oauth(uid);
SELECT token FROM oauth WHERE uid = '{"provider":"GitHub", "login":"taro123"}';

file22.sql

-- あくまで jsonb を使う
CREATE INDEX idx01 ON coupons(summary->'points');
SELECT * FROM coupons WHERE summary->'points' > to_json( 10 )::jsonb;  -- めんどくさい

-- 値をキャストしてから使う
CREATE INDEX idx02 ON coupons((summary->>'points')::int);  -- キャストに失敗する可能性あり
SELECT * FROM coupons WHERE (summary->>'points')::int > 10 ;    -- めんどくさい

file23.sql

CREATE INDEX idx01 ON my_table USING gin(jb_column, jsonb_opt);  -- デフォルト
CREATE INDEX idx02 ON my_table USING gin(jb_column, jsonb_path_opt);

file24.sql

CREATE INDEX idx01 ON articles USING gin(to_tsvector('english', jb_body->>'abstract'));
CREATE INDEX idx02 ON articles USING gin(to_tsvector('english', jb_body::text));
CREATE INDEX idx03 ON articles USING gin(jb_body::text, gin_bigm_ops);         -- pg_bigm module

file25.sql

-- to_tsvector() では JSON の書式記号は無視される
ELECT to_tsvector('english', '{"a": "json to text", "the": [1, "a", true], "aaa": {"bbb": "bbb ccc"}}');
                          to_tsvector
---------------------------------------------------------------
 '1':6 'aaa':9 'bbb':10,11 'ccc':12 'json':2 'text':4 'true':8
(1 row)

-- bi-gram
SELECT show_bigm('{"a": "寿限無寿限無五劫のry", "b": ["すももももももももち"]}');
                               show_bigm
--------------------------------------------------------------------------------
 {すも,"ち ",のr,もち,もも,五劫,劫の,寿限,無五,無寿,限無," \""," ["," {","\"す",
"\"寿","\",","\":","\"]","\"a","\"b",", ",": ","[\"","]}","a\"","b\"",ry,"y\"","
{\"","} "}
(1 row)

file26.sql

-- 正規表現でフィールド名だけ削除する
CREATE FUNCTION jsonb_crude_text(jb jsonb) RETURNS text
AS $$
  SELECT regexp_replace(jb::text, '"[^"]+?[^\\]": ', '', 'g')
$$ LANGUAGE SQL;

file27.sql

-- string の再帰抽出
CREATE FUNCTION jsonb_strvals(jb jsonb) RETURNS text
AS $$
  SELECT string_agg(
    CASE jsonb_typeof(value)
      WHEN 'string' THEN value::text
      WHEN 'object' THEN jsonb_strvals(value)
      WHEN 'array'  THEN jsonb_strvals(value)
    END, '\n'
  )
  FROM (
    SELECT value FROM jsonb_each(jb)           WHERE jsonb_typeof(jb) = 'object'
    UNION ALL
    SELECT value FROM jsonb_array_elements(jb) WHERE jsonb_typeof(jb) = 'array'
  ) AS strvals
$$ LANGUAGE SQL;

file3.sql

-- text to json
SELECT '{"name":"hoge","size":[1,2,3]}'::json;
              json
--------------------------------
 {"name":"hoge","size":[1,2,3]}
(1 row)

-- json to jsonb
SELECT '{"name":"hoge","size":[1,2,3]}'::json::jsonb;
                jsonb
-------------------------------------
 {"name": "hoge", "size": [1, 2, 3]}
(1 row)

-- text to jsonb
SELECT '{"name":"hoge","size":[1,2,3]}'::jsonb;
                jsonb
-------------------------------------
 {"name": "hoge", "size": [1, 2, 3]}
(1 row)

-- jsonb to text
SELECT '{"name":"hoge","size":[1,2,3]}'::jsonb::text;
                text
-------------------------------------
 {"name": "hoge", "size": [1, 2, 3]}
(1 row)

-- jsonb to binary 変換できない
SELECT '{"name":"hoge","size":[1,2,3]}'::jsonb::bytea;
ERROR:  cannot cast type jsonb to bytea

file4.sql

-- last win
SELECT '{"y":1, "x":2, "y":3}'::jsonb;
      jsonb
------------------
 {"x": 2, "y": 3}
(1 row)

-- 辞書順ではない
SELECT '{"aa":1, "x":2, "y":3}'::jsonb;
           jsonb
---------------------------
 {"x": 2, "y": 3, "aa": 1}
(1 row)

-- インデント意味なし
SELECT 
'{
  "conf":{
      "name":"hoge",
      "data":[3,2,1]
  }
}'::jsonb;
                    jsonb
-----------------------------------------------
 {"conf": {"data": [3, 2, 1], "name": "hoge"}}
(1 row)

-- Unicodeエスケープされた文字や数値の指数表現は保持されない。
SELECT '["\u3042", 0.1e-6]'::jsonb, '["\u3042", 0.1e-6]'::json;
       jsonb       |        json
-------------------+--------------------
 ["あ", 0.0000001] | ["\u3042", 0.1e-6]
(1 row)

file5.sql

SELECT '{"aaa":100}'->>'aaa';
ERROR:  operator is not unique: unknown ->> unknown

SELECT '{"aaa":100}'::json->>'aaa';
SELECT '{"aaa":100}'::jsonb->>'aaa';

file6.sql

-- jsonb が正規化されることを利用して連結操作
SELECT replace(
  concat_ws('!!', 
    '{"a":1, "b":2}'::text, 
    '{"a":2, "c":3}'::text
  ),
  '}!!{', ', '
)::jsonb
;
         replace
--------------------------
 {"a": 2, "b": 2, "c": 3}
(1 row)

file7.sql

SELECT '{"a":1, "b":2}'::jsonb = '{"b":2, "a":1}';  -- TRUE
SELECT '[1,2,3]'::jsonb = '[3,2,1]';               -- FALSE

SELECT '{"x":1}'::jsonb->'x' = 1;           -- ERROR 残念
SELECT '{"x":1}'::jsonb->'x' = '1'::jsonb;  -- TRUE
SELECT '{"x":1}'::jsonb->'x' = '1';         -- TRUE リテラルの暗黙変換
SELECT '{"x":1}'::jsonb->'x' = '1.00';      -- TRUE
SELECT '{"x":1}'::jsonb->>'x'::int = 1;     -- TRUE
SELECT '{"x":1}'::jsonb->'x' = to_json(1)::jsonb;  -- TRUE

SELECT '{"x":"a"}'::jsonb->'x' = 'a';       -- ERROR 残念
SELECT '{"x":"a"}'::jsonb->'x' = '"a"'::jsonb;     -- TRUE
SELECT '{"x":"a"}'::jsonb->'x' = '"a"';     -- TRUE リテラルの暗黙変換
SELECT '{"x":"a"}'::jsonb->>'x' = 'a';      -- TRUE
SELECT '{"x":"a"}'::jsonb->'x' = to_json('a'::text)::jsonb;  -- TRUE

SELECT ('{"x":null}'::jsonb->'x') IS NULL;  -- FALSE
SELECT ('{"x":null}'::jsonb->>'x') IS NULL; -- TRUE
SELECT ('{"a":1}'::jsonb->'x') IS NULL;     -- TRUE

SELECT jb_data->'category', count(*) FROM entries
WHERE jb_data->'tags' = '["PostgreSQL", "JSON"]'
  AND (jb_data->>'stars')::int = 5 
GROUP BY jb_data->'category'

file8.sql

SELECT '{"x":1}'::jsonb < '{"x":2}';     -- TRUE フィールド値
SELECT '{"aa":100}'::jsonb < '{"x":1}';   -- TRUE フィールド名 辞書順
SELECT '{"xx":100}'::jsonb < '{"a":1, "b":1}';    -- TRUE フィールド数
SELECT '[1, 1]'::jsonb < '[1, 1, 1]';    -- TRUE 要素数

SELECT '{"x":1}'::jsonb->'x' < 2;            -- ERROR 残念
SELECT '{"x":1}'::jsonb->'x' < '2'::jsonb;   -- TRUE 
SELECT '{"x":"a"}'::jsonb->'x' < '"2"'::jsonb;   -- TRUE 

SELECT '"a"'::jsonb < '1'::jsonb;        -- TRUE string より number の方が"大きい"
SELECT '"aa"'::jsonb < '"b"'::jsonb;     -- TRUE 辞書順で評価されている

SELECT max(json_data->'like') FROM entries;  -- ERROR 残念
ERROR:  function max(jsonb) does not exist

SELECT * FROM entries
WHERE jb_data->'follower' > jb_data->'favorite'  -- OK
  AND jb_data->'points' > to_json(10)::jsonb 
ORDER BY jb_data->'like' DESC                 -- ソートにも使える

file9.sql

SELECT value FROM jsonb_array_elements(
  '[2, 1, 1.0, "a", ["a"], true, "b", "aa", null, "", 0, {},
   {"b":2}, {"b":1, "a":1}, {"b":2, "a":1}, {"aa":1, "b":1}, 
   [2],[1,1,1],[1,2],[1,1],[],{"aa":1}]'
) order by value;
      value
------------------
 []                  -- なぜか空配列が最も"小さい"
 null                -- [] < null
 ""                  -- null < string
 "a"                 --
 "aa"                -- 辞書順
 "b"                 --
 0                   -- string < number
 1.0                 -- 1 = 1.0 だが、Unstable Sort のようで、結果順序は不定になる
 1                   --
 2                   --
 true                -- number < boolean
 ["a"]               -- boolean < array
 [2]                 -- array<string> < array<number>
 [1, 1]              -- 要素数順
 [1, 2]              -- 同要素数なら要素値順
 [1, 1, 1]           --
 {}                  -- array < object
 {"aa": 1}           -- キーの辞書順 -- object 内のフィールドのソート順とは一致しない
 {"b": 1}            --
 {"a": 1, "b": 1}    -- フィールド数順
 {"a": 1, "b": 2}    -- フィールド数が同じなら値順
 {"b": 1, "aa": 1}   -- 正規化後のフィールド順で評価される
(22 rows)