やってみる

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

📌 ？
トップ > SQLite3 > SQLite3学習　JSON拡張（json_group_array 行→配列）

SQLite3学習　JSON拡張（json_group_array 行→配列）

SQLite3

　行をJSON配列テキストに変換する。

成果物

json_group_array()

　ターミナルで以下コマンドを実行する。

sqlite3

　対話モードにて以下コマンドをコピペし実行。

create table users(id int primary key, age int, name text);
insert into users values(111, '12', 'yamada');
insert into users values(222, '25', 'suzuki');
insert into users values(333, '38', 'tanaka');
.headers on
.mode column
select json_group_array(name) from users;

　実行結果は以下。

json_group_array(name)      
----------------------------
["yamada","suzuki","tanaka"]

数値とテキスト混在

　テーブルとデータを作る。

create table users(id int primary key, json text);
insert into users values(111, '{"id": 1, "age": 12, "name": "yamada"}');
insert into users values(222, '{"id": 1, "age": 25, "name": "suzuki"}');
insert into users values(333, '{"id": 1, "age": 39, "name": "tanaka"}');
.headers on
.mode column

　JSONの全キー値を配列にする。それを全行に対して行う。

select json_group_array(value) from (select json_tree.value from users, json_tree(users.json) where json_tree.atom!='');

　インデントを入れると以下。

select json_group_array(value) 
from (
    select json_tree.value 
    from users, json_tree(users.json) 
    where json_tree.atom!=''
);

　実行結果は以下。

json_group_array(value)                    
-------------------------------------------
[1,12,"yamada",1,25,"suzuki",1,39,"tanaka"]

　テキストはダブルクォートされる。OK。

経緯

　JSON配列になる前のテーブルは以下になる。

    select json_tree.value 
    from users, json_tree(users.json) 
    where json_tree.atom!=''

value     
----------
1         
12        
yamada    
1         
25        
suzuki    
1         
39        
tanaka

　これをselect json_group_array(value);することで[1,12,"yamada",1,25,"suzuki",1,39,"tanaka"]になる。

Table→JSONできない

Table→JSONできない。

　できなかった。たとえば以下のテーブルがあったとする。

create table users(id int primary key, name text);
insert into users values(111, 'yamada');
insert into users values(222, 'suzuki');
insert into users values(333, 'tanaka');

　期待値は以下。これをSQL文だけで実現したい。SQLite3シェルはおろかBashシェルやTclなど他のプログラミング言語も一切使わずに。

{"id": 111, "name": "yamada"}
{"id": 222, "name": "suzuki"}
{"id": 333, "name": "tanaka"}

　以下のような表になる。

表

id    name
111 yamada
222 suzuki
333 tanaka

　これを列ごとに行へ展開すると以下。これができない。

列→行

rid   key value
0   id  111
0   name    yamada
1   id  222
1   name    suzuki
2   id  333
2   name    tanaka

カラムの取得まではできるがデータとの紐付けができない。

create table users(id int primary key, json text);

SELECT 
  m.name as table_name, 
  p.name as column_name
FROM 
  sqlite_master AS m
JOIN 
  pragma_table_info(m.name) AS p
ORDER BY 
  m.name, 
  p.cid;

table_name  column_name
----------  -----------
users       id         
users       json

　列を行へ展開できたら、あとは以下のようにjson_group_object()関数でJSON化できると思うのだが……。

select rid, json_group_object(key, value) from (列を行へ展開した表) where rid = ();

対象環境

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

前回まで