http://mysql.taobao.org/monthly/2017/11/10/
實(shí)踐
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
