PostgreSQL JSON查询_postgresql 查询json_eff666的博客

相关文章推荐

坚强的猴子 · PostgreSQL遍历Json_posgr ...· 6 天前 ·

跑龙套的皮蛋 · PostgreSQL遍历Record各例的值 ...· 6 天前 ·

愤怒的西瓜 · cuda an illegal ...· 4 月前 ·

豪气的冰淇淋 · java.lang.NoSuchFieldE ...· 1 年前 ·

博学的高山 · mysql的update底层一定是先删除再插 ...· 1 年前 ·

气宇轩昂的鼠标垫 · 内蒙古自治区各乡镇shp文件.rar_乡镇s ...· 1 年前 ·

俊逸的西瓜 · c - MinGW32-make path ...· 1 年前 ·

JSON 代表 JavaScript Object Notation。JSON是开放的标准格式，由key-value对组成。JSON的主要用于在服务器与web应用之间传输数据。与其他格式不同，JSON是人类可读的文本格式。

PostgreSQL从9.2版本开始支持JSON数据类型，并提供很多函数和操作符维护json数据。下面我们创建一张新表用于说明json数据类型：

CREATE TABLE orders (
   ID serial NOT NULL PRIMARY KEY,
   info json NOT NULL
orders表包括两列：
 id是主键，为自增列
 info列存储json数据 
1.插入json数据
 
要插入json数据，需确保数据是格式规范的json。下面insert语句插入一行order数据： 
INSERT INTO orders (info)
VALUES
      '{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}'
下面同时插入多行。 
INSERT INTO orders (info)
VALUES
      '{ "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}'
      '{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}'
      '{ "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}'
2.查询json数据
 
使用select语句查询json数据，与其他基本数据类型类似： 
SELECT
   orders;
PostgreSQL返回一组JSON数据。PostgreSQL 默认提供了两个操作符用于查询json数据： 
The operator -> 按键返回 JSON 对象字段.
The operator ->> 按文本返回JSON对象字段.
下面查询使用->操作符，查询json中所有顾客作为键: 
SELECT
   info -> 'customer' AS customer
   orders;
下面使用->>操作获取所有顾客作为文本： 
SELECT
   info ->> 'customer' AS customer
   orders;
->操作返回json对象，我们可以链式方式继续使用->>返回特定节点。下面语句返回所有购买的商品： 
SELECT
   info -> 'items' ->> 'product' as product
   orders
ORDER BY
   product;
首先使用info->'item’返回json对象。然后使用info->‘item’->>'product’返回所有产品文本值。 
3.在where子句中使用json操作符
 
我们能在where子句中使用json操作符过滤数据行。举例，查找买了Diaper的记录： 
SELECT
   info ->> 'customer' AS customer
   orders
WHERE
   info -> 'items' ->> 'product' = 'Diaper'
返回结果： 
customer
Lily Bush
下面查询谁一次买了2个商品，语句如下： 
SELECT
   info ->> 'customer' AS customer,
   info -> 'items' ->> 'product' AS product
   orders
WHERE
   CAST (
      info -> 'items' ->> 'qty' AS INTEGER
   ) = 2
结果如下： 
customer	product
Mary Clark	Toy Train
注意我们使用cast转换qty字段值为integer类型，然后和2进行比较。 
4.json 数据的聚集函数
 
我们能对json数据使用聚集函数，如min,max,average,sum等。举例，下面语句返回最小数量，最大数量、平均数量以及总数量。 
SELECT
   MIN (
      CAST (
         info -> 'items' ->> 'qty' AS INTEGER
   MAX (
      CAST (
         info -> 'items' ->> 'qty' AS INTEGER
   SUM (
      CAST (
         info -> 'items' ->> 'qty' AS INTEGER
   AVG (
      CAST (
         info -> 'items' ->> 'qty' AS INTEGER
   orders
min	max	sum	avg
1	2	24	8.25
5.PostgreSQL JSON 函数
 
json_each()函数的作用是:将最外层的JSON对象展开为一组键值对。举例： 
SELECT
   json_each (info)
   orders;
如果想得到一组key-value对作为文本，可以使用json_each_text()函数。
 json_object_keys()函数可以获得json对象最外层的一组键。举例： 
SELECT
   json_object_keys (info->'items')
   orders;
json_typeof函数返回json最外层key的数据类型作为字符串。可能是number, boolean, null, object, array, string。下面语句查询所有item的数据类型： 
SELECT
   json_typeof (info->'items')
   orders;
返回结果为： 
json_typeof
object
object
object
object
下面语句返回嵌套类型中qty字段的数据类型： 
SELECT
   json_typeof (info->'items'->'qty')
   orders;
返回结果： 
json_typeof
number
number
number
number
                    JSON 代表 JavaScript Object Notation。JSON是开放的标准格式，由key-value对组成。JSON的主要用于在服务器与web应用之间传输数据。与其他格式不同，JSON是人类可读的文本格式。PostgreSQL从9.2版本开始支持JSON数据类型，并提供很多函数和操作符维护json数据。下面我们创建一张新表用于说明json数据类型：CREATE TABLE or...
				Postgres-JSON
它是什么？
 一个 Common Lisp 库，它在 PostgreSQL 9.4 的新上提供了一个用户友好的层，允许对 JSON 文档进行简单的存储和检索。 多亏了 Common Lisp 的优秀 JSON 库，Postgres-JSON 从而促进了 lisp 数据结构与适当数据库之间的轻松序列化。
你为什么要使用它？
 您有一些要持久存储的现有 JSON 文档。
 您想将 Common Lisp 哈希表、列表、向量等序列化到数据库中。
 您喜欢 PostgreSQL 的品质，但严格的数据模式不适合您的项目。
 在某种意义上，Postgres-JSON 是一个原始的NoSQL 文档数据库。 它是由激发优良接口但是使用传统的阻塞I / O接口，通过后现代。
 Marijn Haverbeke 的精彩。
 任何 Common Lisp JSON 库。
将json存在字段snapshot中
snapshot::json->>'key1‘,是将snapshot文本看作json取出key1值
json_array_elements是查询一个json数组
--weight   
SELECT  json_array_elements(t2.key2Value::json)->'weight' weig
				有时候我们需要使用PostgreSQL这种结构化数组来存储一些非结构化数据，PostgreSQL恰好又提供了json这种数据类型，这里我们来简单介绍使用jsonb的一些常见操作。
PostgreSQL 提供了 json和 jsonb两种 json类型，两者的主要区别就是，json查询相对慢一些，插入会快一点，而jsonb则相反，查询效率会高一点，插入会慢一点。
下面进入我们今天的正题
create table purchase_order
1. json 和jsonb 区别
两者从用户操作的角度来说没有区别，区别主要是存储和读取的系统处理（预处理）和耗时方面有区别。json写入快，读取慢，jsonb写入慢，读取快。
2. 常用的操作符
 // -> 返回json
 select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 // 输出 {"c":"baz"}
 select '{"a": {"b":"foo"}, "c":{"a": "aaa"}}'::json->'a' //
	scene_actor_template_typhoon_record
	CROSS JOIN LATERAL jsonb_array_elements ( points -> 'points' ) AS e
WHERE
	points :: jsonb ->> 'name' = '雷伊' 
	AND e :: jsonb ->> 'time' > '2021-12-13T14:00:00'
				PostgreSQL和MySQL是两种常用的关系型数据库管理系统（RDBMS）。下面是一些区别：
- 开源协议：PostgreSQL使用PostgreSQL许可证，而MySQL使用GPL授权。
- 语言支持：PostgreSQL支持更多的SQL标准，并且还支持触发器、视图、外键约束等功能。MySQL相对较弱，但是它支持存储过程和触发器。
- 数据类型：PostgreSQL支持更多的数据类型，包括数组、JSON、XML等。MySQL相对较弱。
- 并发控制：PostgreSQL使用MVCC（多版本并发控制）来处理并发事务，而MySQL使用行级锁定。
- 事务支持：PostgreSQL支持事务和事务日志，而MySQL不支持事务日志。
- 性能：在大多数情况下，MySQL的性能略优于PostgreSQL。
总的来说，PostgreSQL更适合大型应用，因为它支持更多的功能和标准，并且有更好的事务支持。MySQL更适合中小型应用，因为它的性能优于PostgreSQL。