すべてのプロダクト
Search

このプロダクト

    :JSON データの操作

    ドキュメントセンター

    :JSON データの操作

    最終更新日:Mar 19, 2020

    JSON は、インターネットと IoT (Internet of Things) の標準データ型になっています。具体的なプロトコルについては、JSON 公式 Web サイトをご参照ください。PostgreSQL は JSON に対応しています。また、AnalyticDB for PostgreSQL も PostgreSQL 構文に基づく JSON データ型に対応しています。

    このドキュメントでは、AnalyticDB for PostgreSQL における JSON データの基本的な操作とサポートされるオブジェクトについて説明します (互換性の確認、文字列から JSON への変換、内部データ型、演算子、および関数など)。また、参考として使用例が紹介されています。

    現在のバージョンが JSON をサポートしているかどうかの確認

    AnalyticDB for PostgreSQL インスタンスを起動し、次のコマンドを実行して、現在のバージョンが JSON をサポートしているかどうかを確認します。

    1. => SELECT '""'::json;

    操作が失敗した場合は、インスタンスを再起動し、上記のコマンドをもう一度実行してください。

    このコマンドは、文字列を JSON 形式へ型強制しています。次の結果は、JSON 型がサポートされているかどうかを示します。

    • システムから次の応答が返ってきた場合は、JSON 型がサポートされており、インスタンスで使用できることを示します。

      1.    json 
      2.   ------
      3.    ""
      4.   (1 row)

    • システムから次の応答が返ってきた場合は、JSON 型がサポートされていないことを示します。

      1.   ERROR:  type "json" does not exist
      2.   LINE 1: SELECT '""'::json;
      3.                        ^

    データベースでの JSON 変換

    データベースに対する主な操作は、読み取りと書き込みです。JSON データを書き込むということは、文字列を JSON 形式に変換するということです。文字列の内容は、文字列、数値、配列、オブジェクトなど、JSON 標準に準拠している必要があります。以下に例を示します。

    文字列

    1. => SELECT '"hijson"'::json;
    2.  json  
    3. -------
    4.  "hijson"
    5. (1 row)

    PostgreSQL、Greenplum、AnalyticDB for PostgreSQL では、:: は、型強制を表します。変換処理では、JSON 型入力関数が呼び出されます。したがって、型変換中に、次のように JSON 形式のチェックが行われます。

    1. => SELECT '{hijson:1024}'::json;
    3. ERROR:  invalid input syntax for type json
    4. LINE 1: SELECT '{hijson:1024}'::json;
    5.                ^
    6. DETAIL:  Token "hijson" is invalid.
    7. CONTEXT:  JSON data, line 1: {hijson...
    8. =>

    前述のように、"hijson" には " が必要です。JSON 標準ではキー値を文字列にする必要があるため、{hijson:1024} と指定すると構文エラーが返されます。

    型の変換とは別に、データベースレコードから JSON 文字列への変換も実行されます。

    通常、JSON では、1 つの文字列または 1 つの数字だけを使用するのではなく、1 つ以上のキーと値のペアを含むオブジェクトを使用します。したがって、Greenplum では、オブジェクトへの変換を、大半の JSON シナリオに適用できます。

    1. => select row_to_json(row('{"a":"a"}', 'b'));
    2.            row_to_json           
    3. ---------------------------------
    4.  {"f1":"{\"a\":\"a\"}","f2":"b"}
    5. (1 row)
    7. => select row_to_json(row('{"a":"a"}'::json, 'b'));
    8.         row_to_json        
    9. ---------------------------
    10.  {"f1":{"a":"a"},"f2":"b"}
    11. (1 row)

    ここでは、文字列と JSON の違いがわかります。レコード全体を JSON 型に簡単に変換できます。

    JSON 内部データ型

    • オブジェクト

      オブジェクトは、JSON で最も頻繁に使用されるデータです。

      1.   => select '{"key":"value"}'::json;
      2.         json       
      3.   -----------------
      4.    {"key":"value"}
      5.   (1 row)

    • 整数と浮動小数点

      JSON プロトコルには、整数、浮動小数点数、および定数式という 3 種類の数値があります。Greenplum は、3 つの数値型をすべてサポートしています。

      1.   => SELECT '1024'::json;
      2.    json 
      3.   ------
      4.    1024
      5.   (1 row)
      7.   => SELECT '0.1'::json;
      8.    json 
      9.   ------
      10.    0.1
      11.   (1 row)

      特殊な状況では、次の情報が必要です。

      1.   => SELECT '1e100'::json;
      2.    json  
      3.   -------
      4.    1e100
      5.   (1 row)
      7.   => SELECT '{"f":1e100}'::json;
      8.       json     
      9.   -------------
      10.    {"f":1e100}
      11.   (1 row)

      また、非常に長い数値は次のようになります。

      1.   => SELECT '9223372036854775808'::json;
      2.           json         
      3.   ---------------------
      4.    9223372036854775808
      5.   (1 row)

    • 配列

      1.   => SELECT '[[1,2], [3,4,5]]'::json;
      2.          json       
      3.   ------------------
      4.    [[1,2], [3,4,5]]
      5.   (1 row)

    演算子

    JSON でサポートされる演算子型

    1. => select oprname,oprcode from pg_operator where oprleft = 3114;
    2.  oprname |          oprcode
    3. ---------+---------------------------
    4.  ->      | json_object_field
    5.  ->>     | json_object_field_text
    6.  ->      | json_array_element
    7.  ->>     | json_array_element_text
    8.  #>      | json_extract_path_op
    9.  #>>     | json_extract_path_text_op
    10. (6 rows)

    基本的な使用方法

    1. => SELECT '{"f":"1e100"}'::json -> 'f';
    2.  ?column?
    3. ----------
    4.  "1e100"
    5. (1 row)
    7. => SELECT '{"f":"1e100"}'::json ->> 'f';
    8.  ?column?
    9. ----------
    10.  1e100
    11. (1 row)
    13. => select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>array['f4','f6'];
    14.  ?column?
    15. -----------
    16.  "stringy"
    17. (1 row)
    19. => select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>'{f4,f6}';
    20.  ?column?
    21. -----------
    22.  "stringy"
    23. (1 row)
    25. => select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f2,0}';
    26.  ?column?
    27. ----------
    28.  f3
    29. (1 row)

    JSON 関数

    サポートされる関数

    1. postgres=# \df *json*
    2.                                                        List of functions
    3.    Schema   |           Name            | Result data type |                    Argument data types                    |  Type
    4. ------------+---------------------------+------------------+-----------------------------------------------------------+--------
    5.  pg_catalog | array_to_json             | json             | anyarray                                                  | normal
    6.  pg_catalog | array_to_json             | json             | anyarray, boolean                                         | normal
    7.  pg_catalog | json_array_element        | json             | from_json json, element_index integer                     | normal
    8.  pg_catalog | json_array_element_text   | text             | from_json json, element_index integer                     | normal
    9.  pg_catalog | json_array_elements       | SETOF json       | from_json json, OUT value json                            | normal
    10.  pg_catalog | json_array_length         | integer          | json                                                      | normal
    11.  pg_catalog | json_each                 | SETOF record     | from_json json, OUT key text, OUT value json              | normal
    12.  pg_catalog | json_each_text            | SETOF record     | from_json json, OUT key text, OUT value text              | normal
    13.  pg_catalog | json_extract_path         | json             | from_json json, VARIADIC path_elems text[]                | normal
    14.  pg_catalog | json_extract_path_op      | json             | from_json json, path_elems text[]                         | normal
    15.  pg_catalog | json_extract_path_text    | text             | from_json json, VARIADIC path_elems text[]                | normal
    16.  pg_catalog | json_extract_path_text_op | text             | from_json json, path_elems text[]                         | normal
    17.  pg_catalog | json_in                   | json             | cstring                                                   | normal
    18.  pg_catalog | json_object_field         | json             | from_json json, field_name text                           | normal
    19.  pg_catalog | json_object_field_text    | text             | from_json json, field_name text                           | normal
    20.  pg_catalog | json_object_keys          | SETOF text       | json                                                      | normal
    21.  pg_catalog | json_out                  | cstring          | json                                                      | normal
    22.  pg_catalog | json_populate_record      | anyelement       | base anyelement, from_json json, use_json_as_text boolean | normal
    23.  pg_catalog | json_populate_recordset   | SETOF anyelement | base anyelement, from_json json, use_json_as_text boolean | normal
    24.  pg_catalog | json_recv                 | json             | internal                                                  | normal
    25.  pg_catalog | json_send                 | bytea            | json                                                      | normal
    26.  pg_catalog | row_to_json               | json             | record                                                    | normal
    27.  pg_catalog | row_to_json               | json             | record, boolean                                           | normal
    28.  pg_catalog | to_json                   | json             | anyelement                                                | normal
    29. (24 rows)

    基本的な使用方法

    1. => SELECT array_to_json('{{1,5},{99,100}}'::int[]);
    2.   array_to_json
    3. ------------------
    4.  [[1,5],[99,100]]
    5. (1 row)
    7. => SELECT row_to_json(row(1,'foo'));
    8.      row_to_json
    9. ---------------------
    10.  {"f1":1,"f2":"foo"}
    11. (1 row)
    13. => SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');
    14.  json_array_length
    15. -------------------
    16.                  5
    17. (1 row)
    19. => select * from json_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q;
    20.  key |   value
    21. -----+-----------
    22.  f1  | [1,2,3]
    23.  f2  | {"f3":1}
    24.  f4  | null
    25.  f5  | 99
    26.  f6  | "stringy"
    27. (5 rows)
    29. => select json_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":"null"}');
    30.   json_each_text
    31. -------------------
    32.  (f1,"[1,2,3]")
    33.  (f2,"{""f3"":1}")
    34.  (f4,)
    35.  (f5,null)
    36. (4 rows)
    38. => select json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]');
    39.   json_array_elements
    40. -----------------------
    41.  1
    42.  true
    43.  [1,[2,3]]
    44.  null
    45.  {"f1":1,"f2":[7,8,9]}
    46.  false
    47. (6 rows)
    49. create type jpop as (a text, b int, c timestamp);
    50. => select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}', false) q;
    51.    a    | b | c
    52. --------+---+---
    53.  blurfl |   |
    54. (1 row)
    56. =>     select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',false) q;
    57.    a    | b |            c
    58. --------+---+--------------------------
    59.  blurfl |   |
    60.         | 3 | Fri Jan 20 10:42:53 2012
    61. (2 rows)

    コード例

    テーブルの作成

    1. create table tj(id serial, ary int[], obj json, num integer);
    3. => insert into tj(ary, obj, num) values('{1,5}'::int[], '{"obj":1}', 5);
    4. INSERT 0 1
    5. => select row_to_json(q) from (select id, ary, obj, num from tj) as q;
    6.                 row_to_json                
    7. -------------------------------------------
    8.  {"f1":1,"f2":[1,5],"f3":{"obj":1},"f4":5}
    9. (1 row)
    11. => insert into tj(ary, obj, num) values('{2,5}'::int[], '{"obj":2}', 5);
    12. INSERT 0 1
    14. => select row_to_json(q) from (select id, ary, obj, num from tj) as q;
    15.                 row_to_json                
    16. -------------------------------------------
    17.  {"f1":1,"f2":[1,5],"f3":{"obj":1},"f4":5}
    18.  {"f1":2,"f2":[2,5],"f3":{"obj":2},"f4":5}
    19. (2 rows)

    複数のテーブルの JOIN

    1. create table tj2(id serial, ary int[], obj json, num integer);
    3. => insert into tj2(ary, obj, num) values('{2,5}'::int[], '{"obj":2}', 5);
    4. INSERT 0 1
    6. => select * from tj, tj2 where tj.obj->>'obj' = tj2.obj->>'obj';
    7.  id |  ary  |    obj    | num | id |  ary  |    obj    | num
    8. ----+-------+-----------+-----+----+-------+-----------+-----
    9.   2 | {2,5} | {"obj":2} |   5 |  1 | {2,5} | {"obj":2} |   5
    10. (1 row)
    12. => select * from tj, tj2 where json_object_field_text(tj.obj, 'obj') = json_object_field_text(tj2.obj, 'obj');
    13.  id |  ary  |    obj    | num | id |  ary  |    obj    | num
    14. ----+-------+-----------+-----+----+-------+-----------+-----
    15.   2 | {2,5} | {"obj":2} |   5 |  1 | {2,5} | {"obj":2} |   5
    16. (1 row)

    JSON 関数インデックス

    1. CREATE TEMP TABLE test_json (
    2.        json_type text,
    3.        obj json
    4. );
    6. => insert into test_json values('aa', '{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}');
    7. INSERT 0 1
    9. => insert into test_json values('cc', '{"f7":{"f3":1},"f8":{"f5":99,"f6":"foo"}}');
    10. INSERT 0 1
    12. => select obj->'f2' from test_json where json_type = 'aa';
    13.  ?column?
    14. ----------
    15.  {"f3":1}
    16. (1 row)
    18. => create index i on test_json (json_extract_path_text(obj, '{f4}'));
    19. CREATE INDEX
    21. => select * from test_json where json_extract_path_text(obj, '{f4}') = '{"f5":99,"f6":"foo"}';
    22.  json_type |                 obj
    23. -----------+-------------------------------------------
    24.  aa        | {"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}
    25. (1 row)

    注：JSON 型は、分散キーとして使用できません。また、JSON 集計関数はサポートされていません。

    以下に Python アクセスの例を示します。

    1. #! /bin/env python
    3. import time
    4. import json
    5. import psycopg2
    7. def gpquery(sql):
    8.     conn = None 
    9.     try: 
    10.         conn = psycopg2.connect("dbname=sanity1x2")
    11.         conn.autocommit = True 
    12.         cur = conn.cursor() 
    13.         cur.execute(sql) 
    14.         return cur.fetchall()
    15.     except Exception as e: 
    16.         if conn: 
    17.             try:     
    18.                 conn.close() 
    19.             except: 
    20.                 pass 
    21.             time.sleep(10)
    22.         print e
    23.     return None
    26. def main():
    27.     sql = "select obj from tj;"
    28.     #rows = Connection(host, port, user, pwd, dbname).query(sql) 
    29.     rows = gpquery(sql)
    30.     for row in rows:
    31.         print json.loads(row[0])
    34. if __name__ == "__main__":
    35.     main()