postgres java 数据类型 postjson数据

转载

mob64ca13f7ecc9 2023-09-29 20:50:53

文章标签 postgres java 数据类型 postgresql json 数据库 JSON 文章分类 Java 后端开发

作者：瀚高PG实验室（Highgo PG Lab）- Kalath

什么是JSON？

JSON是一种半结构化的、当前最为流行的几种数据交换格式之一。RFC 7159中定义了JSON数据类型的输入/输出语法。合法的JSON（或者JSON）表达式有两种：

一种是标量类型，包括数字、带引号的字符串、true、false或者null。

另一种是非标量类型，包括有零个或者更多元素的数组、包含键值对的对象。二进制数据不能直接存放，例如如果想要存
放图片，需要先使用base64进行编码。同样，时间/日期类型的数据也不能直接存放。

如何在PostgreSQL中使用JSON？

PostgreSQL中提供了很多用于JSON和JSONB类型的操作符和函数。

下面通过几个示例来展示用于两种JSON类型操作符的使用方法：

1) 获得键'a'的值

postgres=# select '{"a":"foo","b":1}'::json->'a';

 ?column? 

----------

 "foo"

(1 row)

2) 以文本形式获得键'a'的值

postgres=# select '{"a":"foo","b":1}'::json->>'a';

 ?column? 

----------

 foo

(1 row)

3) 获得下标为1的数组元素

postgres=# select '["postgres",3,null,true,2,33,{"a":12}]'::json->1;

 ?column? 

----------

 3

(1 row)

4) 找到下标为5的数组元素，并以文本形式获得键'a'的值

postgres=# select '["postgres",3,null,true,2.33,{"a":"red"}]'::json #>>'{5,a}';

 ?column? 

----------

 red

(1 row)

5) 找到下标为5的数组元素，并获得键'a'的值

postgres=# select '["postgres",3,null,true,2.33,{"a":"red"}]'::json #>'{5,a}';

 ?column? 

----------

 "red"

(1 row)

6) 将两个数组合并为一个数组

postgres=# select '[1,2,3]'::jsonb || '[6,5,4]'::jsonb;

      ?column?      

--------------------

 [1, 2, 3, 6, 5, 4]

(1 row)

7) 删除数组中的第三个元素

postgres=# select '[6,5,4,3,2]'::jsonb -3;

   ?column?   

--------------

 [6, 5, 4, 2]

(1 row)

8) 将两个对象合并为一个对象，使用jsonb数据类型，重复的键只保留最后的一个值。

postgres=# select '{"a":3,"b":1}'::jsonb || '{"a":10,"c":20}'::jsonb;

          ?column?          

----------------------------

 {"a": 10, "b": 1, "c": 20}

(1 row)

9) 删除键'a'

postgres=# select '{"a":1,"b":2}'::jsonb - 'a';

 ?column? 

----------

 {"b": 2}

(1 row)

JSON函数

PostgreSQL提供了很多适用于JSON类型的函数，包括JSON创建函数、JSON处理函数等类别。

下面通过几个示例来展示用于JSON类型函数的使用方法：

1) 将值返回为json或者jsonb类型

postgres=# select to_json('[1,2,3] "Postgres"'::text);

        to_json         

------------------------

 "[1,2,3] \"Postgres\""

(1 row)

2) 等效于#>操作符

postgres=# select json_extract_path('["postgres",3,null,true,2.33,{"a":"red"}]'::json,5::text,'a');

 json_extract_path 

-------------------

 "red"

(1 row)

3) 等效于#>>操作符

postgres=# select json_extract_path_text('["postgres",3,null,true,2.33,{"a":"red"}]'::json,'5','a');

 json_extract_path_text 

------------------------

 red

(1 row)

4) 将键'a'的值替换为3

postgres=# select jsonb_set('{"a":1,"b":2}','{a}','3'::jsonb);

    jsonb_set     

------------------

 {"a": 3, "b": 2}

(1 row)

5) 将下标为3的元素的值替换为4

postgres=# select jsonb_set('[6,7,8,9,10]','{3}','4'::jsonb);

    jsonb_set     

------------------

 [6, 7, 8, 4, 10]

(1 row)

6) 指定下标不存在，则添加值到数组末尾

postgres=# select jsonb_set('[6,7,8,9,10]','{99}','99'::jsonb);

      jsonb_set       

----------------------

 [6, 7, 8, 9, 10, 99]

(1 row)

7) 将值99插入到数组中下标为4的位置上

postgres=# select jsonb_insert('[1,2,3,4,5,6]','{4}','99');

      jsonb_insert      

------------------------

 [1, 2, 3, 4, 99, 5, 6]

(1 row)

8) 将值20插入到路径中，位于键'b'的值中数组下标为2的位置

postgres=# select jsonb_insert('{"a":true,"b":[5,6,7,8,9],"c":null}','{b,2}','20');

                   jsonb_insert                   

--------------------------------------------------

 {"a": true, "b": [5, 6, 20, 7, 8, 9], "c": null}

(1 row)

其他函数和操作符请参考官方文档。