Mysql JSON_ARRAYAGG as a string instead of a json
Hi guys, how are you ?
I'm trying to use a sub query to get and array of objects but in the result i'm not sure how to get the array and dont' get a string
My query is like this
and the result that im getting is
one solution is for loop every result and do a JSON.parse but dosen't feel right
I'm not really sure what to do here
thanks for help
const data: PaymentResponse[] = await db
.select({
id: parent.id,
date: parent.date,
status: parent.status,
method: parent.method,
totalAmount: parent.totalAmount,
contacts: sql<SimpleContactResponse[]>`(
SELECT JSON_ARRAYAGG(JSON_OBJECT(
'id', c.id,
'name', c.name,
'lastName', c.last_name
))
FROM contact c
INNER JOIN invoice i ON c.id = i.contact_id
INNER JOIN payment_invoice pi ON i.id = pi.invoice_id
WHERE pi.payment_id = p.id
GROUP BY pi.payment_id
)`,
settlements: sql<{
id: number;
name: string;
}[]>`(
SELECT JSON_ARRAYAGG(JSON_OBJECT(
'id', s.id,
'name', s.name
))
FROM settlement s
INNER JOIN invoice i ON s.id = i.settlement_id
INNER JOIN payment_invoice pi ON i.id = pi.invoice_id
WHERE pi.payment_id = p.id
GROUP BY pi.payment_id
)`
})
.from(parent)
.where(condition)
.limit(dto.limit)
.offset(offset)
.orderBy(desc(parent.date), desc(parent.id));
const data: PaymentResponse[] = await db
.select({
id: parent.id,
date: parent.date,
status: parent.status,
method: parent.method,
totalAmount: parent.totalAmount,
contacts: sql<SimpleContactResponse[]>`(
SELECT JSON_ARRAYAGG(JSON_OBJECT(
'id', c.id,
'name', c.name,
'lastName', c.last_name
))
FROM contact c
INNER JOIN invoice i ON c.id = i.contact_id
INNER JOIN payment_invoice pi ON i.id = pi.invoice_id
WHERE pi.payment_id = p.id
GROUP BY pi.payment_id
)`,
settlements: sql<{
id: number;
name: string;
}[]>`(
SELECT JSON_ARRAYAGG(JSON_OBJECT(
'id', s.id,
'name', s.name
))
FROM settlement s
INNER JOIN invoice i ON s.id = i.settlement_id
INNER JOIN payment_invoice pi ON i.id = pi.invoice_id
WHERE pi.payment_id = p.id
GROUP BY pi.payment_id
)`
})
.from(parent)
.where(condition)
.limit(dto.limit)
.offset(offset)
.orderBy(desc(parent.date), desc(parent.id));
{
"id": 2901,
"date": "2023-10-17T07:00:00.000Z",
"status": "PAID",
"method": "TRANSFER",
"totalAmount": "56460.00",
"contacts": "[{\"id\": 311, \"name\": \"Maximiliano\", \"lastName\": \"Figueroa\"}]",
"settlements": "[{\"id\": 64, \"name\": \"Octubre 2023\"}]"
},
{
"id": 2901,
"date": "2023-10-17T07:00:00.000Z",
"status": "PAID",
"method": "TRANSFER",
"totalAmount": "56460.00",
"contacts": "[{\"id\": 311, \"name\": \"Maximiliano\", \"lastName\": \"Figueroa\"}]",
"settlements": "[{\"id\": 64, \"name\": \"Octubre 2023\"}]"
},
2 Replies
You could use the
.mapWith method on that select like this:
...
contacts: sql<SimpleContactResponse[]>`(
SELECT JSON_ARRAYAGG(JSON_OBJECT(
'id', c.id,
'name', c.name,
'lastName', c.last_name
))
FROM contact c
INNER JOIN invoice i ON c.id = i.contact_id
INNER JOIN payment_invoice pi ON i.id = pi.invoice_id
WHERE pi.payment_id = p.id
GROUP BY pi.payment_id
)`.mapWith(JSON.parse),
...
...
contacts: sql<SimpleContactResponse[]>`(
SELECT JSON_ARRAYAGG(JSON_OBJECT(
'id', c.id,
'name', c.name,
'lastName', c.last_name
))
FROM contact c
INNER JOIN invoice i ON c.id = i.contact_id
INNER JOIN payment_invoice pi ON i.id = pi.invoice_id
WHERE pi.payment_id = p.id
GROUP BY pi.payment_id
)`.mapWith(JSON.parse),
...
that works perfect thanks