JSON數據類型操作__快速入門_雲數據庫 HybridDB-阿裏雲

=> 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聚合函數；

#! /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()