MariaDBでIN句にJSONデータを利用したい

Question

テーブル定義

CREATE TABLE IF NOT EXISTS `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `items` (`id`, `name`) VALUES
	(1, 'item001'),
	(2, 'item002');

CREATE TABLE IF NOT EXISTS `weapons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

INSERT INTO `weapons` (`id`, `item`) VALUES
	(1, '{"item_ids":[1,2]}');

やりたい事

以下のSQLと同じ意味の事をJSONデータを利用して行いたいです。
求めている結果もこのSQLの実行結果と同じです。

select * from items i where i.id in (1,2);

実行結果

聞きたい事

以下のSQLを実行すると結果が0件になります。

select * from items i where i.id in (
  select JSON_EXTRACT(item, '$.item_ids') from weapons w where w.id=1
);

おそらく、IN句にJSONの文字列データが渡されている為0件になっています。

weaponsテーブルのitemカラムのデータが複数行データとして取得できていればIN句で利用できると思いましたがやり方が分かりませんでした。

どのようなSQLを書けばよいでしょうか。

環境

10.2.12-MariaDB

Comments

[m.ts10806]

>select JSON_EXTRACT(item, '$.item_ids') from weapons w where w.id=1
上記だけの実行結果はどうなっていますか？

[退会済みユーザー]

文字列で"[1,2]"が得られました。
つまりselect * from items i where i.id in ("[1,2]")というSQLになっている為、期待する結果が得られていないと思います。

Answer 1

where i.id in (
  select JSON_EXTRACT(item, '$.item_ids') from weapons w where w.id=1
)

これだと、i.id in ("[1,2]")のような抽出になっていますので意図する所では無いですね。

MySQL8.0だと**JSON_TABLE()**が使用できますが、MariaDBには該当するものがないので、in記述を使用するなら、自前で用意するしかありません。

existsなら相関副問合せで同様の結果が得られると思います。

select * from items i where exists(
  select 1 from weapons 
  where id=1 and JSON_CONTAINS(item, concat('[', i.id, ']'), '$.item_ids')
)

Comments

[退会済みユーザー]

ご回答有り難うございます。
しかし、ご回答頂いたSQLを実行しても期待する結果が得られませんでした。

おそらく、JSON_EXTRACT(item, '$.item_ids[*]')=i.idこの部分がうまく比較出来ていないと思いました。

自分なりにFIEND_IN_SETを利用して書き直したのですがこちらも期待する結果が得られませんでした。

select * from items i where exists(
select 1 from weapons w where w.id=1 and FIND_IN_SET(i.id, REPLACE(REPLACE(JSON_EXTRACT(w.item, '$.item_ids'),'[' , ''), ']', ''))
)

[sazi]

JSON_CONTAINS使ってみるのはどうでしょう。
JSON_CONTAINS(item, concat('[', i.id, ']'), '$.item_ids')

[退会済みユーザー]

ありがとうございます。
以下のSQLで期待する結果が得られました。

select * from items i where exists(
select 1 from weapons w
where id = 1 and
JSON_CONTAINS(w.item, concat('[', i.id, ']'), '$.item_ids')
)