やってみる

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

SQLite3学習 JSON拡張(json_tree オブジェクトツリー→行)

 オブジェクトツリーを行へ展開する。

成果物

オブジェクトツリーにて指定位置のオブジェクトを行へ展開する

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

sqlite3

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

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

 実行結果。

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

 前回のやつにjson_extract(users.json, '$.status')を足しただけ。これでツリーの位置を指定している。

ネスト

create table users(id int primary key, json text);
insert into users values(1, '{"root":{"node1":{"node2":{"age": 10, "name": "A"}}}}');
insert into users values(2, '{"root":{"node1":{"node2":{"age": 25, "name": "B"}}}}');
insert into users values(3, '{}');
.headers on
.mode column
select * from users, json_tree(json_extract(users.json, '$.root.node1.node2'));
id          json                                                   key         value                  type        atom        id          parent      fullkey     path      
----------  -----------------------------------------------------  ----------  ---------------------  ----------  ----------  ----------  ----------  ----------  ----------
1           {"root":{"node1":{"node2":{"age": 10, "name": "A"}}}}              {"age":10,"name":"A"}  object                  0                       $           $         
1           {"root":{"node1":{"node2":{"age": 10, "name": "A"}}}}  age         10                     integer     10          2           0           $.age       $         
1           {"root":{"node1":{"node2":{"age": 10, "name": "A"}}}}  name        A                      text        A           4           0           $.name      $         
2           {"root":{"node1":{"node2":{"age": 25, "name": "B"}}}}              {"age":25,"name":"B"}  object                  0                       $           $         
2           {"root":{"node1":{"node2":{"age": 25, "name": "B"}}}}  age         25                     integer     25          2           0           $.age       $         
2           {"root":{"node1":{"node2":{"age": 25, "name": "B"}}}}  name        B                      text        B           4           0           $.name      $         

キーと値

select rowid, key, value 
from (
    select * from (
        select rowid, json from users
    ) as users, json_tree(json_extract(users.json, '$.root.node1.node2')) 
    where key!=''
);
rowid       key         value     
----------  ----------  ----------
1           age         10        
1           name        A         
2           age         25        
2           name        B         

情報源

対象環境

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

前回まで