阅读678 返回首页    go 阿里云

JSON数据类型操作__快速入门_云数据库 HybridDB-阿里云

JSON类型几乎已成为互联网及物联网（IoT）的基础数据类型，其重要性不言而喻，具体协议请参考官网。

PostgreSQL对JSON的支持已经比较完善，阿里云深度优化云数据库HybridDB，基于PostgreSQL语法进行了JSON数据类型的支持。

检查现有版本是否支持JSON

首先，先来看看是否已经支持了JSON，可以通过下面的命令：

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

这说明已经支持了JSON的类型，可以使用了，如果执行不成功，请[重新启动实例]。如果报下面这个，则是未支持：

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

上面的命令很简单，是一次从字符串到JSON格式的强制转换，这也基本上是其操作上的本质。

JSON在数据库中的转换

数据库的操作主要分为读和写，JSON的写一般是数据库中的字符串到JSON，但字符串中的内容支持是比较标准的，支持JSON标准中包括字符串、数字、数组、对象等所有内容。如：

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

“::”在PostgreSQL/Greenplum/HybridDB代表着强制类型转换。在此转换的时候，会调用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. =>

这里的””是必不可少的，原因是在标准中，KEY值对应的是一个字符串。因此在语法上会报错。

除了类型上的强制转换，还有的就是从数据库记录到JSON串的转换。

我们正常使用JSON，不会只用一个String或一个Number，而往往是一个对象，对象中包含一个或多个键值对。那么，对GP来说，支持到对象的转换，即支持了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的协议只有三种数字：整数、浮点数、常数表达式，当前GP对这三种都有很好的支持。

  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()

最后更新：2016-12-11 20:56:22

  上一篇：go 客户端工具使用__快速入门_云数据库 HybridDB-阿里云
  下一篇：go 云数据库HybridDB 服务协议__服务条款_云数据库 HybridDB-阿里云