说明
如果pretty_bool为true，在第一级别元素之间会增加换行。
row_to_json (row(1,'foo'))
{"f1":1,"f2":"foo"}
json_each(json)
set of key text, value json set of key text, value jsonb
把最外层的JSON对象展开成键/值对的集合。
select * from json_each('{"a":"foo", "b":"bar"}')
key | value
-----+-------
a | "foo"
b | "bar"
json_each_text(json)
set of key text, value text
把最外层的JSON对象展开成键/值对的集合。返回值的类型是text。
select * from json_each_text('{"a":"foo", "b":"bar"}')
key | value
-----+-------
a | foo
b | bar
json_extract_path(from_json json, VARIADIC path_elems text[])
返回path_elems指定的JSON值。等效于#>操作符。
json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4')
{"f5":99,"f6":"foo"}
json_extract_path_text(from_json json, VARIADIC path_elems text[])
返回path_elems指定的JSON值为文本。等效于#>>操作符。
json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6')
json_object_keys(json)
setof text
返回最外层JSON对象中的键集合。
json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')
json_object_keys
------------------
json_populate_record(base anyelement, from_json json)
anyelement
把Expands the object in from_json中的对象展开成一行，其中的列匹配由base定义的记录类型。
select * from json_populate_record(null::myrowtype, '{"a":1,"b":2}')
a | b
---+---
1 | 2
json_populate_recordset(base anyelement, from_json json)
set of anyelement
将from_json中最外层的对象数组展开成一个行集合，其中的列匹配由base定义的记录类型。
select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]')
a | b
---+---
1 | 2
3 | 4
json_array_elements(json)
set of json
将一个JSON数组展开成JSON值的一个集合。
select * from json_array_elements('[1,true, [2,false]]')
value
-----------
[2,false]
JSONB创建索引
JSONB类型支持GIN, BTree索引。一般情况下，我们会在JSONB类型字段上建GIN索引，语法如下：
CREATE INDEX idx_name ON table_name USING gin (idx_col);
CREATE INDEX idx_name ON table_name USING gin (idx_col jsonb_path_ops);
说明 在JSONB上创建GIN索引的方式有两种：使用默认的jsonb_ops操作符创建和使用jsonb_path_ops操作符创建。两者的区别在jsonb_ops的GIN索引中，JSONB数据中的每个key和value都是作为一个单独的索引项的，而jsonb_path_ops则只为每个value创建一个索引项。
JSON操作举例
创建表
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)
说明 JSON 类型不能支持作为分布键来使用；也不支持 JSON 聚合函数。
多表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)
JSONB创建索引
-- 创建测试表并生成数据
CREATE TABLE jtest1 (
id int,
jdoc json
CREATE OR REPLACE FUNCTION random_string(INTEGER)
RETURNS TEXT AS
$BODY$
SELECT array_to_string(
ARRAY (
SELECT substring(
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
FROM (ceil(random()*62))::int FOR 1
FROM generate_series(1, $1)
$BODY$
LANGUAGE sql VOLATILE;
insert into jtest1 select t.seq, ('{"a":{"a1":"a1a1", "a2":"a2a2"},
"name":"'||random_string(10)||'","b":"bbbbb"}')::json from
generate_series(1, 10000000) as t(seq);
CREATE TABLE jtest2 (
id int,
jdoc jsonb
CREATE TABLE jtest3 (
id int,
jdoc jsonb
insert into jtest2 select id, jdoc::jsonb from jtest1;
insert into jtest3 select id, jdoc::jsonb from jtest1;
-- 建立索引
CREATE INDEX idx_jtest2 ON jtest2 USING gin(jdoc);
CREATE INDEX idx_jtest3 ON jtest3 USING gin(jdoc jsonb_path_ops);
-- 未建索引执行
EXPLAIN ANALYZE SELECT * FROM jtest1 where jdoc @> '{"name":"N9WP5txmVu"}';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..162065.73 rows=10100 width=88) (actual time=1343.248..1777.605 rows=1 loops=1)
-> Seq Scan on jtest2 (cost=0.00..162065.73 rows=5050 width=88) (actual time=0.042..1342.426 rows=1 loops=1)
Filter: (jdoc @> '{"name": "N9WP5txmVu"}'::jsonb)
Planning time: 0.172 ms
(slice0) Executor memory: 59K bytes.
(slice1) Executor memory: 91K bytes avg x 2 workers, 91K bytes max (seg0).
Memory used: 2047000kB
Optimizer: Postgres query optimizer
Execution time: 1778.234 ms
(9 rows)
-- 使用jsonb_ops操作符创建索引执行
EXPLAIN ANALYZE SELECT * FROM jtest2 where jdoc @> '{"name":"N9WP5txmVu"}';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=88.27..13517.81 rows=10100 width=88) (actual time=0.655..0.659 rows=1 loops=1)
-> Bitmap Heap Scan on jtest2 (cost=88.27..13517.81 rows=5050 width=88) (actual time=0.171..0.172 rows=1 loops=1)
Recheck Cond: (jdoc @> '{"name": "N9WP5txmVu"}'::jsonb)
-> Bitmap Index Scan on idx_jtest2 (cost=0.00..85.75 rows=5050 width=0) (actual time=0.217..0.217 rows=1 loops=1)
Index Cond: (jdoc @> '{"name": "N9WP5txmVu"}'::jsonb)
Planning time: 0.151 ms
(slice0) Executor memory: 69K bytes.
(slice1) Executor memory: 628K bytes avg x 2 workers, 632K bytes max (seg1). Work_mem: 9K bytes max.
Memory used: 2047000kB
Optimizer: Postgres query optimizer
Execution time: 1.266 ms
(11 rows)
-- 使用jsonb_path_ops操作符创建索引执行
EXPLAIN ANALYZE SELECT * FROM jtest3 where jdoc @> '{"name":"N9WP5txmVu"}';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=84.28..13513.81 rows=10101 width=88) (actual time=0.710..0.711 rows=1 loops=1)
-> Bitmap Heap Scan on jtest3 (cost=84.28..13513.81 rows=5051 width=88) (actual time=0.179..0.181 rows=1 loops=1)
Recheck Cond: (jdoc @> '{"name": "N9WP5txmVu"}'::jsonb)
-> Bitmap Index Scan on idx_jtest3 (cost=0.00..81.75 rows=5051 width=0) (actual time=0.106..0.106 rows=1 loops=1)
Index Cond: (jdoc @> '{"name": "N9WP5txmVu"}'::jsonb)
Planning time: 0.144 ms
(slice0) Executor memory: 69K bytes.
(slice1) Executor memory: 305K bytes avg x 2 workers, 309K bytes max (seg1). Work_mem: 9K bytes max.
Memory used: 2047000kB
Optimizer: Postgres query optimizer
Execution time: 1.291 ms
(11 rows)
下面是Python访问的一个例子：
#! /bin/env python
import time
import json
import psycopg2
def gpquery(sql):
conn = None
conn = psycopg2.connect("dbname=sanity1x2")
conn.autocommit = True
cur = conn.cursor()
cur.execute(sql)
return cur.fetchall()
except Exception as e:
if conn:
conn.close()
except:
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()
