0

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 (").

Improve this question
3
  • 1
    The query you show does not look wrong to me, and I can't explain the result you got. I suggest you create a dbfiddle with a minimal example to reproduce the problem. Commented Apr 26, 2022 at 15:49
  • 1
    The query works correctly. dbfiddle.uk/… The problematic output looks like double-paste misprint. Commented Apr 26, 2022 at 15:58
  • I was testing it on postman, I don't know if that is the reason for a wrong output, but normally i use it for testing and works fine. Commented Apr 27, 2022 at 8:14
Related questions
1
json_decode(json_encode(an indexed array)) gives NULL
2
Getting back strange JSON array from MySQL
0
PHP treats an array as an object
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.