👌

AQLでCRUDおよび集計

2021/09/24に公開

ArangoDB

tech

AQLでデータの作成、読み出し、更新、削除をする方法…これさえわかれば大体できる…と思う

※コレクションはtestを使います＆WebUIのQUERIESを使います

CRUDの前に覚えておくべきこと

FOR

繰り返し処理です

INSERT(データの追加)

// 1から100まで
FOR i IN 1..100
  // データ作成処理(INSERT文)
  INSERT { id: i, value: i * 100 } INTO test

※結果は特にないです([]が帰ってくる)

SELECT

// testコレクションのデータをすべて回す
FOR t IN test
  // 10で割り切れるデータだけにする
  FILTER t.id % 10 == 0
  // t.idを降順でソート
  SORT t.id DESC
  // tを返す
  RETURN t

結果

_key	_id	_rev	id	value
70237	test/70237	_d_U2L9e--F	100	10000
70227	test/70227	_d_U2L9a-_X	90	9000
70217	test/70217	_d_U2L9a-_N	80	8000
70207	test/70207	_d_U2L9a-_D	70	7000
70197	test/70197	_d_U2L9a--5	60	6000
70187	test/70187	_d_U2L9a--v	50	5000
70177	test/70177	_d_U2L9a--l	40	4000
70167	test/70167	_d_U2L9a--b	30	3000
70157	test/70157	_d_U2L9a--R	20	2000
70147	test/70147	_d_U2L9a--H	10	1000

UPDATE

// testコレクションのデータをすべて回す
FOR t IN test
  FILTER
    t.id == 100
  // UPDATE文
  UPDATE t WITH { value: t.value + 1 } IN test

※結果は特にないです([]が帰ってくる)

確認

// testコレクションのデータをすべて回す
FOR t IN test
  FILTER
    t.id == 100
  RETURN t

結果

_key	_id	_rev	id	value
70237	test/70237	_d_VEdDC---	100	10001

REMOVE(DELETE)

// testコレクションのデータをすべて回す
FOR t IN test
  FILTER
    t.id == 100
  // REMOVE文
  REMOVE t IN test

※結果は特にないです([]が帰ってくる)

確認

// testコレクションのデータをすべて回す
FOR t IN test
  FILTER
    t.id == 100
  RETURN t

結果

[]

COLLECT(GROUP 集計)

// testコレクションのデータをすべて回す
FOR t IN test
  FILTER
    // 50未満のデータを対象にする
    t.id < 50
  // 集計(FLOOR(t.id / 10)の結果が同じものでグループ化＆結果はfに代入、sにグループ化されたデータの合計を代入、gに対象となったデータを入れる
  COLLECT f = FLOOR(t.id / 10) AGGREGATE s = SUM(t.value) INTO g
  // データを返す
  RETURN {
    f: f,
    sum: s,
    g: g
  }

結果

f	sum	g
0	4500	[{"t":{"_key":"70138","_id":"test/70138","_rev":"_d_U2L9a---","id":1,"value":100}},{"t":{"_key":"70139","_id":"test/70139","_rev":"_d_U2L9a--_","id":2,"value":200}},{"t":{"_key":"70140","_id":"test/70140","_rev":"_d_U2L9a--A","id":3,"value":300}},{"t":{"_key":"70141","_id":"test/70141","_rev":"_d_U2L9a--B","id":4,"value":400}},{"t":{"_key":"70142","_id":"test/70142","_rev":"_d_U2L9a--C","id":5,"value":500}},{"t":{"_key":"70143","_id":"test/70143","_rev":"_d_U2L9a--D","id":6,"value":600}},{"t":{"_key":"70144","_id":"test/70144","_rev":"_d_U2L9a--E","id":7,"value":700}},{"t":{"_key":"70145","_id":"test/70145","_rev":"_d_U2L9a--F","id":8,"value":800}},{"t":{"_key":"70146","_id":"test/70146","_rev":"_d_U2L9a--G","id":9,"value":900}}]
1	14500	[{"t":{"_key":"70147","_id":"test/70147","_rev":"_d_U2L9a--H","id":10,"value":1000}},{"t":{"_key":"70148","_id":"test/70148","_rev":"_d_U2L9a--I","id":11,"value":1100}},{"t":{"_key":"70149","_id":"test/70149","_rev":"_d_U2L9a--J","id":12,"value":1200}},{"t":{"_key":"70150","_id":"test/70150","_rev":"_d_U2L9a--K","id":13,"value":1300}},{"t":{"_key":"70151","_id":"test/70151","_rev":"_d_U2L9a--L","id":14,"value":1400}},{"t":{"_key":"70152","_id":"test/70152","_rev":"_d_U2L9a--M","id":15,"value":1500}},{"t":{"_key":"70153","_id":"test/70153","_rev":"_d_U2L9a--N","id":16,"value":1600}},{"t":{"_key":"70154","_id":"test/70154","_rev":"_d_U2L9a--O","id":17,"value":1700}},{"t":{"_key":"70155","_id":"test/70155","_rev":"_d_U2L9a--P","id":18,"value":1800}},{"t":{"_key":"70156","_id":"test/70156","_rev":"_d_U2L9a--Q","id":19,"value":1900}}]
2	24500	[{"t":{"_key":"70157","_id":"test/70157","_rev":"_d_U2L9a--R","id":20,"value":2000}},{"t":{"_key":"70158","_id":"test/70158","_rev":"_d_U2L9a--S","id":21,"value":2100}},{"t":{"_key":"70159","_id":"test/70159","_rev":"_d_U2L9a--T","id":22,"value":2200}},{"t":{"_key":"70160","_id":"test/70160","_rev":"_d_U2L9a--U","id":23,"value":2300}},{"t":{"_key":"70161","_id":"test/70161","_rev":"_d_U2L9a--V","id":24,"value":2400}},{"t":{"_key":"70162","_id":"test/70162","_rev":"_d_U2L9a--W","id":25,"value":2500}},{"t":{"_key":"70163","_id":"test/70163","_rev":"_d_U2L9a--X","id":26,"value":2600}},{"t":{"_key":"70164","_id":"test/70164","_rev":"_d_U2L9a--Y","id":27,"value":2700}},{"t":{"_key":"70165","_id":"test/70165","_rev":"_d_U2L9a--Z","id":28,"value":2800}},{"t":{"_key":"70166","_id":"test/70166","_rev":"_d_U2L9a--a","id":29,"value":2900}}]
3	34500	[{"t":{"_key":"70167","_id":"test/70167","_rev":"_d_U2L9a--b","id":30,"value":3000}},{"t":{"_key":"70168","_id":"test/70168","_rev":"_d_U2L9a--c","id":31,"value":3100}},{"t":{"_key":"70169","_id":"test/70169","_rev":"_d_U2L9a--d","id":32,"value":3200}},{"t":{"_key":"70170","_id":"test/70170","_rev":"_d_U2L9a--e","id":33,"value":3300}},{"t":{"_key":"70171","_id":"test/70171","_rev":"_d_U2L9a--f","id":34,"value":3400}},{"t":{"_key":"70172","_id":"test/70172","_rev":"_d_U2L9a--g","id":35,"value":3500}},{"t":{"_key":"70173","_id":"test/70173","_rev":"_d_U2L9a--h","id":36,"value":3600}},{"t":{"_key":"70174","_id":"test/70174","_rev":"_d_U2L9a--i","id":37,"value":3700}},{"t":{"_key":"70175","_id":"test/70175","_rev":"_d_U2L9a--j","id":38,"value":3800}},{"t":{"_key":"70176","_id":"test/70176","_rev":"_d_U2L9a--k","id":39,"value":3900}}]
4	44500	[{"t":{"_key":"70177","_id":"test/70177","_rev":"_d_U2L9a--l","id":40,"value":4000}},{"t":{"_key":"70178","_id":"test/70178","_rev":"_d_U2L9a--m","id":41,"value":4100}},{"t":{"_key":"70179","_id":"test/70179","_rev":"_d_U2L9a--n","id":42,"value":4200}},{"t":{"_key":"70180","_id":"test/70180","_rev":"_d_U2L9a--o","id":43,"value":4300}},{"t":{"_key":"70181","_id":"test/70181","_rev":"_d_U2L9a--p","id":44,"value":4400}},{"t":{"_key":"70182","_id":"test/70182","_rev":"_d_U2L9a--q","id":45,"value":4500}},{"t":{"_key":"70183","_id":"test/70183","_rev":"_d_U2L9a--r","id":46,"value":4600}},{"t":{"_key":"70184","_id":"test/70184","_rev":"_d_U2L9a--s","id":47,"value":4700}},{"t":{"_key":"70185","_id":"test/70185","_rev":"_d_U2L9a--t","id":48,"value":4800}},{"t":{"_key":"70186","_id":"test/70186","_rev":"_d_U2L9a--u","id":49,"value":4900}}]

CRUDの前に覚えておくべきこと

FOR

INSERT(データの追加)

SELECT

FILTER

RETURN

SORT

UPDATE

REMOVE(DELETE)

COLLECT(GROUP 集計)

Discussion