postgreshql操作整理

最新推荐文章于 2023-06-16 16:31:09 发布
最新推荐文章于 2023-06-16 16:31:09 发布
阅读量317 收藏
点赞数
分类专栏： postgres
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zsx18273117003/article/details/102861129
版权

官网地址：https://www.postgresql.org/docs/12/functions-json.html

 # 基础操作

# json和jsonb运算符

# ->右操作数类型int，返回值为json or jsonb格式，获取JSON数组元素（从零开始索引，从末数开始为负整数
SELECT '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2;

# ->右操作数类型text，返回值为json or jsonb格式，通过键获取JSON对象字段
SELECT '{"a": {"b":"foo"}}'::json->'a';

# ->>右操作数类型int，返回值为text格式，获取JSON数组元素为 text
SELECT '[1,2,3]'::json->>2;

# ->>右操作数类型text，返回值为text格式，获取JSON数组元素为 text
SELECT '{"a":1,"b":2}'::json->>'b';

# #>右操作数类型text[]，返回值为json or jsonb格式，在指定路径获取JSON对象
SELECT '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}';

# #>>右操作数类型text[]，返回值为text格式，在指定路径下获取JSON对象为 text
SELECT '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}';

# 附加jsonb操作

# @> 右操作数类型为jsonb格式，左侧的JSON值是否在顶层包含正确的JSON路径/值条目
SELECT '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb;

# <@ 返回值为jsonb格式，左侧JSON路径/值条目是否包含在右侧JSON值的顶层
SELECT '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb;

# ?右操作数类型为text格式，字符串是否作为JSON值中的顶级键存在
SELECT '{"a":1, "b":2}'::jsonb ? 'b';

# ?|右操作数类型为text[]格式，所有这些数组字符串都作为顶级键存在吗
SELECT '{"a":1, "b":2, "c":{"d": "foo"}}'::jsonb ?| array['c', 'D'];

# ?&返回值为text[]格式，数组元素全部都存在左边的数组中
SELECT '["a", "b", {"c":"d"}]'::jsonb ?& array['a', 'c'];

# ||右操作数类型为jsonb格式，将两个jsonb值连接成一个新jsonb值
SELECT '["a", "b"]'::jsonb || '["c", "d"]'::jsonb;

# -右操作数类型为text格式，从左操作数中删除键/值对或字符串元素。键/值对基于其键值进行匹配。
SELECT '{"a": "b"}'::jsonb - 'a';

# -右操作数类型为text[]格式，从左操作数中删除多个键/值对或字符串元素。键/值对基于其键值进行匹配。
SELECT '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[];

# - 右操作数类型integer,删除具有指定索引的数组元素（末尾为负整数）。如果顶级容器不是数组，则会引发错误。
SELECT 	'["a", "b"]'::jsonb - 1;

# #-	右操作数类型为text[]格式，删除具有指定路径的字段或元素（对于JSON数组，从末尾算起负整数）
SELECT '["a", {"b":1}]'::jsonb #- '{1,b}';

# @? 右操作数类型为jsonpath	JSON路径是否为指定的JSON值返回任何项目？
SELECT '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)';

# @@	右操作数类型jsonpath	返回指定JSON值的JSON路径谓词检查的结果。仅考虑结果的第一项。如果结果不是布尔值，则null返回。	
SELECT '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2';

# json创建函数

# 函数to_json(anyelement)|to_jsonb(anyelement)
# 返回值为json或jsonb。
# 数组和组合被（递归）转换为数组和对象。否则，如果存在从类型到json的转换，则将使用转换功能执行转换；否则，将使用转换功能执行转换。
# 否则，产生标量值。对于除数字，布尔值或空值以外的任何标量类型，将使用文本表示形式，即它是有效值json还是jsonb值。
# "Fred said \"Hi.\""
SELECT to_json('Fred said "Hi."'::text);

# 函数array_to_json(anyarray [, pretty_bool])
# 将数组作为JSON数组返回。
# PostgreSQL多维数组变成数组的JSON数组。如果pretty_bool为true，则将在维度1元素之间添加换行符。
# [[1,5],[99,100]]
SELECT array_to_json('{{1,5},{99,100}}'::int[]);

# 函数row_to_json(record [, pretty_bool])	以JSON对象的形式返回该行。如果pretty_bool为true，则将在1级元素之间添加换行符。
# {"f1":1,"f2":"foo"}
SELECT row_to_json(row(1,'foo'));

# 函数json_build_array(VARIADIC "any")，jsonb_build_array(VARIADIC "any")
# 根据可变参数列表构建一个可能是异构类型的JSON数组。	
# [1, 2, "3", 4, 5]
SELECT json_build_array(1,2,'3',4,5);

# 函数json_build_object(VARIADIC "any")，jsonb_build_object(VARIADIC "any")
# 从可变参数列表中构建JSON对象。按照惯例，参数列表由交替的键和值组成。	
# {"foo": 1, "bar": 2}
SELECT json_build_object('foo',1,'bar',2);

# 函数json_object(text[])，jsonb_object(text[])
# 从文本数组构建JSON对象。
# 数组必须具有一个具有偶数个成员的正好一维（在这种情况下，它们被视为交替的键/值对），
# 或者必须具有两个维，以使每个内部数组都具有两个正好作为键/值对的元素。
# {"a": "1", "b": "def", "c": "3.5"}
SELECT json_object('{a, 1, b, "def", c, 3.5}');

SELECT json_object('{{a, 1},{b, "def"},{c, 3.5}}');


# 函数json_object(keys text[], values text[])，jsonb_object(keys text[], values text[])
# 这种形式的json_object键和值成对地来自两个单独的数组。在所有其他方面，它与单参数形式相同。
# {"a": "1", "b": "2"}
SELECT json_object('{a, b}', '{1,2}');

# json处理函数

# 函数json_array_length(json)，jsonb_array_length(jsonb)
# 返回类型int
# 返回最外面的JSON数组中的元素数。	
SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');

# 函数json_each(json)，jsonb_each(jsonb)
# 返回值类型setof key text, value json | jsonb
# 将最外面的JSON对象扩展为一组键/值对。
SELECT * FROM json_each('{"a":"foo", "b":"bar"}');

# 函数json_each_text(json)，jsonb_each_text(jsonb)
# 返回类型setof key text, value text
# 将最外面的JSON对象扩展为一组键/值对。返回的值将是类型text。
SELECT * FROM json_each_text('{"a":"foo", "b":"bar"}');

# 函数json_extract_path(from_json json, VARIADIC path_elems text[])，jsonb_extract_path(from_json jsonb, VARIADIC path_elems text[])
# 返回类型json，jsonb
# 返回指向的JSON值path_elems（等效于#>运算符）。
SELECT json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4');

# 函数json_extract_path_text(from_json json, VARIADIC path_elems text[])，jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[])
# 返回类型text
# 返回path_elemsas所指向的JSON值text（等效于#>>运算符）。
SELECT json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6');

# 函数json_object_keys(json)，jsonb_object_keys(jsonb)
# 返回类型setof text
# 返回最外层JSON对象中的键集。
SELECT json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}');


# 函数json_populate_record(base anyelement, from_json json)，jsonb_populate_record(base anyelement, from_json jsonb)
# 返回类型anyelement
# 将对象扩展from_json到其列与定义的记录类型相匹配的行
DROP TYPE jsonb_test;
CREATE TYPE jsonb_test AS (a INT, b VARCHAR[], c jsonb);
SELECT * FROM json_populate_record(null::jsonb_test, '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}}');


# 函数json_populate_recordset(base anyelement, from_json json)，jsonb_populate_recordset(base anyelement, from_json jsonb)
# 返回类型setof anyelement
# 将对象的最外面的数组扩展from_json为一组行，这些行的列与定义的记录类型相匹配
DROP TYPE jsonb_test1;
CREATE TYPE jsonb_test1 AS (a INT, b INT);
SELECT * FROM json_populate_recordset(null::jsonb_test1, '[{"a":1,"b":2},{"a":3,"b":4}]');


# 函数json_array_elements(json)，jsonb_array_elements(jsonb)
# 返回类型setof json | jsonb
# 将JSON数组扩展为一组JSON值。
SELECT * FROM json_array_elements('[1,true, [2,false]]');

# 函数json_array_elements_text(json)，jsonb_array_elements_text(jsonb)
# 返回类型setof text
# 将JSON数组扩展为一组text值。
SELECT * FROM json_array_elements_text('["foo", "bar"]');

# 函数json_typeof(json)，jsonb_typeof(jsonb)
# 返回类型text
# 以文字字串传回最外层JSON值的类型。可能的类型有object，array，string，number，boolean，和null。
SELECT json_typeof('-123.4');

# 函数json_to_record(json，jsonb_to_record(jsonb)
# 返回类型record
# 从JSON对象构建任意记录。与所有返回函数一样record，调用者必须使用AS子句显式定义记录的结构。
DROP TYPE jsonb_test2;
CREATE TYPE jsonb_test2 AS (a INT, b jsonb);
SELECT * FROM json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r jsonb_test2);

# 函数json_to_recordset(json)，jsonb_to_recordset(jsonb)
# 返回类型setof record
# 根据对象的JSON数组构建任意记录集。与所有返回函数一样record，调用者必须使用AS子句显式定义记录的结构。
SELECT * FROM json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text);

# 函数json_strip_nulls(from_json json)，jsonb_strip_nulls(from_json jsonb)
# 返回类型json，jsonb
# 返回from_json所有具有空值的对象字段。其他空值保持不变。
SELECT json_strip_nulls('[{"f1":1,"f2":null},2,null,3]');

# 函数jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean])
# 返回类型jsonb
# 返回target，用指定的部分path替换为new_value，new_value如果create_missing是true（默认为true）且添加的项path不存在，则添加。
# 与面向路径的运算符一样，path从JSON数组末尾开始计数的负整数。
SELECT jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false);
SELECT jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]');

# 函数jsonb_insert(target jsonb, path text[], new_value jsonb [, insert_after boolean])
# 返回类型jsonb
# 返回插入了new_value的target。 如果path指定的目标节点在JSONB数组中，
# insert_after为false时（默认值为false），则new_value将在target之前插入，
# insert_after为true时，则new_value将在target之后插入。 
# 如果path指定的目标节点位于JSONB对象中，则只有目标不存在时才插入new_value。 
# 与面向路径的运算符一样，出现在路径中的负整数从JSON数组的末尾开始计数。
# {"a": [0, "new_value", 1, 2]}
SELECT jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"');
# {"a": [0, 1, "new_value", 2]}
SELECT jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true);

# 函数jsonb_pretty(from_json jsonb)
# 返回类型text
# 返回from_json缩进的JSON文本。
SELECT jsonb_pretty('[{"f1":1,"f2":null},2,null,3]');

# 函数jsonb_path_exists(target jsonb, path jsonpath [, vars jsonb [, silent bool]])
# 返回类型boolean
# 检查JSON路径是否返回指定JSON值的任何项目。
SELECT jsonb_path_exists('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}');

# 函数jsonb_path_match(target jsonb, path jsonpath [, vars jsonb [, silent bool]])
# 返回类型boolean
# 返回指定JSON值的JSON路径谓词检查的结果。仅考虑结果的第一项。如果结果不是布尔值，则null返回。
SELECT jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists($.a[*] ? (@ >= $min && @ <= $max))', '{"min":2,"max":4}');

# 函数jsonb_path_query(target jsonb, path jsonpath [, vars jsonb [, silent bool]])
# 返回类型setof jsonb
# 获取JSON路径返回的指定JSON值的所有JSON项。
SELECT * FROM jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}');

# 函数jsonb_path_query_array(target jsonb, path jsonpath [, vars jsonb [, silent bool]])
# 返回类型jsonb
# 获取JSON路径返回的，用于指定JSON值的所有JSON项，并将结果包装到数组中。
SELECT jsonb_path_query_array('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}');

# 函数jsonb_path_query_first(target jsonb, path jsonpath [, vars jsonb [, silent bool]])
# 返回类型jsonb
# 获取JSON路径返回的指定JSON值的第一个JSON项。没有结果返回NULL。
SELECT jsonb_path_query_first('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}');

# 表操作

DROP TABLE test;
CREATE TABLE test(id serial, data jsonb);

INSERT INTO test(data) values ('{"name": "my-name", "tags": ["tag1", "tag2"]}');

SELECT * FROM test;

# 错误:  语法错误 在 "->" 或附近的
UPDATE test SET data->'name' = 'my-other-name' WHERE id = 1;

# 更新名称
UPDATE test SET data = jsonb_set(data, '{name}', '"my-other-name"');

# 替换标签（与添加或删除标签相反）
UPDATE test SET data = jsonb_set(data, '{tags}', '["tag3", "tag4"]');

# 替换第二个标记（0索引）
UPDATE test SET data = jsonb_set(data, '{tags,1}', '"tag5"');

# 附加一个标签
UPDATE test SET data = jsonb_set(data, '{tags,100}', '"tag6"', true);

# 删除最后一个标记
UPDATE test SET data = data #- '{tags,-1}';

# 复杂更新（删除最后一个标记，插入新标记并更改名称）
UPDATE test SET data = jsonb_set(
    jsonb_set(data #- '{tags,-1}', '{tags,100}', '"tag4"', true), 
    '{name}', '"my-other-name1"');


CREATE TABLE test1(id serial, data jsonb);
INSERT INTO test1(data) values ('{"name": "my-name", "tags": ["tag1", "tag2"]}');
SELECT * FROM test1;

UPDATE test1 
SET data = replace(data::TEXT,'"name":','"my-other-name":')::jsonb 
WHERE id = 1;

# 更新'name'属性
UPDATE test1 SET data=data||'{"name":"my-other-name"}' WHERE id = 1;

# 删除例如'name'和'tags'属性
UPDATE test1 SET data=data-'{"name","tags"}'::text[] WHERE id = 1;

 

禅剑一如
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
hibernate结合postgresql数据库在项目中的使用
潜行IT的博客
11-13 2061
添加mvn依赖： &lt;!-- hibernate dependence --&gt; &lt;dependency&gt; &lt;groupId&gt;org.hibernate&lt;/groupId&gt; &lt;artifactId&gt;hibernate-core&lt;/artifactId&gt; &...
sql/hql对json进行解析
小白
03-29 981
## Sql中json使用方法 对于jsonArray（json数组），如person表的xjson字段有数据： ```scala [{"name":"王二狗","sex":"男","age":"25"},{"name":"李狗嗨","sex":"男","age":"47"}] 取出第一个json对象，那么hive sql为： SELECT get_json_object(xjson,"$.[0]") FROM person; 结果是： {"name":"王二狗","sex":"男","age":"
pgsql到hql的转换
AnlaGodness的博客
04-29 483
工作中，遇到要从已有的pgsql 转移到hive 中，下面总结一些用到的转换。 字符定位 pgsql: position('市' in locationField) hql: instr(locationField, '市') 2.hive只支持全表操作，pg支持指定记录操作。 pgsql: update set field=newValue where field in ... hq...
postgresql使用sql或者hql语句对varchar类型的字段进行排序
qq_39181017的博客
07-19 809
SELECT tcwz FROM biao t order by cast(t.tcwz as INTEGER); 使用hql语句，如下 。。。+ order by cast(t.tcwz as int);
PostgreSql 怎么获取数据库中关键系统信息（一）
sql server的专栏
09-21 838
如何通过SQL 的方式获得数据库中的一些关键信息，是一个DB最正常的工作，如何通过一些SQL来获得PG的一些关键的参数和信息或者是数据库中的一些信息是需要知道的一件事情。以下是部分 1一...
2023年三个最佳的免费PostgreSQL GUI工具
最新发布
网络技术联盟站
06-16 2301
在本文中，我们介绍了2023年中的三个最佳免费PostgreSQL GUI工具：DBeaver、pgAdmin和Beekeeper Studio。这些工具都提供了直观的界面和丰富的功能，使用户能够方便地管理和操作PostgreSQL数据库。无论是对于开发人员、数据库管理员还是对于需要频繁使用PostgreSQL的用户来说，这些GUI工具都是强大且实用的选择。通过使用这些工具，用户可以提高工作效率、简化数据库管理，并更好地利用PostgreSQL的功能和潜力。
金仓数据库 KingbaseES SQL 语言参考手册 (8. 函数（六））
arthemis_14的博客
07-22 1479
JSON处理函数，从文本数组中构造JSON对象，该函数必须可以使具有偶数个成员的一维数组(成员被当做交替出现的键值对)，或者是一个二维数组（每一个内部数组刚好有两个元素，可以被看做键值对）。JSON处理函数，从文本数组中构造JSON对象，该函数必须可以使具有偶数个成员的一维数组(成员被当做交替出现的键值对)，或者是一个二维数组（每一个内部数组刚好有两个元素，可以被看做键值对）。如果结果不是布尔值，则返回NULL.JSON函数，获取指定JSON值的JSON路径返回的所有项，并将结果包装到数组中。...
9.15. JSON 函数和操作符
m0_51063580的博客
11-30 578
9.15.JSON 函数和操作符 9.15.1. 处理和创建JSON数据 9.15.2. SQL/JSON路径语言 本节描述: 用于处理和创建JSON数据的函数和运算器 SQL/JSON路径语言 要了解有关SQL/JSON标准的更多信息，请参阅[sqltr-19075-6]。有关PostgreSQL中支持的JSON类型的详细信息，见 第8.14节。 . 9.15.1.处理和创建JSON数据 表9.44展示了可以用于 JSON 数据类型（见第8.14节）的...
PostGreSQL 结合 Hibernate 在项目中的使用小结
钱猛的专栏
09-19 6932
我本人是从事J2EE开发的，一提到J2EE开发，估计大家第一想到的就是SSH三层架构，当然现在已经不一定是这样的了，也有SSI，也有SpringMVC框架等等，其中ORM这个技术在开发中是肯定会用到的，毕竟是一种里程碑的技术，这里只介绍下Hibernate与PostGreSQL的结合使用。一、驱动包下载这里使用过Hibernate的人应该都知道，和数据库结合使用必须下载指定数据库的数据库驱动jar包
postgresql
hqlai1234的专栏
09-26 1594
异常处理 ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. 把select 改为PERFORM 例如： select test() 改为PERFORM test()
PostgreSQL DBA(67) - PG 12 SQLJSON path
08-01 157
PostgreSQL 12提供了SQL/JSON函数用以兼容SQL 2016 SQL/JSON特性. 这些函数包括: [loc...
PostgreSQL中JSONB的索引
热门推荐
万里归来少年心
10-12 1万+
    JSONB类型的列上可以直接建索引，支持BTree索引和GIN索引。其中，GIN索引可以高效的从JSONB内部的key/value对中搜索数据，因此JSONB类型上常用GIN索引。      JSONB上创建GIN索引的方式有两种：   （1）使用默认的jsonb_ops操作符创建。   （2）使用jsonb_path_ops操作符创建。     两者的区别是：   （1）jso...
spring data jpa hibernate 映射 postgres jsonb
偶是一只小小鸟~
03-17 5912
spring boot jpa 映射postgres 的jsonb 类型使用 jpa-hibernate 处理postgres 的jsonb类型 首先需要自定一个Dialect，然后注册到hibernate框架中 自定义一个Type用于java和数据库之间的mapping 在jsonb的字段上使用自定义的类型进行映射 具体的步骤 CustomPostgreSqlDialect.java public
如何使用PostgreSQL更新JSONB数组中的对象
cumian8165的博客
08-07 1092
by Leandro Cesquini Pereira 通过莱昂德罗·塞斯奎尼·佩雷拉 如何使用PostgreSQL更新JSONB数组中的对象 (How to update objects inside JSONB arrays with PostgreSQL) 如何更新JSONB数组上的特定值 (How to update a specific value on a JSONB array...
json 转对象函数_JSON_QUERY（）函数从JSON数据提取对象
culuo4781的博客
07-22 6242
json 转对象函数 In this article, we will explore JSON_QUERY() functions in SQL Server to extract JSON objects and array from the JSON Data. 在本文中，我们将探索SQL Server中的JSON_QUERY（）函数，以从JSON数据中提取JSON对...
PL/pgSQL编写postgresql函数（二）--基本语句
qq_39727113的博客
12-15 1万+
目录 基本语句 1 赋值 赋值运算符 := 或 = 2 单一行结果返回 SELECT...INTO 语法赋值 更新操作结果返回 3 多行结果返回 方式一：使用表充当容器 方式二：使用自定义TYPE充当容器 方式三：return query进行结果整体写入容器 方式四：return setof record返回 4 无结果执行 5 执行动态命令 2.1 语法 2.2示例准备 2.3 例子 6 打印输出语法 基本语句 1 赋值 赋值运算符 := 或 = 语法：varia
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值