创建一个导致嵌套 JSON 数组对象的 MySQL 过程 [英] Create a MySQL procedure that result in a nested JSON Array Object

问题描述

我需要在 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屋！