例えばこんな風にuser_recieve_log にjsonのデータが入っているとします。
-- 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
これに対して 例えば user_id: 1 の人が3つ受け取っているので、その場合は3レコードで表示したい、ユーザー名-マスタ名-取得数も表示したいという時にどうやればいいか考えてみました。
mysql 8
JSON_TABLE() を使う
参考
json_table() を使うとこのように、jsonの配列の中身をレコードで表示できます
select *
from json_table(
'[1, 2, 3, 4, 5]',
'$[*]' columns( c1 int path '$' )
) t;
json_table()にjsonのカラムを指定して結合するとうまく表示できました
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
;
user_id: 1のAliceのjsonで3つのデータ ( [{"amount": 2, "master_id": 101, "master_category": 1}, {"amount": 1, "master_id": 301, "master_category": 3}, {"amount": 5, "master_id": 103, "master_category": 1}] ) があり、それが3レコードで表示されているのが確認できました。
他のユーザーIDの人もうまく表示されているようです。
再帰with句を使う
再帰with句を使うと、このように任意の数でレコードを作成できる
with recursive r(i) as (
select 1
union select i + 1
from r
where i < 5
)
select * from r;
再帰with句でjsonのデータの中で最大の受取数分の数でレコード作成して、結合
→json_extract() で配列の何個めかを指定しつつ取得でうまく行きました
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
;
うまく取得できました
ストアドプロシージャで一時テーブルを作成してループでjsonの配列の内容を一つずつインサートして、それを表示する
参考
ストアドプロシージャでループ処理ができますので、それでjsonの配列の内容を一つずつ扱うことができるようでした、
以下の例はTEMPORARY TABLE(テーブル作成したセッション内でだけ使えるテーブル)にjsonの配列の内容を一つずつインサートしていってます
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
;
postgresql
postgresqlは普段あまり使っておらず、簡単にしか調べてないのですが、postgresqlでもやってみました
参考
json_to_recordset() や json_array_elements() を使うと簡単にできるようです
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);
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;
最後まで見ていただいてありがとうございました