PL/pgSQL JSON

PostgreSQLのPL/pgSQLのJSONについて。

概要

PostgreSQLにはJSON（JavaScript Object Notation）を扱うデータ型としてjsonとjsonbがある。

jsonは入力データをそのまま保持し、使用するときに解析するらしい。
jsonbは入力時に解析するので、使用するのは高速らしい。
したがって、通常はjsonbを使えば良いらしい。

JSONの初期化方法

jsonbの変数へは、文字列でJSONを書いて渡せばいい。

declare
  j jsonb;
begin
  j := '{"id":1, "name":"zzz"}';
  raise info 'json = %', j;
  raise info 'json = %', jsonb_pretty(j);
end

↓実行結果

INFO:  json = {"id": 1, "name": "zzz"}
INFO:  json = {
    "id": 1,
    "name": "zzz"
}

jsonb_prettyを使うと、整形された文字列が返ってくる。
（json_prettyは無いっぽい。json型の場合は解析せずにそのまま保持しているからかな？）

''で囲んだJSONの後ろに「::jsonb」を付けることでも指定できる。
明示的に変換するにはto_jsonbを使う。

raise info 'json = %', '{"id":1, "name":"zzz"}'::jsonb;

raise info 'json = %', '123'::jsonb;
raise info 'json = %', to_jsonb(123);

raise info 'json = %', '"aaa"'::jsonb;
raise info 'json = %', to_jsonb('aaa'::text);

↓実行結果

INFO:  json = {"id": 1, "name": "zzz"}
INFO:  json = 123
INFO:  json = 123
INFO:  json = "aaa"
INFO:  json = "aaa"

テーブルのレコードをJSONに変換するのもto_jsonbで出来る。

declare
  h hoge%rowtype;
  i int;
  j jsonb;
  a jsonb[];
begin
  i := 0;
  for h in select * from hoge loop
    raise info 'h = %', h;
    i := i + 1;
    a[i] := to_jsonb(h);
  end loop;

  raise info 'a = %', a;
  
  foreach j in array a loop
    raise info 'j = %', j;
  end loop;
end

→jsonb_populate_record（JSONからテーブルへの変換）

jsonb_build_objectでキーと値を並べて指定することが出来る。（JavaのMap.of相当）

declare
  j jsonb;
begin
  j := jsonb_build_object("key1", "value1", "key2", 123);
  raise info 'json = %', j;
end

↓実行結果

INFO:  json = {"key1": "value1", "key2": 123}

SELECT結果をJSONにする方法

SELECTした結果をJSONにするには、jsonb_aggを使う。
1行が各カラム名とデータであり、各行を要素とする配列が返る。
（この配列はJSONとしての配列であり、PL/pgSQLの配列ではない。PL/pgSQLの配列への変換→jsonb_array_elements）

declare
  j jsonb;
begin
  select jsonb_agg(h.*) into j from hoge h;
  raise info 'json = %', j;
end

↓実行結果

INFO: json = [{"h_id": 1, "h_name": "hoge1"}, {"h_id": 2, "h_name": "hoge2"}, {"h_id": 3, "h_name": "hoge3"}]

--サンプルデータ
hishidamadb=> select * from hoge;
 h_id | h_name 
------+--------
    1 | hoge1
    2 | hoge2
    3 | hoge3
(3 行)

JSON配列の巡回

jsonb_array_elements

トップレベルが配列であるJSONは、以下のようにfor文を使って各要素を取得できる。

declare
  j jsonb;
  r jsonb;
begin
  select jsonb_agg(h.*) into j from hoge h;

  for r in select * from jsonb_array_elements(j) loop
    raise info 'r = %', r;
  end loop;
end

↓実行結果

INFO: r = {"h_id": 1, "h_name": "hoge1"}
INFO: r = {"h_id": 2, "h_name": "hoge2"}
INFO: r = {"h_id": 3, "h_name": "hoge3"}

jsonb_array_lengthでJSON配列の長さを取得できるので、それを使ってループさせることも出来る。

declare
  j jsonb;
  i int;
begin
  select jsonb_agg(h.*) into j from hoge h;

  for i in 0..jsonb_array_length(j) - 1 loop
    raise info 'r = %', j->i;
  end loop;
end

「jsonbの（PL/pgSQLとしての）配列」に変換する例。

declare
  j jsonb;
  a jsonb[];
begin
  select jsonb_agg(h.*) into j from hoge h;
  a = array(select * from jsonb_array_elements(j));
end

逆に、PL/pgSQLの配列をJSON配列にするにはarray_to_jsonという関数がある。

declare
  a int[];
  j jsonb;
begin
  a := array[1, 2, 3];
  j := array_to_json(a);
  raise info 'j = %', j;
end

jsonb_populate_recordset

JSON配列の各要素がテーブルのレコードである場合は、以下のようにfor文を使ってテーブルの1行分を取得できる。

declare
  j jsonb;
  r hoge%rowtype;
begin
  select jsonb_agg(h.*) into j from hoge h;

  for r in select * from jsonb_populate_recordset(null::hoge, j) loop
    raise info 'r = %', r;
    raise info 'r.id = %', r.h_id;
    raise info 'r.nm = %', r.h_name;
  end loop;
end

↓実行結果

INFO: r = (1,hoge1)
INFO: r.id = 1
INFO: r.nm = hoge1
INFO: r = (2,hoge2)
INFO: r.id = 2
INFO: r.nm = hoge2
INFO: r = (3,hoge3)
INFO: r.id = 3
INFO: r.nm = hoge3

jsonb_populate_record

配列でなく、テーブルのレコードであるようなJSONに対しては、jsonb_populate_recordを使えば良い。

declare
  h hoge%rowtype;
  i int;
  j jsonb;
  a jsonb[];
begin
  i := 0;
  for h in select * from hoge loop
    i := i + 1;
    a[i] := to_jsonb(h);
  end loop;

  foreach j in array a loop
    h = jsonb_populate_record(null::hoge, j);
    raise info 'h = %', h;
  end loop;
end

jsonb_to_recordset

jsonb_to_recordsetの例。
for文で受け取る変数の型はrecordとし、select文のas句でカラム名とデータ型を指定する必要がある。

declare
  j jsonb;
  r record;
begin
  select jsonb_agg(h.*) into j from hoge h;

  for r in select * from jsonb_to_recordset(j) as(h_id int, h_name text) loop
    raise info 'r = %', r;
    raise info 'r.id = %', r.h_id;
    raise info 'r.nm = %', r.h_name;
  end loop;
end

配列でなく、テーブルのレコードであるようなJSONに対しては、jsonb_to_recordを使えば良さそう。

JSONの操作

JSONの各要素の取得はjsonbの演算子によって行う。

declare
  j  jsonb;
  jj jsonb;
  i  int;
begin
  select jsonb_agg(h.*) into jj from hoge h;

  for i in 0..jsonb_array_length(jj) - 1 loop
    j := jj->i;
    raise info 'j = %', j;
    raise info 'j.id = %', j->>'h_id';
  end loop;
end

j := '[{"a":"foo"}, {"b":"zzz"}]';
raise info 'j[0] = %', j->0;

j := '{"a":"foo", "b":"zzz"}';
raise info 'j[a] = %', j->'a';

j := '[{"a":"foo"}, {"b":"zzz"}]';
raise info 'j[0] = %', j->>0;

j := '{"a":"foo", "b":"zzz"}';
raise info 'j[a] = %', j->>'a';

j := '{"a": {"b":{"c": "foo"}}}';
raise info 'j[a/b] = %', j#>'{a,b}';

JSONオブジェクト（jsonb）として欲しい場合は「->」、textとして欲しい場合（JSONの末端）は「->>」を使うのが良いようだ。

JSONの更新

JSONオブジェクト（jsonb）を直接更新する方法は無いっぽい？
jsonbを変更した新しいjsonbを作る方法はある。

j := '{"a":"foo", "b":"zzz"}';
j := jsonb_set(j, '{a}', to_jsonb('bar'::text));
raise info 'j = %', j;

j := '[{"a":"foo"}, {"a":"zzz"}]';
j := jsonb_insert(j, '{1}', '{"a":"bar"}'::jsonb, true);
raise info 'j = %', j;

j := '[{"a":"foo"}, {"a":"zzz"}]';
j := j || '{"a":"bar"}'::jsonb;
raise info 'j = %', j;

j := '{"a":"foo", "b":"zzz"}';
j := j - 'b';
raise info 'j = %', j;

j := '{"a":"foo", "b":"zzz"}';
j := j #- '{b}';
raise info 'j = %', j;