Create a MySQL procedure that result in a nested JSON Array Object

Question

I need to create a stored procedure in mysql that take some fields from DB and create a neasted json object:

Firstly I create a json object as showing below:

{
    "form": "Exams tests",
    "version": "v3.001.01",
    "questions": []
}

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."
        }
]}

And the result query showld be something like this:

{
    "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."
        }
    ]}
}

I got an error when I'm trying to insert a nested json array into a json object

Let us see the code please, a letter is nice but you cannot beat showing us the code — RiggsFolly
– RiggsFolly, Commented Jan 30, 2020 at 16:41
Possible you can find the answer here: stackoverflow.com/questions/41758870/… — Slava Rozhnev
– Slava Rozhnev, Commented Jan 30, 2020 at 18:00
Does this answer your question? How to convert result table to JSON array in MySQL — nbk
– nbk, Commented Jan 30, 2020 at 18:17
I got it now. I used JSON_ARRAYAGG with JSON_OBJECT, both together. — Juliano Costa
– Juliano Costa, Commented Jul 12, 2020 at 22:40
Do all steps together with only one select, as below: 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) ) INTO json FROM v_case AS v; — Juliano Costa
– Juliano Costa, Commented Jul 12, 2020 at 23:05

Juliano Costa · Accepted Answer · 2020-07-12 22:51:44Z

I joined JSON_OBJECT with json_arrayagg as below:

{
    "name": "Moon",
    "resume": "This is a resume.",
    "dt_ini": "2018-09-01",
    "dt_end": null,
    "cases": [
        {
            "unit": "unit 1",
            "unit_id": 10
        },
        {
            "unit": "unit 2",
            "unit_id": 290
        },
        {
            "unit": "unit 3",
            "unit_id": 44
        },
        {
            "unit": "unit 4",
            "unit_id": 108
        }
    ]
}

The final result is this:

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_list_case`(
IN `code` int,
IN `base_code` int)
BEGIN

    DECLARE json TEXT DEFAULT '';

    SELECT JSON_OBJECT(
        'name', v.name, 
        'dt_ini', v.dt_ini, 
        'dt_end', v.dt_end, 
        'resumo', v.resumo,
        'cases', ( select json_arrayagg(json_object(
                                'unit_id',`tb_unit`.`unit_id`,
                                'unit',`tb_unit`.`unit`))
                            from tb_unit_case
                                INNER JOIN tb_unit ON tb_unit_case.unid_code = tb_unit.unit_id
                            WHERE tb_unit_case.case_code = code)
    ) INTO json
    FROM v_case AS v
    WHERE v.code = code and v.base_code = base_code;
    
    SELECT json;
    
END

Juliano Costa · Accepted Answer · 2020-07-12 23:06:19Z

0

    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;

answered Jul 12, 2020 at 23:06

Juliano Costa

5337 silver badges10 bronze badges

Comments

Chris Catignani · Accepted Answer · 2022-05-30 16:41:19Z

0

This is not an array, you have to remove {}

{[
        {
            "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."
        }
]}

edited May 30, 2022 at 16:41

Chris Catignani

5,30819 gold badges46 silver badges54 bronze badges

answered May 30, 2022 at 12:08

Mohammad mhaidat

11 bronze badge

Collectives™ on Stack Overflow

Create a MySQL procedure that result in a nested JSON Array Object

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related