基于另一个键在某个键上获取/分配SQL JSON数据值

Question

我有两个SQL表，一个是问题列表，另一个是答案。答案表如下：

Question_ID      answer_id
1              ["A"]
2              ["B"]
3            ["A", "B"]

对于问题表，它看起来像这样：

Question_ID                                   choices_value
1           {"multiple_choices:"[{"id":"A", "name":"Italy"},{id:"B", "name":"Germany"}]}
2            {"multiple_choices:"[{"id":"A", "name":"Left"},{id:"B", "name":"Right"}]}
3             {"multiple_choices:"[{"id":"A", "name":"Red"},{id:"B", "name":"Green"}]}

我的问题是，我如何获取/分配"；name"；基于question_ID和answer_ID中的密钥的choices_value上的密钥？

编辑：

附加信息：

• 我使用MySQL 8.0

Answer 1

您可以使用JSON_TABLE将json解析为表格格式。然后连接两个表。

db<gt；小提琴在这里。

with questions as (
select 1 as question_id
, '{"multiple_choices":[{"id":"A", "name":"Italy"},{"id":"B", "name":"Germany"}]}' as jsval
union all 
select 2
, '{"multiple_choices":[{"id":"A", "name":"Left"},{"id":"B", "name":"Right"}]}'
union all
select 3
, '{"multiple_choices":[{"id":"A", "name":"Red"},{"id":"B", "name":"Green"}]}'
)
, answers as (
select 1  as question_id, '["A"]' as answer       
union all
select 2  as question_id, '["B"]' as answer
union all
select 3  as question_id, '["A", "B"]' as answer
)
, q_avail_ans as (
select q.question_id, q_a.*
from questions as q,
json_table(q.jsval,
'$."multiple_choices"[*]'
columns (
ans_id varchar(100) path '$."id"',
ans_name varchar(100) path '$."name"'
)
) as q_a
)
, q_answered_ans as (
select a.question_id,
a_a.*
from answers as a,
json_table(a.answer,
'$[*]'
columns (
ans varchar(100) path '$'
)
) as a_a
)
select qa.*
, aa.ans
from q_avail_ans as qa
left join q_answered_ans as aa
on qa.question_id = aa.question_id
and qa.ans_id = aa.ans