2.3、jsonb函数应用

2.3.1、jsonb_each()将json对象转变键和值

  1. postgres=# select  f_jsonb  from t_jsonb where id=1;
  2.                    f_jsonb                   
  3. ---------------------------------------------
  4.  {"col": "pgxz", "col1": 1, "col2": "tbase"}
  5. (1 row)
  7. postgres=#  select * from  jsonb_each((select  f_jsonb  from t_jsonb where id=1)); 
  8.  key  |  value  
  9. ------+---------
  10.  col  | "pgxz"
  11.  col1 | 1
  12.  col2 | "tbase"
  13. (3 rows)

2.3.2、jsonb_each_text()将json对象转变文本类型的键和值

  1. postgres=#  select * from  jsonb_each_text((select  f_jsonb  from t_jsonb where id=1)); 
  2.  key  | value 
  3. ------+-------
  4.  col  | pgxz
  5.  col1 | 1
  6.  col2 | tbase
  7. (3 rows)

2.3.3、row_to_json()将一行记录变成一个json对象

  1. postgres=# \d+ tbase
  2.                                     Table "public.tbase"
  3.   Column  |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
  4. ----------+---------+-----------+----------+---------+----------+--------------+-------------
  5.  id       | integer |           | not null |         | plain    |              | 
  6.  nickname | text    |           |          |         | extended |              | 
  7. Indexes:
  8.     "tbase_pkey" PRIMARY KEY, btree (id)
  9. Distribute By: SHARD(id)
  10. Location Nodes: ALL DATANODES
  12. postgres=# select * from tbase;
  13.  id | nickname 
  14. ----+----------
  15.   1 | tbase
  16.   2 | pgxz
  17. (2 rows)
  19. postgres=# select row_to_json(tbase) from tbase;
  20.          row_to_json         
  21. -----------------------------
  22.  {"id":1,"nickname":"tbase"}
  23.  {"id":2,"nickname":"pgxz"}
  24. (2 rows)

2.3.4、json_object_keys()返回一个对象中所有的键

  1. postgres=#  select * from json_object_keys((select  f_jsonb  from t_jsonb where id=1)::json); 
  2.  json_object_keys 
  3. ------------------
  4.  col
  5.  col1
  6.  col2
  7. (3 rows)