pg sql

http://mysql.taobao.org/monthly/2017/11/10/
实践



https://blog.csdn.net/cdnight/article/details/34847689
-- @block select json array
-- @conn PGSQL
select '[1, 2, "foo", null]'::json;

-- @block select json object
select '{"bar": "baz", "balance": 7.77, "active": false}'::json;

-- @block select json object complex
select '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

-------------- some query operations ------------

-- @block return  json array index 0
select '[1, 2, "foo", null]'::json->0;

-- @block return json object
select '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json->'tags' as tags;

-- @block return text
select '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json->>'tags' as tags;

-- @block path select: will return "bar"
select '{"a": [1, "bar", 2], "tags": {"a": 1, "b": null}}'::json #> '{a,1}';

-------------- jsonb ------------

-- json type will save as document, but jsonb has only useful value for higher performance (GIN index)
-- @block use jsonb
select '{"nickname": "goodspeed", "avatar": "avatar_url", "tags":["python", "golang", "db"]}'::jsonb->'nickname' as nickname;

-------------- jsonb specially supported query --------------
-- @block contain value
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb @> '{"nickname": "gs"}'::jsonb;
--select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->'nickname' = '"gs"';

-- @block include in
select '{"nickname": "gs"}'::jsonb <@ '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb;

-- @block if contain in top keys
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb ? 'avatar'

-- @block if contain in top keys: ?& all match, ?| any match
select '{"nickname": "gs", "tags": ["python", "golang", "db"]}'::jsonb ?& array['avatar', 'tags'];

-- @block map row to json object
select row_to_json(company) from public.company;

-- @block aggregate many rowsto one json array 
select array_to_json(array_agg(row_to_json(company))) from public.company;



--------------  update --------------

--jsonb_set(target         jsonb,  // data to update
--          path           text[], // data path
--          new_value      jsonb,  // new value
--          create_missing boolean default true)


-- @block update override value
update accounts
set content = jsonb_set(content, '{}', '{"nickname": "gs", "tags": ["python", "golang", "db"]}', false)

-- @block update
update account
set content = jsonb_set(content, '{tags}', '["test", "心理"]', true)
where content @> '{"nickname": "nickname"}'::jsonb;

-- @block create schema
create schema test;

-- @block ddl
-- @conn PGSQL
create table test.accounts_jsonb (
  id serial primary key,
  content jsonb not null,
  created_at timestamp(6) default current_timestamp,
  updated_at timestamp(6) default current_timestamp
);

---- traditional table -------------

-- @block ddl
-- @conn PGSQL
create table test.accounts (
  id serial primary key,
  username varchar(20) not null,
  nickname varchar(20),
  avatar varchar(20),
  created_at timestamp(6) default current_timestamp,
  updated_at timestamp(6) default current_timestamp
);

-- @block ddl
-- @conn PGSQL
select * from test.accounts

-- @block ddl
-- @conn PGSQL
create table test.tags (
  id serial primary key,
  name varchar(20) NOT NULL
);

-- @block ddl
-- @conn PGSQL
create table test.account_tags(
  id serial primary key,
  aid integer not null,
  tid integer NOT NULL,
  constraint fk_aid foreign key (aid) references accounts(id),
  constraint fk_tid foreign key (tid) references tags(id)
);

--------------insert data -----------------------

-- @block insert accounts
-- @conn PGSQL
insert into test.accounts(username, nickname, avatar)
values
('tim','haha','/url/tim_a'),
('kris','cheer','/url/kris_a'),
('cow','cowww','/url/cow_a')

-- @block insert tags
-- @conn PGSQL
insert into tags(name)
values
('python'),
('java'),
('scala'),
('golang'),
('kotlin')

-- @block insert 
-- @conn PGSQL
insert into account_tags(aid, tid)
values (1, 1), (1, 2), (2, 2), (2, 3), (2, 5), (3, 2)


-- @block query tags name for given account Id
select name from test.tags as T where T.id in (select tid from test.account_tags as AT where AT.aid = 1)


-- @block transform data to json object and insert into table
-- @conn PGSQL
insert into accounts_jsonb (content)
select row_to_json(R)::jsonb
from
(select id, username, nickname,
(select array_to_json(array_agg(name)) from tags as T where T.id in (select tid from account_tags as AT where AT.aid = A.id)) as tags
from accounts as A) as R

-- @block contain nickname = 'cowww'
-- @conn PGSQL
select content from test.accounts_jsonb where content @> '{"nickname" : "cowww"}'

-- @block select content tags contain 'scala'
-- @conn PGSQL
select content from test.accounts_jsonb where content::jsonb->'tags' ? 'scala'

-- @block select accounts and tags info
-- @conn PGSQL
select A.id, username, nickname, T.name
from test.accounts as A
JOIN test.account_tags as AT on A.id = AT.aid
JOIN test.tags as T on AT.tid = T.id
-- where A.id = 1