やってみる

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

📌 ？
トップ > SQLite3 > SQLite3学習　JSON拡張（json_tree オブジェクト→行）

SQLite3学習　JSON拡張（json_tree オブジェクト→行）

SQLite3

　オブジェクト(ツリー,マップ)、配列を行へ展開する。

成果物

オブジェクトを行にする

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

sqlite3

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

create table users(id int primary key, json text);
insert into users values(1, '{"id": 1, "name": "yamada", "class": "A"}');
insert into users values(2, '{"id": 2, "name": "suzuki", "class": "B"}');
insert into users values(3, '{}');
.headers on
.mode column
select * from users, json_tree(users.json);

id          json                                       key         value                                 type        atom        id          parent      fullkey     path      
----------  -----------------------------------------  ----------  ------------------------------------  ----------  ----------  ----------  ----------  ----------  ----------
1           {"id": 1, "name": "yamada", "class": "A"}              {"id":1,"name":"yamada","class":"A"}  object                  0                       $           $         
1           {"id": 1, "name": "yamada", "class": "A"}  id          1                                     integer     1           2           0           $.id        $         
1           {"id": 1, "name": "yamada", "class": "A"}  name        yamada                                text        yamada      4           0           $.name      $         
1           {"id": 1, "name": "yamada", "class": "A"}  class       A                                     text        A           6           0           $.class     $         
2           {"id": 2, "name": "suzuki", "class": "B"}              {"id":2,"name":"suzuki","class":"B"}  object                  0                       $           $         
2           {"id": 2, "name": "suzuki", "class": "B"}  id          2                                     integer     2           2           0           $.id        $         
2           {"id": 2, "name": "suzuki", "class": "B"}  name        suzuki                                text        suzuki      4           0           $.name      $         
2           {"id": 2, "name": "suzuki", "class": "B"}  class       B                                     text        B           6           0           $.class     $         
3           {}                                                     {}                                    object                  0                       $           $

　なぜかidが2つ出てくる。しかもselect idすると後ろのidしか取れない。前のほうのidが取れない。おそらく前のは行番号、後ろのは列番号。列番号はなぜ偶数になるのか不明。列はkeyとvalueという2値の組合せだから？

　ツリーでみてみると、リーフはkey値がある。ノードはkey値がない。

json_treeテーブル

　json_tree()関数はjson_treeという名のテーブルを返す。

fullkey + json_extract

　fullkeyを元にしてjson_extract関数にて展開してみる。

select key, json_extract(json, fullkey) from (select * from users, json_tree(users.json));

key         json_extract(json, fullkey)         
----------  ------------------------------------
            {"id":1,"name":"yamada","class":"A"}
id          1                                   
name        yamada                              
class       A                                   
            {"id":2,"name":"suzuki","class":"B"}
id          2                                   
name        suzuki                              
class       B                                   
            {}

　行番号を追加する。

select rowid, key, json_extract(json, fullkey) from (select * from (select rowid, json from users) as users, json_tree(users.json));

rowid       key         json_extract(json, fullkey)         
----------  ----------  ------------------------------------
1                       {"id":1,"name":"yamada","class":"A"}
1           id          1                                   
1           name        yamada                              
1           class       A                                   
2                       {"id":2,"name":"suzuki","class":"B"}
2           id          2                                   
2           name        suzuki                              
2           class       B                                   
3                       {}

　列データのみに絞り込む。

select rowid, key, json_extract(json, fullkey) from (select * from (select rowid, json from users) as users, json_tree(users.json) where key!='');

rowid       key         json_extract(json, fullkey)
----------  ----------  ---------------------------
1           id          1                          
1           name        yamada                     
1           class       A                          
2           id          2                          
2           name        suzuki                     
2           class       B

　これで行における列を特定できるデータが揃った。

name='yamada'の行番号は何か

　名前で行を特定する。

select rowid from (select rowid, key, json_extract(json, fullkey) as value from (select * from (select rowid, json from users) as users, json_tree(users.json) where key!='')) where key='name' and value='yamada';

　副問合せ地獄。インデントを含めたコードは以下。

select rowid 
from (
    select rowid, key, json_extract(json, fullkey) as value 
    from (
        select * 
        from (
            select rowid, json from users
        ) as users, json_tree(users.json) 
        where key!=''
    )
) 
where key='name' and value='yamada';

　実行結果は以下。

rowid     
----------
1

name='yamada'のclassは何か

select value 
from (
    select rowid, key, json_extract(json, fullkey) as value 
    from (
        select * 
        from (
            select rowid, json from users
        ) as users, json_tree(users.json) 
        where key!=''
    )
)
where key='class' and rowid = (
    select rowid from (
        select rowid, key, json_extract(json, fullkey) as value 
        from (
            select * 
            from (
                select rowid, json from users
            ) as users, json_tree(users.json) 
            where key!=''
        )
    ) 
    where key='name' and value='yamada'
);

　なんと4重の副問合せ。しかも最上位のfromとwhereにある副問合せはほとんど重複している。これを重複なく書けないものか。

　実行結果は以下。

value     
----------
A

情報源

対象環境

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

前回まで