JSON_ARRAYAGG returns itself as key

153 views Asked by At

Given the following query:

SELECT JSON_ARRAYAGG(
        JSON_OBJECT(
        "empresa_id", agenda.empresa_id,
        "nombreemp", agenda.nombre_empresa,
        "personas", (SELECT DISTINCT JSON_ARRAYAGG(JSON_OBJECT("nombrecon",s.nombre_contacto))          
FROM subagenda s
where s.id_empresa = agenda.empresa_id
)
)
)
from (select DISTINCT agenda.empresa_id, agenda.nombre_empresa from agenda left join subagenda on subagenda.id_empresa= agenda.empresa_id ) agenda

it returns the following json:

[
    {
        "JSON_ARRAYAGG(JSON_OBJECT('empresa_id', agenda.empresa_id,'nombreemp', agenda.nombre_empresa, 'personas', (SELECT DISTINCT JSON_ARRAYAGG(JSON_OBJECT('nombrecon',s.nombre_contacto)) FROM subagenda s where s.id_empresa = agenda.empresa_id)))": "[{\"personas\": null, \"nombreemp\": \"Jane\", \"empresa_id\": 2}, {\"personas\": [{\"nombrecon\": \"Sergio\"}, {\"nombrecon\": \"testnomb\"}], \"nombreemp\": \"CandyCorp\", \"empresa_id\": 3}]"
    }
]

I don't understand why the query itself is being returned as the key of the json and also I don't know why are the quotes slashed (") instead of just the quotes like in other queries (").

0

There are 0 answers