やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

📌 ？
トップ > SQLite3 > SQLite3学習　JSON拡張（json_extract）

SQLite3学習　JSON拡張（json_extract）

SQLite3

　JSON値を取得する。

成果物

JSONから値を取得する

　ターミナルでSQLite3コマンドを対話モードで実行する。

sqlite3

　以下コマンドをコピペする。

create table users(id int primary key, json text);
insert into users values(1, '{"age":10, "name":"yamada", "class":"A"}');
insert into users values(2, '{"age":22, "name":"suzuki", "class":"A"}');
insert into users values(3, '{"age":34, "name":"tanaka", "class":"B"}');
select json_extract(json, '$.name') from users;

　以下、実行結果。

yamada
suzuki
tanaka

　SQLite3でJSON値を取得できた！

取得する

ツリーから

単一キーの値を

sqlite3

　以下コマンドをコピペする。

create table users(id int primary key, json text);
insert into users values(1, '{"node1": {"node2": {"node3": {"name": "A"}}}}');
insert into users values(2, '{"node1": {"node2": {"node3": {"name": "B"}}}}');
insert into users values(3, '{"node1": {"node2": {"node3": {"name": "C"}}}}');
select json_extract(json, '$.node1.node2.node3.name') from users;

　以下、実行結果。

A
B
C

オブジェクトを

select json_extract(json, '$.node1.node2') from users;

{"node3":{"name":"A"}}
{"node3":{"name":"B"}}
{"node3":{"name":"C"}}

　だが、オブジェクト形式ではSQLで行や列として操作できない。

配列を

sqlite3
create table users(id int primary key, json text);
insert into users values(1, '{"node1": {"node2": ["A", "B"]}}');
insert into users values(2, '{"node1": {"node2": ["A", "C"]}}');
insert into users values(3, '{"node1": {"node2": []}}');
select json_extract(json, '$.node1.node2') from users;

["A","B"]
["A","C"]
[]

　だが、配列形式で取得できても、これを行に変換せねばSQLで扱えない。そのためにはjson_each()を使う。

json_each();

　配列の末尾や先頭への要素追加・削除する関数はない。以下参考のようにするか、配列を作り直すしかない。

配列の要素を

select json_extract(json, '$.node1.node2[0]') from users;

A
A

　指定位置にある要素を取得することは稀かもしれない。たとえば個数不定、順不同であれば位置の指定に意味はない。

配列から

配列を

sqlite3

　以下コマンドをコピペする。

create table users(id int primary key, json text);
insert into users values(1, '[[1,2],["A","B"]]');
insert into users values(2, '[[3],["C"]]');
insert into users values(3, '[]');
select json_extract(json, '$[1]') from users;

　以下、実行結果。

["A","B"]
["C"]
[]

　だが、配列形式で取得できても、これを行に変換せねばSQLで扱えない。

　配列←→行への相互変換ができない。

　また、配列の末尾や先頭への要素追加・削除もできない。

指定インデックスにある値を

select json_extract(json, '$[1][0]') from users;

A
C

　指定位置にある要素を取得することは稀かもしれない。たとえば個数不定、順不同であれば位置の指定に意味がなくなる。

変換する

sqlite3
.headers on
.mode column
create table users(id int primary key, json text);
insert into users values(1, '{"age":10, "name":"yamada", "class":"A"}');
insert into users values(2, '{"age":22, "name":"suzuki", "class":"A"}');
insert into users values(3, '{"age":34, "name":"tanaka", "class":"B"}');

名前付き列へ

select json_extract(json, '$.name') as name from users;

name      
----------
yamada    
suzuki    
tanaka

テーブルへ

select rowid as id, json_extract(json, '$.age') as age, json_extract(json, '$.name') as name from users;

id          age         name      
----------  ----------  ----------
1           10          yamada    
2           22          suzuki    
3           34          tanaka

配列へ

create table users(id int primary key, json text);
insert into users values(1, '{"node1": {"node2": ["A", "B"]}}');
insert into users values(2, '{"node1": {"node2": ["A", "C"]}}');
insert into users values(3, '{"node1": {"node2": []}}');
select json_extract(json, '$.node1.node2') from users;

select json_group_array(value) from (select distinct json_each.value from users, json_each(json_extract(users.json, '$.node1.node2')) order by json_each.value asc);

["A","B","C"]

　他の関数も使っている。

情報源

対象環境

$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux

前回まで