构建一个查询使用JSON数组的值在一个表中的另一个选择行?

Question

我正试图从一个表中选择基于匹配该表中的列到另一个表中的JSON数组的行。

例如:

reader_templates:数组是JSON数据类型:

id: 1
ckeditor_templates:  ["CK_DX_ABD", "CK_DX_ABD2"]
radreports_templates:  ["0000001", "0000005", "0000045WO", "0000045WC", "0000303"]
reader_id:  0001

report_templates:

id: 1
radreport_id:  0000001
..
id:2
radreport_id:  CK_DX_ABD

我想从report_templates表中的行中选择*，其中radreport_id匹配ckeditor_templates JSON数组中的值，然后还有另一个查询做同样的事情来匹配radreports_templates JSON数组中的值。似乎应该有一种简单的方法来做到这一点?

查询的结果如下:

SELECT REPLACE ((SELECT REPLACE((SELECT ckeditor_templates from reader_templates WHERE reader_id = '0001'), '[', '')),']','')
"CK_DX_ABD", "CK_DX_ABD2"

，我希望我可以使用"WHERE IN"子句给我我正在寻找的结果，或者使用一些JSON函数来做同样的事情。

Answer 1

嗯，这实际上似乎可以从reader_templates表中生成匹配:

SELECT radreport_id FROM reader_templates 
CROSS JOIN json_table(ckeditor_templates, '$[*]' COLUMNS ( 
radreport_id varchar(20) path '$')) AS temp WHERE reader_id = '0001';

结果

radreport_id
CK_DX_ABD
CK_DX_ABD2

然后我可以在"IN"子句作为子查询:

SELECT * from report_templates WHERE radreport_id IN
(SELECT radreport_id FROM reader_templates 
CROSS JOIN json_table(ckeditor_templates, '$[*]' COLUMNS ( 
radreport_id varchar(20) path '$')) AS temp WHERE reader_id = '0001')

给出匹配那些radreport_id的行，这是我正在寻找的。

唯一的问题似乎是，我不得不改变编码整理radreport_id从utf8mb4_unicode_ci到utf8mb4_bin，这显然是JSON的整理。

我还得再做点改动，但这就是我想要的结果。

对另一组radreports_templates的查询类似:

SELECT * from report_templates WHERE radreport_id IN
(SELECT radreport_id FROM reader_templates 
CROSS JOIN json_table(radreports_templates, '$[*]' COLUMNS ( 
radreport_id varchar(20) path '$')) AS temp WHERE reader_id = '0001')

这个场景是，我在一个表中有一组具有唯一radreport_id的模板，并且该表中有两种不同类型的模板。实际上，将它们全部放在一个表中更容易一些，然后将特定用户希望在其"集合"中拥有的选项(总数的一个子集)以JSON形式存储在另一个表中，而不是为每个选项单独设置一行。不确定哪一种是最好的方法，但这种方法有效，而且似乎足够快。不会有更多的100-200个模板。