pg操作array，json

最新推荐文章于 2026-04-08 12:56:12 发布

原创最新推荐文章于 2026-04-08 12:56:12 发布 · 2k 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#jsonb_to_recordset #unnest

pgsql 专栏收录该内容

3 篇文章

订阅专栏

本文通过一系列 PostgreSQL SQL 语句示例介绍了如何使用 JSONB 数据类型进行数据存储、查询及解析。包括创建包含 JSONB 字段的表、插入 JSON 对象、使用 JSON 函数和操作符来检索和更新 JSONB 数据等。

CREATE TABLE glj
(
  sip jsonb
)
insert into glj(sip) values ('[{"content":"1.1.1.1","type":"ip"},{"content":"1.1.1.2","type":"ip"},{"content":"www.baidu.com","type":"url"}]')
select type, count(0) from glj, jsonb_to_recordset(glj.sip) as x(content text, type text) group by type;
select sip->2->'content' from glj;

CREATE TABLE glj
(
  sip text[]
)
insert into glj(sip) values ('{"1.1.1.1",null}')
select * from glj where sip && '{"1.1.1.1"}'
select sip, count(*) from (select unnest(sip) as sip from glj) as a where sip is not null group by sip order by count desc limit 5

SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

create table testjson(
  name VARCHAR,
  glj jsonb
)
INSERT INTO "public"."testjson" ("name", "glj") VALUES ('a', '{"年龄": 1, "性别": "男", "分流量": {"k": "1.1.1.1", "v": 1}}');
INSERT INTO "public"."testjson" ("name", "glj") VALUES ('b', '{"年龄": 2, "性别": "男", "分流量": {"k": "1.1.1.2", "v": 2}}');


select glj->>'性别' as sex,jsonb_to_record() as x(a text, b int),name from testjson
select "unnest"(sip),"unnest"(src_occurrence) from xxx where nsuid='befb8bd314064d34a37960a031ef7b0e'
select * from (select * from testjson,jsonb_to_record(testjson.glj)  as x(年龄 int, 性别 text,分流量 jsonb)) t

select k,sum(v) from (select 分流量 from testjson,jsonb_to_record(testjson.glj)  as x(分流量 jsonb)) t,jsonb_to_record(t.分流量) as x(k text, v int) group by k