PostgreSQL処理JSON入門
26344 ワード
バックグラウンド環境
簡単で使いやすい非構造化データとしてJSON形式の応用シーンは非常に広い.現在のビッグデータ環境では、非構造化データの処理がますます頻繁になっていますが、MongoDBのようなNoSQLのソリューションを使用する必要がありますか?強力なPostgreSQLデータベースは、RDBMSに基づいてJSONの完全なサポートを提供し、MongoDBを必要とせずにJSONをプレイすることができます.
PostgreSQL-9.2はJSONタイプのサポートを導入し、いくつかの大きなバージョンの進化を経て、現在JSONデジタルタイプのサポートは比較的完備している.PGにおけるJSON形式情報のCRUD操作は,特定のノードに対してインデックスを確立することで容易に実現できる.
今回はPGでJSONを使用する一般的なシーンをテストします.ソフトウェア環境は以下の通りです.
CentOS 7 x64
PostgreSQL 11.1
2つのデータ型
PGには、JSONとJSOnbの2つの異なるデータ型が用意されている.名前の通りJSONは文字列を格納する元のフォーマットであり、JSOnbはバイナリ符号化バージョンである.JSONは、スペースなどの元のフォーマットを格納する必要があるため、クエリのたびに解析処理が行われます.JSOnbはリアルタイムで解析する必要がないので、より効率的です.
簡単に言えば、JSONは正確に記憶するために、速いクエリーを挿入するのが遅い.JSONBは効率的なクエリーのため、遅い検索を挿入するのが速い.
特別な理由がない場合はJSOnbタイプを使用した方が良いです.
データ照会
出力のフォーマット
オブジェクトメンバーの抽出
PGには、トップレベルのメンバーを抽出する->と、ネストされたメンバーを抽出する#>の2種類のクエリー構文があります.テキストの内容だけを取り出したい場合は、->>または#>>を使用します.
条件フィルタ
PGは特殊な存在判断記号を提供しています.この構文はis not nullと等価である.
?| と?&そうですか?の機能を拡張し、orとand操作に等価です.
キーの存在をチェックするほか、キー:valueを@>記号でチェックすることもできます.
データ更新
新規/マージ
削除
変更
jsonb_set()は、単一パスノード値を更新するように設計されている.パラメータの意味は次のとおりです.最初に修正するJSOnbデータ型フィールドです. の2番目は、変更のパスを指定するテキスト配列です. の3番目のパラメータは、置換する値(JSONであってもよい)である. 与えられたパスが存在しない場合、json_set()はデフォルトで作成されます.この動作を無効にするには、4番目のパラメータをfalseに設定します.
索引
PGに付属するginタイプインデックスは、範囲クエリー以外のすべてのJSON操作をサポートします.いくつかの例で説明します.
デフォルトモード
ginには2つの使用モードがあり、デフォルトではパラメータはありません.indexを作成するには、次のようにします.
指定されたKEYが存在するかどうかを判断しますか?次のように操作します.
指定キー:Valueが等しいかどうかを判断する@>操作は、次のようになります.
OR操作の値等しい判断
jsonb_path_opsモード
jsonb_付きpath_opsのginインデックスは、デフォルトよりも効率が高い.
実行計画を表示し、より効率的なインデックスidxが使用されていることを確認します.jc_ops
btreeインデックス-数値
ginインデックスは範囲クエリーをサポートしていないため、このようなニーズのあるフィールドを提案してbtreeインデックスを構築します.作成するときは、次のように明示的なタイプ変換を行う必要があります.
インデックスを使用する場合も、次のようにタイプ変換を実行する必要があります.
btreeインデックス-タイムスタンプ
重要:timestampタイプのbtreeインデックスを直接作成すると、デフォルトの文字列転送タイムスタンプ関数がIMMUTABLEプロパティを満たしていないため、エラーが次のように報告されます.
正しい方法は、次のようにIMMUTABLE関数を作成してタイプ変換することです.
SQLでもカスタム関数を使用してインデックスを使用する必要があります.
転載先:https://juejin.im/post/5ca9d4b36fb9a05e15301cab
簡単で使いやすい非構造化データとしてJSON形式の応用シーンは非常に広い.現在のビッグデータ環境では、非構造化データの処理がますます頻繁になっていますが、MongoDBのようなNoSQLのソリューションを使用する必要がありますか?強力なPostgreSQLデータベースは、RDBMSに基づいてJSONの完全なサポートを提供し、MongoDBを必要とせずにJSONをプレイすることができます.
PostgreSQL-9.2はJSONタイプのサポートを導入し、いくつかの大きなバージョンの進化を経て、現在JSONデジタルタイプのサポートは比較的完備している.PGにおけるJSON形式情報のCRUD操作は,特定のノードに対してインデックスを確立することで容易に実現できる.
今回はPGでJSONを使用する一般的なシーンをテストします.ソフトウェア環境は以下の通りです.
CentOS 7 x64
PostgreSQL 11.1
2つのデータ型
PGには、JSONとJSOnbの2つの異なるデータ型が用意されている.名前の通りJSONは文字列を格納する元のフォーマットであり、JSOnbはバイナリ符号化バージョンである.JSONは、スペースなどの元のフォーマットを格納する必要があるため、クエリのたびに解析処理が行われます.JSOnbはリアルタイムで解析する必要がないので、より効率的です.
簡単に言えば、JSONは正確に記憶するために、速いクエリーを挿入するのが遅い.JSONBは効率的なクエリーのため、遅い検索を挿入するのが速い.
特別な理由がない場合はJSOnbタイプを使用した方が良いです.
-- JSONB （ JSON ）
drop table if exists demo.j_waybill;
create table demo.j_waybill (id int primary key, data jsonb);
insert into demo.j_waybill(id, data) values(1,
'
{
"waybill": 2019000000,
"project": " ",
"pay_org_name": "ABC ",
"driver": {
"name": " ",
"mobile": 13800000000
},
"line": {
"from": {"province":" ", "city":" ", "district":" "},
"to": {"province":" ", "city":" ", "district":" "}
},
"payment": {
"oil_amount": 1234,
"cash_amount": 5678
}
}
'
);
データ照会
出力のフォーマット
-- jsonb_pretty() ， JSON
select jsonb_pretty(w.data) from demo.j_waybill w where w.id = 1;
jsonb_pretty
-----------------------------------
{ +
"line": { +
"to": { +
"city": " ", +
"district": " ",+
"province": " " +
}, +
"from": { +
"city": " ", +
"district": " ",+
"province": " " +
} +
}, +
"driver": { +
"name": " ", +
"mobile": 13800000000 +
}, +
"payment": { +
"oil_amount": 1234, +
"cash_amount": 5678 +
}, +
"project": " ", +
"waybill": 2019000000, +
"pay_org_name": "ABC " +
}
(1 row)
オブジェクトメンバーの抽出
PGには、トップレベルのメンバーを抽出する->と、ネストされたメンバーを抽出する#>の2種類のクエリー構文があります.テキストの内容だけを取り出したい場合は、->>または#>>を使用します.
-- ， -> ->> ，
select
w.data->'waybill' as waybill,
w.data->'project' as project,
w.data->>'project' as project_text
from demo.j_waybill w where w.id = 1;
waybill | project | project_text
------------+------------+--------------
2019000000 | " " |
(1 row)
-- ， #> #>>
select
w.data#>'{driver}' as driver,
w.data#>>'{driver, name}' as driver_name,
w.data#>'{driver, mobile}' as mobile
from demo.j_waybill w where w.id = 1;
driver | driver_name | mobile
-----------------------------------------+-------------+-------------
{"name": " ", "mobile": 13800000000} | | 13800000000
(1 row)
条件フィルタ
PGは特殊な存在判断記号を提供しています.この構文はis not nullと等価である.
-- key
select count(1) from demo.j_waybill w where w.data ? 'waybill';
count
-------
1
(1 row)
--
select count(1) from demo.j_waybill w where w.data->'waybill' is not null ;
-- key
select count(1) from demo.j_waybill w where w.data->'driver' ? 'mobile';
count
-------
1
(1 row)
?| と?&そうですか?の機能を拡張し、orとand操作に等価です.
-- ?| or， ?& and
select count(1) from demo.j_waybill w where w.data->'driver' ?| '{"mobile", "addr"}';
キーの存在をチェックするほか、キー:valueを@>記号でチェックすることもできます.
-- ? key ， @>
select count(1) from demo.j_waybill w where w.data @> '{"waybill":2019000000, "project":" "}';
count
-------
1
(1 row)
--
-- PS： to_jsonb()， ->>
select count(1) from demo.j_waybill w
where w.data->'waybill' = to_jsonb(2019000000)
and w.data->>'project' = ' ' ;
--
select count(1) from demo.j_waybill w
where (w.data->'waybill')::numeric = 2019000000
and w.data->>'project' = ' ' ;
データ更新
新規/マージ
-- || ，
select
jsonb_pretty(w.data#>'{line}' || '{"new_line":" "}') as new_line,
jsonb_pretty(w.data || '{"new_key":" "}') as new_key
from demo.j_waybill w where w.id = 1;
new_line | new_key
-------------------------------+-----------------------------------
{ +| { +
"to": { +| "line": { +
"city": " ", +| "to": { +
"district": " ",+| "city": " ", +
"province": " " +| "district": " ",+
}, +| "province": " " +
"from": { +| }, +
"city": " ", +| "from": { +
"district": " ",+| "city": " ", +
"province": " " +| "district": " ",+
}, +| "province": " " +
"new_line": " " +| } +
} | }, +
| "driver": { +
| "name": " ", +
| "mobile": 13800000000 +
| }, +
| "new_key": " ", +
| "payment": { +
| "oil_amount": 1234, +
| "cash_amount": 5678 +
| }, +
| "project": " ", +
| "waybill": 2019000000, +
| "pay_org_name": "ABC " +
| }
(1 row)
-- update
update demo.j_waybill
set data = data || '{"new_key":" "}' ;
削除
--
update demo.j_waybill
set data = data-'driver' ;
--
update demo.j_waybill
set data = data#-'{driver, mobile}' ;
--
update demo.j_waybill
set data = data#-'{driver, mobile}'#-'{line, to}' ;
変更
jsonb_set()は、単一パスノード値を更新するように設計されている.パラメータの意味は次のとおりです.
-- ，
update demo.j_waybill set data = jsonb_set(data, '{"project"}', '" "' );
-- ， to_jsonb()
update demo.j_waybill set data = jsonb_set(data, '{"waybill"}', to_jsonb(100) );
--
update demo.j_waybill set data = jsonb_set(data, '{"new_simple"}', to_jsonb(999) );
--
update demo.j_waybill set data = jsonb_set(data, '{"new_complex"}', '{"foo":"bar", "foo1": 123}');
索引
PGに付属するginタイプインデックスは、範囲クエリー以外のすべてのJSON操作をサポートします.いくつかの例で説明します.
--
drop table if exists demo.j_cargo;
create table demo.j_cargo (id int primary key, data jsonb);
insert into demo.j_cargo(id, data)
select v.waybill_id, to_jsonb(v)
from (
select b.waybill_create_time, c.*
from dwd_lhb.wb_cargo_info as c, dwd_lhb.wb_base_info as b
where c.waybill_id = b.waybill_id
limit 100000
) as v
;
デフォルトモード
ginには2つの使用モードがあり、デフォルトではパラメータはありません.indexを作成するには、次のようにします.
--
drop index if exists idx_jc_non_ops ;
create index idx_jc_non_ops on demo.j_cargo using gin (data);
指定されたKEYが存在するかどうかを判断しますか?次のように操作します.
--
explain select * from demo.j_cargo j where j.data ? 'cargo_name';
QUERY PLAN
--------------------------------------------------------------------------------
Bitmap Heap Scan on j_cargo j (cost=16.77..389.25 rows=100 width=803)
Recheck Cond: (data ? 'cargo_name'::text)
-> Bitmap Index Scan on idx_jc_non_ops (cost=0.00..16.75 rows=100 width=0)
Index Cond: (data ? 'cargo_name'::text)
(4 rows)
指定キー:Valueが等しいかどうかを判断する@>操作は、次のようになります.
-- ，
explain select * from demo.j_cargo j where j.data @> '{"cargo_name":" "}' ;
QUERY PLAN
--------------------------------------------------------------------------------
Bitmap Heap Scan on j_cargo j (cost=28.77..401.25 rows=100 width=803)
Recheck Cond: (data @> '{"cargo_name": " "}'::jsonb)
-> Bitmap Index Scan on idx_jc_non_ops (cost=0.00..28.75 rows=100 width=0)
Index Cond: (data @> '{"cargo_name": " "}'::jsonb)
(4 rows)
OR操作の値等しい判断
-- PS： or
explain select * from demo.j_cargo j where j.data @> '{"cargo_name":" "}' or j.data @> '{"cargo_name":" "}';
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on j_cargo j (cost=57.60..775.81 rows=200 width=803)
Recheck Cond: ((data @> '{"cargo_name": " "}'::jsonb) OR (data @> '{"cargo_name": " "}'::jsonb))
-> BitmapOr (cost=57.60..57.60 rows=200 width=0)
-> Bitmap Index Scan on idx_jc_non_ops (cost=0.00..28.75 rows=100 width=0)
Index Cond: (data @> '{"cargo_name": " "}'::jsonb)
-> Bitmap Index Scan on idx_jc_non_ops (cost=0.00..28.75 rows=100 width=0)
Index Cond: (data @> '{"cargo_name": " "}'::jsonb)
(7 rows)
jsonb_path_opsモード
jsonb_付きpath_opsのginインデックスは、デフォルトよりも効率が高い.
-- jsonb_path_ops @> ，
drop index if exists idx_jc_ops ;
create index idx_jc_ops on demo.j_cargo using gin (data jsonb_path_ops);
実行計画を表示し、より効率的なインデックスidxが使用されていることを確認します.jc_ops
explain select * from demo.j_cargo j where j.data @> '{"cargo_name":" "}' ;
QUERY PLAN
----------------------------------------------------------------------------
Bitmap Heap Scan on j_cargo j (cost=16.77..389.25 rows=100 width=803)
Recheck Cond: (data @> '{"cargo_name": " "}'::jsonb)
-> Bitmap Index Scan on idx_jc_ops (cost=0.00..16.75 rows=100 width=0)
Index Cond: (data @> '{"cargo_name": " "}'::jsonb)
(4 rows)
btreeインデックス-数値
ginインデックスは範囲クエリーをサポートしていないため、このようなニーズのあるフィールドを提案してbtreeインデックスを構築します.作成するときは、次のように明示的なタイプ変換を行う必要があります.
-- ， ， btree
drop index if exists idx_jc_btree_num ;
create index idx_jc_btree_num on demo.j_cargo ( ((data->>'price')::numeric) );
インデックスを使用する場合も、次のようにタイプ変換を実行する必要があります.
explain select * from demo.j_cargo j where (j.data->>'price')::numeric between 10 and 100;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on j_cargo j (cost=13.42..1673.22 rows=500 width=803)
Recheck Cond: ((((data ->> 'price'::text))::numeric >= '10'::numeric) AND (((data ->> 'price'::text))::numeric <= '100'::numeric))
-> Bitmap Index Scan on idx_jc_btree_num (cost=0.00..13.29 rows=500 width=0)
Index Cond: ((((data ->> 'price'::text))::numeric >= '10'::numeric) AND (((data ->> 'price'::text))::numeric <= '100'::numeric))
(4 rows)
btreeインデックス-タイムスタンプ
重要:timestampタイプのbtreeインデックスを直接作成すると、デフォルトの文字列転送タイムスタンプ関数がIMMUTABLEプロパティを満たしていないため、エラーが次のように報告されます.
-- Timestamp ！！！ immutable
create index idx_jc_btree_ts on demo.j_cargo ( ((data->>'waybill_create_time')::timestamp) );
ERROR: functions in index expression must be marked IMMUTABLE
正しい方法は、次のようにIMMUTABLE関数を作成してタイプ変換することです.
-- immutable
drop function if exists demo.to_timestamp ;
create or replace function demo.to_timestamp(text) returns timestamp as $$
select $1::timestamp;
$$ language sql strict immutable;
--
drop index if exists idx_jc_btree_ts ;
create index idx_jc_btree_ts on demo.j_cargo ( demo.to_timestamp(data->>'waybill_create_time') );
SQLでもカスタム関数を使用してインデックスを使用する必要があります.
--
explain select * from demo.j_cargo j where demo.to_timestamp(j.data->>'waybill_create_time') between '2015-06-27' and '2015-06-28';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------
Bitmap Heap Scan on j_cargo j (cost=13.42..1918.22 rows=500 width=803)
Recheck Cond: ((demo.to_timestamp((data ->> 'waybill_create_time'::text)) >= '2015-06-27 00:00:00'::timestamp without time zone) AND (demo.to_timestamp((data ->> 'waybill_create_time'::text)) <= '201
5-06-28 00:00:00'::timestamp without time zone))
-> Bitmap Index Scan on idx_jc_btree_ts (cost=0.00..13.29 rows=500 width=0)
Index Cond: ((demo.to_timestamp((data ->> 'waybill_create_time'::text)) >= '2015-06-27 00:00:00'::timestamp without time zone) AND (demo.to_timestamp((data ->> 'waybill_create_time'::text)) <=
'2015-06-28 00:00:00'::timestamp without time zone))
(4 rows)
-- ， filter
explain select * from demo.j_cargo j where (j.data->>'waybill_create_time')::timestamp between '2015-06-27' and '2015-06-28';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------
Gather (cost=1000.00..13167.00 rows=500 width=803)
Workers Planned: 2
-> Parallel Seq Scan on j_cargo j (cost=0.00..12117.00 rows=208 width=803)
Filter: ((((data ->> 'waybill_create_time'::text))::timestamp without time zone >= '2015-06-27 00:00:00'::timestamp without time zone) AND (((data ->> 'waybill_create_time'::text))::timestamp w
ithout time zone <= '2015-06-28 00:00:00'::timestamp without time zone))
(4 rows)
転載先:https://juejin.im/post/5ca9d4b36fb9a05e15301cab