Sql 知识积累2023.10月

Sql 知识积累2023.10月

Postgrel Sql中JSON

• Postgrel Version： 9.6
• 官方文档：http://postgres.cn/docs/9.6/functions-json.html#FUNCTIONS-JSON-CREATION-TABLE

JSON与JSONB

• 相同点
  • 都用来存储json数据
• 不同点
  • 存储方式不同：json是以文本格式进行存储的；jsonb是以二进制格式进行存储的。
  • 写入速度不同：json > jsonb (因为把json解析之后再进行存储，所以jsonb多了解析的过程)
  • 条件查询速度不同：json < jsonb (因为每次读取json都需要进行解析，多了解析的过程)
  • json格式上的不同：json原样进行存储，会保留空格等不影响含义的字符；jsonb会解析之后进行存储，所以不会保留空格等字符，且json中key不能重复，如重复保留最后一个值。
• 使用建议
  • 如果你的应用只是需要对json进行保存和读取，则建议使用json
  • 如果你的应用需要在PostgreSQL中做比较多的json值操作，或者在json字段上使用索引时，应该使用jsonb

---

Postgrel Sql中JSON操作

json和jsonb 操作符

操作符	右操作数类型	描述	例子	例子结果
->	int	获得 JSON 数组元素（索引从 0 开始，负整数结束）	'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2	{"c":"baz"}
->	text	通过键获得 JSON 对象域	'{"a": {"b":"foo"}}'::json->'a'	{"b":"foo"}
->>	int	以文本形式获得 JSON 数组元素	'[1,2,3]'::json->>2	3
->>	text	以文本形式获得 JSON 对象域	'{"a":1,"b":2}'::json->>'b'	2
#>	text[]	获取在指定路径的 JSON 对象	'{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'	{"c": "foo"}
#>>	text[]	以文本形式获取在指定路径的 JSON 对象	'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'	3

---

常用的JSON创建操作

JSON创建函数

to_json(anyelement) to_jsonb(anyelement)	把值返回为json或者jsonb。数组和组合被（递归地）转换成数组和对象；否则， 如果有从该类型到json的投影，将使用该投影函数来执行转换； 否则将产生一个标量值。对任何一个数值、布尔量或空值的标量类型， 将使用其文本表达，以这样一种方式使其成为有效的json或者jsonb值。	to_json('Fred said "Hi."'::text)	"Fred said \"Hi.\""

--- 查询数据转换为json格式
select to_json(epm.*) from event_process_main epm limit 5 

---

字符串函数和操作符：

函数	返回类型	描述	例子	结果
string || string	text	字串连接	‘Post’ || ‘greSQL’	PostgreSQL
substring(string [from int] [for int])	text	抽取子字串	substring(‘Thomas’ from 2 for 3)	hom
left(str, length)\right(str, length)	text	获取字符串左、右开始截取	select left(‘hello world’, 3)	hel