创建一个导致嵌套 JSON 数组对象的 MySQL 过程 [英] Create a MySQL procedure that result in a nested JSON Array Object
本文介绍了创建一个导致嵌套 JSON 数组对象的 MySQL 过程的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要在 mysql 中创建一个存储过程,它从数据库中获取一些字段并创建一个嵌套的 json 对象:
I need to create a stored procedure in mysql that take some fields from DB and create a neasted json object:
首先我创建一个 json 对象,如下所示:
Firstly I create a json object as showing below:
{
"form": "Exams tests",
"version": "v3.001.01",
"questions": []
}
其次是一个像这样的 json 数组对象:
And secondly a json array object like this:
{[
{
"ordem": 1,
"num_questions": 1,
"question1": "How old are you?"
"answer1": "I'm 18 years old."
}
{
"ordem": 2,
"num_questions": 2,
"question1": "How old are you?"
"answer1": "I'm 18 years old."
"question2": "Where do you live?"
"answer2": "I live in Boston."
}
{
"ordem": 3,
"num_questions": 1,
"question1": "How old are you?"
"answer1": "I'm 23 years old."
}
]}
结果查询显示如下:
{
"form": "Exams tests",
"version": "v3.001.01",
"questions": {[
{
"ordem": 1,
"num_questions": 1,
"question1": "How old are you?"
"answer1": "I'm 18 years old."
}
{
"ordem": 2,
"num_questions": 2,
"question1": "How old are you?"
"answer1": "I'm 18 years old."
"question2": "Where do you live?"
"answer2": "I live in Boston."
}
{
"ordem": 3,
"num_questions": 1,
"question1": "How old are you?"
"answer1": "I'm 23 years old."
}
]}
}
当我尝试将嵌套的 json 数组插入到 json 对象时出现错误
I got an error when I'm trying to insert a nested json array into a json object
推荐答案
DECLARE json TEXT DEFAULT '';
DECLARE code INTEGER;
SET code = 1;
SELECT JSON_OBJECT(
'form', v.form_name,
'version', v.version,
'questions, ( select json_arrayagg(json_object(
'ordem',`tb_questions`.`order`,
'num_questions',`tb_questions`.`num`
'question1',`tb_questions`.`question1`
'answer1',`tb_questions`.`answer1`
))
from tb_questions
WHERE tb_questions.code = code)
) INTO json
FROM v_case AS v
WHERE v.code = code;
这篇关于创建一个导致嵌套 JSON 数组对象的 MySQL 过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文