JSON は、インターネットと IoT (Internet of Things) の標準データ型になっています。具体的なプロトコルについては、JSON 公式 Web サイトをご参照ください。PostgreSQL は JSON に対応しています。また、AnalyticDB for PostgreSQL も PostgreSQL 構文に基づく JSON データ型に対応しています。
このドキュメントでは、AnalyticDB for PostgreSQL における JSON データの基本的な操作とサポートされるオブジェクトについて説明します (互換性の確認、文字列から JSON への変換、内部データ型、演算子、および関数など)。また、参考として使用例が紹介されています。
現在のバージョンが JSON をサポートしているかどうかの確認
AnalyticDB for PostgreSQL インスタンスを起動し、次のコマンドを実行して、現在のバージョンが JSON をサポートしているかどうかを確認します。
=> SELECT '""'::json;
操作が失敗した場合は、インスタンスを再起動し、上記のコマンドをもう一度実行してください。
このコマンドは、文字列を JSON 形式へ型強制しています。次の結果は、JSON 型がサポートされているかどうかを示します。
システムから次の応答が返ってきた場合は、JSON 型がサポートされており、インスタンスで使用できることを示します。
json
------
""
(1 row)
システムから次の応答が返ってきた場合は、JSON 型がサポートされていないことを示します。
ERROR: type "json" does not exist
LINE 1: SELECT '""'::json;
^
データベースでの JSON 変換
データベースに対する主な操作は、読み取りと書き込みです。JSON データを書き込むということは、文字列を JSON 形式に変換するということです。文字列の内容は、文字列、数値、配列、オブジェクトなど、JSON 標準に準拠している必要があります。以下に例を示します。
文字列
=> SELECT '"hijson"'::json;
json
-------
"hijson"
(1 row)
PostgreSQL、Greenplum、AnalyticDB for PostgreSQL では、::
は、型強制を表します。変換処理では、JSON 型入力関数が呼び出されます。したがって、型変換中に、次のように JSON 形式のチェックが行われます。
=> SELECT '{hijson:1024}'::json;
ERROR: invalid input syntax for type json
LINE 1: SELECT '{hijson:1024}'::json;
^
DETAIL: Token "hijson" is invalid.
CONTEXT: JSON data, line 1: {hijson...
=>
前述のように、"hijson"
には "
が必要です。JSON 標準ではキー値を文字列にする必要があるため、{hijson:1024}
と指定すると構文エラーが返されます。
型の変換とは別に、データベースレコードから JSON 文字列への変換も実行されます。
通常、JSON では、1 つの文字列または 1 つの数字だけを使用するのではなく、1 つ以上のキーと値のペアを含むオブジェクトを使用します。したがって、Greenplum では、オブジェクトへの変換を、大半の JSON シナリオに適用できます。
=> select row_to_json(row('{"a":"a"}', 'b'));
row_to_json
---------------------------------
{"f1":"{\"a\":\"a\"}","f2":"b"}
(1 row)
=> select row_to_json(row('{"a":"a"}'::json, 'b'));
row_to_json
---------------------------
{"f1":{"a":"a"},"f2":"b"}
(1 row)
ここでは、文字列と JSON の違いがわかります。レコード全体を JSON 型に簡単に変換できます。
JSON 内部データ型
オブジェクト
オブジェクトは、JSON で最も頻繁に使用されるデータです。
=> select '{"key":"value"}'::json;
json
-----------------
{"key":"value"}
(1 row)
整数と浮動小数点
JSON プロトコルには、整数、浮動小数点数、および定数式という 3 種類の数値があります。Greenplum は、3 つの数値型をすべてサポートしています。
=> SELECT '1024'::json;
json
------
1024
(1 row)
=> SELECT '0.1'::json;
json
------
0.1
(1 row)
特殊な状況では、次の情報が必要です。
=> SELECT '1e100'::json;
json
-------
1e100
(1 row)
=> SELECT '{"f":1e100}'::json;
json
-------------
{"f":1e100}
(1 row)
また、非常に長い数値は次のようになります。
=> SELECT '9223372036854775808'::json;
json
---------------------
9223372036854775808
(1 row)
配列
=> SELECT '[[1,2], [3,4,5]]'::json;
json
------------------
[[1,2], [3,4,5]]
(1 row)
演算子
JSON でサポートされる演算子型
=> select oprname,oprcode from pg_operator where oprleft = 3114;
oprname | oprcode
---------+---------------------------
-> | json_object_field
->> | json_object_field_text
-> | json_array_element
->> | json_array_element_text
#> | json_extract_path_op
#>> | json_extract_path_text_op
(6 rows)
基本的な使用方法
=> SELECT '{"f":"1e100"}'::json -> 'f';
?column?
----------
"1e100"
(1 row)
=> SELECT '{"f":"1e100"}'::json ->> 'f';
?column?
----------
1e100
(1 row)
=> select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>array['f4','f6'];
?column?
-----------
"stringy"
(1 row)
=> select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>'{f4,f6}';
?column?
-----------
"stringy"
(1 row)
=> select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f2,0}';
?column?
----------
f3
(1 row)
JSON 関数
サポートされる関数
postgres=# \df *json*
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+---------------------------+------------------+-----------------------------------------------------------+--------
pg_catalog | array_to_json | json | anyarray | normal
pg_catalog | array_to_json | json | anyarray, boolean | normal
pg_catalog | json_array_element | json | from_json json, element_index integer | normal
pg_catalog | json_array_element_text | text | from_json json, element_index integer | normal
pg_catalog | json_array_elements | SETOF json | from_json json, OUT value json | normal
pg_catalog | json_array_length | integer | json | normal
pg_catalog | json_each | SETOF record | from_json json, OUT key text, OUT value json | normal
pg_catalog | json_each_text | SETOF record | from_json json, OUT key text, OUT value text | normal
pg_catalog | json_extract_path | json | from_json json, VARIADIC path_elems text[] | normal
pg_catalog | json_extract_path_op | json | from_json json, path_elems text[] | normal
pg_catalog | json_extract_path_text | text | from_json json, VARIADIC path_elems text[] | normal
pg_catalog | json_extract_path_text_op | text | from_json json, path_elems text[] | normal
pg_catalog | json_in | json | cstring | normal
pg_catalog | json_object_field | json | from_json json, field_name text | normal
pg_catalog | json_object_field_text | text | from_json json, field_name text | normal
pg_catalog | json_object_keys | SETOF text | json | normal
pg_catalog | json_out | cstring | json | normal
pg_catalog | json_populate_record | anyelement | base anyelement, from_json json, use_json_as_text boolean | normal
pg_catalog | json_populate_recordset | SETOF anyelement | base anyelement, from_json json, use_json_as_text boolean | normal
pg_catalog | json_recv | json | internal | normal
pg_catalog | json_send | bytea | json | normal
pg_catalog | row_to_json | json | record | normal
pg_catalog | row_to_json | json | record, boolean | normal
pg_catalog | to_json | json | anyelement | normal
(24 rows)
基本的な使用方法
=> SELECT array_to_json('{{1,5},{99,100}}'::int[]);
array_to_json
------------------
[[1,5],[99,100]]
(1 row)
=> SELECT row_to_json(row(1,'foo'));
row_to_json
---------------------
{"f1":1,"f2":"foo"}
(1 row)
=> SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');
json_array_length
-------------------
5
(1 row)
=> select * from json_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q;
key | value
-----+-----------
f1 | [1,2,3]
f2 | {"f3":1}
f4 | null
f5 | 99
f6 | "stringy"
(5 rows)
=> select json_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":"null"}');
json_each_text
-------------------
(f1,"[1,2,3]")
(f2,"{""f3"":1}")
(f4,)
(f5,null)
(4 rows)
=> select json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]');
json_array_elements
-----------------------
1
true
[1,[2,3]]
null
{"f1":1,"f2":[7,8,9]}
false
(6 rows)
create type jpop as (a text, b int, c timestamp);
=> select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}', false) q;
a | b | c
--------+---+---
blurfl | |
(1 row)
=> select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',false) q;
a | b | c
--------+---+--------------------------
blurfl | |
| 3 | Fri Jan 20 10:42:53 2012
(2 rows)
コード例
テーブルの作成
create table tj(id serial, ary int[], obj json, num integer);
=> insert into tj(ary, obj, num) values('{1,5}'::int[], '{"obj":1}', 5);
INSERT 0 1
=> select row_to_json(q) from (select id, ary, obj, num from tj) as q;
row_to_json
-------------------------------------------
{"f1":1,"f2":[1,5],"f3":{"obj":1},"f4":5}
(1 row)
=> insert into tj(ary, obj, num) values('{2,5}'::int[], '{"obj":2}', 5);
INSERT 0 1
=> select row_to_json(q) from (select id, ary, obj, num from tj) as q;
row_to_json
-------------------------------------------
{"f1":1,"f2":[1,5],"f3":{"obj":1},"f4":5}
{"f1":2,"f2":[2,5],"f3":{"obj":2},"f4":5}
(2 rows)
複数のテーブルの JOIN
create table tj2(id serial, ary int[], obj json, num integer);
=> insert into tj2(ary, obj, num) values('{2,5}'::int[], '{"obj":2}', 5);
INSERT 0 1
=> select * from tj, tj2 where tj.obj->>'obj' = tj2.obj->>'obj';
id | ary | obj | num | id | ary | obj | num
----+-------+-----------+-----+----+-------+-----------+-----
2 | {2,5} | {"obj":2} | 5 | 1 | {2,5} | {"obj":2} | 5
(1 row)
=> select * from tj, tj2 where json_object_field_text(tj.obj, 'obj') = json_object_field_text(tj2.obj, 'obj');
id | ary | obj | num | id | ary | obj | num
----+-------+-----------+-----+----+-------+-----------+-----
2 | {2,5} | {"obj":2} | 5 | 1 | {2,5} | {"obj":2} | 5
(1 row)
JSON 関数インデックス
CREATE TEMP TABLE test_json (
json_type text,
obj json
);
=> insert into test_json values('aa', '{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}');
INSERT 0 1
=> insert into test_json values('cc', '{"f7":{"f3":1},"f8":{"f5":99,"f6":"foo"}}');
INSERT 0 1
=> select obj->'f2' from test_json where json_type = 'aa';
?column?
----------
{"f3":1}
(1 row)
=> create index i on test_json (json_extract_path_text(obj, '{f4}'));
CREATE INDEX
=> select * from test_json where json_extract_path_text(obj, '{f4}') = '{"f5":99,"f6":"foo"}';
json_type | obj
-----------+-------------------------------------------
aa | {"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}
(1 row)
注:JSON 型は、分散キーとして使用できません。また、JSON 集計関数はサポートされていません。
以下に Python アクセスの例を示します。
#! /bin/env python
import time
import json
import psycopg2
def gpquery(sql):
conn = None
try:
conn = psycopg2.connect("dbname=sanity1x2")
conn.autocommit = True
cur = conn.cursor()
cur.execute(sql)
return cur.fetchall()
except Exception as e:
if conn:
try:
conn.close()
except:
pass
time.sleep(10)
print e
return None
def main():
sql = "select obj from tj;"
#rows = Connection(host, port, user, pwd, dbname).query(sql)
rows = gpquery(sql)
for row in rows:
print json.loads(row[0])
if __name__ == "__main__":
main()