hubble数据库函数之JSON函数

• to_json

select to_json('json type'::text);

    to_json
---------------
  "json type"

• to_jsonb

select to_jsonb('json type'::text);

    to_jsonb
---------------
  "json type"

• json_array_length

SELECT json_array_length('[1,2,3,4]');

  json_array_length
---------------------
                  4

• json_build_array

 select json_build_array(1,2,'3',4,5);

  json_build_array
---------------------
  [1, 2, "3", 4, 5]

• json_build_object

select json_build_object('1','2','3','4')

   json_build_object
------------------------
  {"1": "2", "3": "4"}

• jsonb_pretty

数据准备

CREATE TABLE reports (rep_id int primary key, data json);


INSERT INTO reports (rep_id, data)
VALUES
(1, '{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"}, {"album": 2, "src":"barB.png", "pos": "top"}], "background":"background.png"}')
, (2, '{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"}, {"album": 2, "src":"barC.png", "pos": "top"}], "background":"bacakground.png"}')
, (3, '{"objects":[{"album": 1, "src":"fooA.png", "pos": "middle"},{"album": 2, "src":"barB.png", "pos": "middle"}],"background":"background.png"}')
, (4, '{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"}, {"album": 3, "src":"barB.png", "pos": "top"}], "background":"backgroundA.png"}')
;

select jsonb_pretty( '{"name": "Alice", "agent": {"bot": true} }'::jsonb );

     jsonb_pretty
-----------------------
  {
      "agent": {
          "bot": true
      },
      "name": "Alice"
  }

• json_object_keys

返回最外层的json对象中的键的集合

select * from json_object_keys('{"b":"1","a":"2"}');

  json_object_keys
--------------------
  a
  b

• jsonb_set

json值的更新，jsonb_set函数，格式：jsonb_set(target jsonb,path text[],new_value jsonb[, create_missing boolean])，target指源jsonb数据，path指路径，new_value指更新后的键值，create_missing值为true表示键不存在则添加，为false表示如果键不存在则不添加。

select jsonb_set('{"name":"bob","age":"27"}'::jsonb,'{age}','"28"'::jsonb,false); 

           jsonb_set
--------------------------------
  {"age": "28", "name": "bob"}

• json_each

扩展最外层的json对象成为一组键值结果集

select * from json_each('{"b":"1","a":"2"}'); 

  key | value
------+--------
  a   | "2"
  b   | "1"

• json_each_text以文本返回结果

select * from json_each_text('{"b":"1","a":"2"}');

  key | value
------+--------
  a   | 2
  b   | 1

• json_object_keys

返回最外层的json对象中的键的集合

select * from json_object_keys('{"b":"1","a":"2"}');

  json_object_keys
--------------------
  a
  b

• json_typeof

以文字字串传回最外层JSON值的类型

select * from json_typeof('{"b":"1","a":"2"}');

  json_typeof
---------------
  object

• json键值的删除用-

select '{"b":"1","a":"2"}'::json - 'a';

   column
--------------
  {"b": "1"}

• 删除嵌套json数据

select '["a","b","c"]'::jsonb - 0; 

  column  
------------
 ["b", "c"]

select '["a","b","c"]'::jsonb - 1; 

  column  
------------
 ["a", "c"]

• json_strip_nulls

select json_strip_nulls('[{"f1":1,"f2":null},2,null,3]');

     json_strip_nulls
---------------------------
  [{"f1": 1}, 2, null, 3]