mysql 8でjsonカラムの配列のデータをレコードにして縦表示する方法

例えばこんな風に`user_recieve_log` にjsonのデータが入っているとします。


```sql
-- jsonのデータが入っているテーブル
create table user_recieve_log (user_id int, context json);
insert into user_recieve_log values
(1, '{"results" : [
  {"master_category": 1, "master_id": 101, "amount": 2},
  {"master_category": 3, "master_id": 301, "amount": 1}, 
  {"master_category": 1, "master_id": 103, "amount": 5}
]}'),
(2, '{"results" : [
  {"master_category": 2, "master_id": 201, "amount": 10}
]}'),
(3, '{"results" : [
  {"master_category": 2, "master_id": 201, "amount": 6}, 
  {"master_category": 2, "master_id": 202, "amount": 3}
]}'),
(4, '{"results" : [
  {"master_category": 3, "master_id": 303, "amount": 2}
]}'),
(5, '{"results" : [
  {"master_category": 2, "master_id": 202, "amount": 3},
  {"master_category": 3, "master_id": 302, "amount": 1}, 
  {"master_category": 3, "master_id": 301, "amount": 2}, 
  {"master_category": 2, "master_id": 203, "amount": 1}, 
  {"master_category": 1, "master_id": 102, "amount": 12}
]}');

-- ユーザーやマスタの名称が入ってるテーブル
create table users (user_id int, name text);
insert into users values(1, 'Alice'), (2, 'Bob'), (3, 'Carol'), (4, 'David'), (5, 'Erin');
create table mst_resource (id int, name text);
insert into mst_resource values(101, 'ゴールド'), (102, 'ジェム'), (103, 'チケット');
create table mst_item (id int, name text);
insert into mst_item values(201, '爆弾'), (202, '回復薬'), (203, 'ハンマー');
create table mst_avatar (id int, name text);
insert into mst_avatar values(301, '帽子'), (302, 'パーカー'), (303, 'メガネ');
```

`select * from user_recieve_log`

![Screen Shot 2020-08-29 at 16.56.47.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142910/6b4af67f-3ad5-fd0a-5b99-567b0d5888f4.png)



これに対して 例えば `user_id: 1` の人が3つ受け取っているので、その場合は3レコードで表示したい、`ユーザー名-マスタ名-取得数`も表示したいという時にどうやればいいか考えてみました。

# mysql 8

### `JSON_TABLE()` を使う

参考

- https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

`json_table()` を使うとこのように、jsonの配列の中身をレコードで表示できます

```sql
select *
from json_table(
  '[1, 2, 3, 4, 5]',
  '$[*]' columns( c1 int path '$' )
) t;
```

![Screen Shot 2020-08-29 at 17.19.50.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142910/f977b53d-598b-4c36-f29f-cd6ba44eb845.png)

`json_table()`にjsonのカラムを指定して結合するとうまく表示できました

```sql
select a.user_id
      ,c.master_category
      ,c.master_id
      ,' σﾟﾛﾟ)σ -> '
      ,concat_ws('-'
         ,b.name
         ,ifnull(d.name, ifnull(e.name, f.name))
         ,c.amount
       ) `ユーザー名-マスタ名-個数`
from user_recieve_log a
join users b using(user_id)
join json_table(
  a.context
  ,'$.results[*]' columns (
     master_category int path '$.master_category'
    ,master_id int path '$.master_id'
    ,amount int path '$.amount'
  )
) c
left join mst_resource d on (c.master_category = 1 and c.master_id = d.id)
left join mst_item e on (c.master_category = 2 and c.master_id = e.id)
left join mst_avatar f on (c.master_category = 3 and c.master_id = f.id)
order by a.user_id, c.master_category, c.master_id
;
```

https://www.db-fiddle.com/f/pJTbWUutEKcNviJLHSb6Fc/0

![Screen Shot 2020-08-29 at 17.08.41.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142910/87321c67-96d5-6c96-5453-264ef4f9cd14.png)

`user_id: 1`のAliceのjsonで3つのデータ ( &nbsp;&nbsp;&nbsp;`[{"amount": 2, "master_id": 101, "master_category": 1}, {"amount": 1, "master_id": 301, "master_category": 3}, {"amount": 5, "master_id": 103, "master_category": 1}]` &nbsp;&nbsp;&nbsp;) があり、それが3レコードで表示されているのが確認できました。
他のユーザーIDの人もうまく表示されているようです。

### 再帰with句を使う

再帰with句を使うと、このように任意の数でレコードを作成できる

```sql
with recursive r(i) as (
  select 1
  union select i + 1
  from r
  where i < 5
)
select * from r;
```

![Screen Shot 2020-08-29 at 17.31.51.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142910/8d51ae39-bb9b-832d-c76d-4663514cc762.png)

再帰with句でjsonのデータの中で最大の受取数分の数でレコード作成して、結合
→`json_extract()` で配列の何個めかを指定しつつ取得でうまく行きました

```sql
with recursive r(i) as (
  select 0
  union select i + 1
  from r, (
    select max(json_length(context->'$.results')) - 1 max
    from user_recieve_log
  ) tmp
  where i < tmp.max
)
select b.user_id
      ,b.master_category
      ,b.master_id
      ,' 👉 😮 👉 '
      ,concat_ws(','
         ,c.name
         ,ifnull(d.name, ifnull(e.name, f.name))
         ,b.amount
       ) `ユーザー名,マスタ名,個数`
from (
  select a.user_id
        ,json_extract(a.context, concat('$.results[', r.i, '].master_category')) master_category
        ,json_extract(a.context, concat('$.results[', r.i, '].master_id')) master_id
        ,json_extract(a.context, concat('$.results[', r.i, '].amount')) amount
  from user_recieve_log a
  join r on r.i < json_length(a.context->'$.results')
) b
join users c using(user_id)
left join mst_resource d on (master_category = 1 and master_id = d.id)
left join mst_item e on (master_category = 2 and master_id = e.id)
left join mst_avatar f on (master_category = 3 and master_id = f.id)
order by b.user_id, b.master_category, b.master_id
;
```

https://www.db-fiddle.com/f/iG92BYkrZXHKXoepetJvsH/0

![Screen Shot 2020-08-29 at 17.44.33.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142910/87d3d362-b4b8-9b0a-2600-2a3bcc4d6756.png)

うまく取得できました

### ストアドプロシージャで一時テーブルを作成してループでjsonの配列の内容を一つずつインサートして、それを表示する

参考

- [ストアドプロシージャの基本的ななにか - Qiita](https://qiita.com/setsuna82001/items/e742338eb93e3a48ba46)
- [MySQL :: MySQL 8.0 Reference Manual :: 13.6.6 Cursors](https://dev.mysql.com/doc/refman/8.0/en/cursors.html)

ストアドプロシージャでループ処理ができますので、それでjsonの配列の内容を一つずつ扱うことができるようでした、
以下の例はTEMPORARY TABLE(テーブル作成したセッション内でだけ使えるテーブル)にjsonの配列の内容を一つずつインサートしていってます


```sql
drop procedure if exists p;

delimiter //
create procedure p()
begin
  declare done int default false;
  declare i int default 0;
  declare userId int;
  declare contextJson text;
  declare c cursor for select user_id, context from user_recieve_log;
  declare continue handler for not found set done = true;
  
  open c;

  drop temporary table if exists tmp;
  create temporary table tmp (user_id int, master_category int, master_id int, amount int);
  read_loop: loop
    fetch c into userId, contextJson;

    if done then
      leave read_loop;
    end if;

    while i < json_length(contextJson->'$.results') do
      insert into tmp values (
         userId
		,json_extract(contextJson, concat('$.results[', i, '].master_category'))
		,json_extract(contextJson, concat('$.results[', i, '].master_id'))
		,json_extract(contextJson, concat('$.results[', i, '].amount'))
	  );
      set i = i + 1;
    end while;
    set i = 0;

  end loop;
end
//
delimiter ;

call p;

select a.user_id
      ,a.master_category
      ,a.master_id
      ,case row_number() over(order by a.user_id, a.master_category, a.master_id) % 6
          when 1 then '　 ∧＿∧'
          when 2 then '（｡･ω･｡)つ━☆・*。'
          when 3 then '⊂　　 ノ 　　　・゜+.'
          when 4 then '　しーＪ　　　°。+ *´¨)'
          when 5 then '　　　　　　　　　.· ´¸.·*´¨) ¸.·*¨)'
          when 0 then '　　　　　　　　　　(¸.·´ (¸.·* ☆'
       end ☆☆☆
      ,concat_ws(','
         ,b.name
         ,ifnull(d.name, ifnull(e.name, f.name))
         ,a.amount
       ) `ユーザー名,マスタ名,個数`
from tmp a
join users b using(user_id)
left join mst_resource d on (a.master_category = 1 and a.master_id = d.id)
left join mst_item e on (a.master_category = 2 and a.master_id = e.id)
left join mst_avatar f on (a.master_category = 3 and a.master_id = f.id)
order by a.user_id, a.master_category, a.master_id
;
```

https://www.db-fiddle.com/f/rq8eCKdT5BMEoSyG6hfjQj/0

![Screen Shot 2020-08-29 at 18.02.38.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142910/e7e32ebc-fc0f-6987-9201-a79e7acc0fd2.png)

# postgresql 

postgresqlは普段あまり使っておらず、簡単にしか調べてないのですが、postgresqlでもやってみました

参考

- [sql - Query combinations with nested array of records in JSON datatype - Stack Overflow](https://stackoverflow.com/questions/26877241/query-combinations-with-nested-array-of-records-in-json-datatype)

`json_to_recordset()` や `json_array_elements()` を使うと簡単にできるようです


```sql
select a.user_id, b.*
from user_recieve_log a,
json_to_recordset(context->'results')
as b("master_category" text, "master_id" int, "amount" int);
```

```sql
select a.user_id
      ,b.value->'master_category'
      ,b.value->'master_id'
      ,b.value->'amount'
from user_recieve_log a,
json_array_elements(context->'results') b;
```

https://www.db-fiddle.com/f/iahh5hk4BGoT1PQEkSPUDF/7

---

最後まで見ていただいてありがとうございました