Solución:
Actualización: en PostgreSQL 9.4 esto mejora mucho con la introducción de to_json, json_build_object, json_object y json_build_array, aunque es detallado debido a la necesidad de nombrar todos los campos explícitamente:
select
json_build_object(
'id', u.id,
'name', u.name,
'email', u.email,
'user_role_id', u.user_role_id,
'user_role', json_build_object(
'id', ur.id,
'name', ur.name,
'description', ur.description,
'duty_id', ur.duty_id,
'duty', json_build_object(
'id', d.id,
'name', d.name
)
)
)
from users u
inner join user_roles ur on ur.id = u.user_role_id
inner join role_duties d on d.id = ur.duty_id;
Para versiones anteriores, siga leyendo.
No se limita a una sola fila, solo es un poco doloroso. No puede crear un alias de tipos de filas compuestos usando AS, por lo que debe usar una expresión de subconsulta con alias o CTE para lograr el efecto:
select row_to_json(row)
from (
select u.*, urd AS user_role
from users u
inner join (
select ur.*, d
from user_roles ur
inner join role_duties d on d.id = ur.duty_id
) urd(id,name,description,duty_id,duty) on urd.id = u.user_role_id
) row;
produce, a través de http://jsonprettyprint.com/:
{
"id": 1,
"name": "Dan",
"email": "[email protected]",
"user_role_id": 1,
"user_role": {
"id": 1,
"name": "admin",
"description": "Administrative duties in the system",
"duty_id": 1,
"duty": {
"id": 1,
"name": "Script Execution"
}
}
}
Querrás usar array_to_json(array_agg(...)) cuando tienes una relación de 1: muchos, por cierto.
Idealmente, la consulta anterior debería poder escribirse como:
select row_to_json(
ROW(u.*, ROW(ur.*, d AS duty) AS user_role)
)
from users u
inner join user_roles ur on ur.id = u.user_role_id
inner join role_duties d on d.id = ur.duty_id;
… pero PostgreSQL ROW el constructor no acepta AS alias de columna. Desafortunadamente.
Afortunadamente, optimizan lo mismo. Compare los planes:
- La versión de la subconsulta anidada; vs
- Este último anidado
ROWversión del constructor con los alias eliminados para que se ejecute
Debido a que los CTE son vallas de optimización, reformular la versión de subconsultas anidadas para usar CTE encadenados (WITH expresiones) pueden no funcionar tan bien y no darán como resultado el mismo plan. En este caso, está atascado con subconsultas anidadas desagradables hasta que obtengamos algunas mejoras en row_to_json o una forma de anular los nombres de las columnas en un ROW constructor más directamente.
De todos modos, en general, el principio es que donde desea crear un objeto json con columnas a, b, c, y desearía poder escribir la sintaxis ilegal:
ROW(a, b, c) AS outername(name1, name2, name3)
en su lugar, puede usar subconsultas escalares que devuelvan valores con tipo de fila:
(SELECT x FROM (SELECT a AS name1, b AS name2, c AS name3) x) AS outername
O:
(SELECT x FROM (SELECT a, b, c) AS x(name1, name2, name3)) AS outername
Además, tenga en cuenta que puede componer json valores sin comillas adicionales, por ejemplo, si coloca la salida de un json_agg Dentro de un row_to_json, el interior json_agg el resultado no se cotizará como una cadena, se incorporará directamente como json.
por ejemplo, en el ejemplo arbitrario:
SELECT row_to_json(
(SELECT x FROM (SELECT
1 AS k1,
2 AS k2,
(SELECT json_agg( (SELECT x FROM (SELECT 1 AS a, 2 AS b) x) )
FROM generate_series(1,2) ) AS k3
) x),
true
);
la salida es:
{"k1":1,
"k2":2,
"k3":[{"a":1,"b":2},
{"a":1,"b":2}]}
Tenga en cuenta que el json_agg producto, [{"a":1,"b":2}, {"a":1,"b":2}], no se ha escapado de nuevo, ya que text sería.
Esto significa que puedes componer json para construir filas, no siempre es necesario crear tipos compuestos de PostgreSQL enormemente complejos y luego llamar row_to_json en la salida.
Mi sugerencia para la capacidad de mantenimiento a largo plazo es usar una VISTA para construir la versión aproximada de su consulta y luego usar una función como la siguiente:
CREATE OR REPLACE FUNCTION fnc_query_prominence_users( )
RETURNS json AS $$
DECLARE
d_result json;
BEGIN
SELECT ARRAY_TO_JSON(
ARRAY_AGG(
ROW_TO_JSON(
CAST(ROW(users.*) AS prominence.users)
)
)
)
INTO d_result
FROM prominence.users;
RETURN d_result;
END; $$
LANGUAGE plpgsql
SECURITY INVOKER;
En este caso, el objeto prominence.users es una vista. Como seleccioné users. *, No tendré que actualizar esta función si necesito actualizar la vista para incluir más campos en un registro de usuario.
Estoy agregando esta solución porque la respuesta aceptada no contempla las relaciones N: N. también conocido como: colecciones de colecciones de objetos
Si tienes relaciones N: N la clausula with es tu amigo. En mi ejemplo, me gustaría construir una vista de árbol de la siguiente jerarquía.
A Requirement - Has - TestSuites
A Test Suite - Contains - TestCases.
La siguiente consulta representa las combinaciones.
SELECT reqId ,r.description as reqDesc ,array_agg(s.id)
s.id as suiteId , s."Name" as suiteName,
tc.id as tcId , tc."Title" as testCaseTitle
from "Requirement" r
inner join "Has" h on r.id = h.requirementid
inner join "TestSuite" s on s.id = h.testsuiteid
inner join "Contains" c on c.testsuiteid = s.id
inner join "TestCase" tc on tc.id = c.testcaseid
GROUP BY r.id, s.id;
Dado que no puede hacer múltiples agregaciones, debe usar “CON”.
with testcases as (
select c.testsuiteid,ts."Name" , tc.id, tc."Title" from "TestSuite" ts
inner join "Contains" c on c.testsuiteid = ts.id
inner join "TestCase" tc on tc.id = c.testcaseid
),
requirements as (
select r.id as reqId ,r.description as reqDesc , s.id as suiteId
from "Requirement" r
inner join "Has" h on r.id = h.requirementid
inner join "TestSuite" s on s.id = h.testsuiteid
)
, suitesJson as (
select testcases.testsuiteid,
json_agg(
json_build_object('tc_id', testcases.id,'tc_title', testcases."Title" )
) as suiteJson
from testcases
group by testcases.testsuiteid,testcases."Name"
),
allSuites as (
select has.requirementid,
json_agg(
json_build_object('ts_id', suitesJson.testsuiteid,'name',s."Name" , 'test_cases', suitesJson.suiteJson )
) as suites
from suitesJson inner join "TestSuite" s on s.id = suitesJson.testsuiteid
inner join "Has" has on has.testsuiteid = s.id
group by has.requirementid
),
allRequirements as (
select json_agg(
json_build_object('req_id', r.id ,'req_description',r.description , 'test_suites', allSuites.suites )
) as suites
from allSuites inner join "Requirement" r on r.id = allSuites.requirementid
)
select * from allRequirements
Lo que hace es construir el objeto JSON en una pequeña colección de elementos y agregarlos en cada with clausulas.
Resultado:
[
{
"req_id": 1,
"req_description": "<character varying>",
"test_suites": [
{
"ts_id": 1,
"name": "TestSuite",
"test_cases": [
{
"tc_id": 1,
"tc_title": "TestCase"
},
{
"tc_id": 2,
"tc_title": "TestCase2"
}
]
},
{
"ts_id": 2,
"name": "TestSuite",
"test_cases": [
{
"tc_id": 2,
"tc_title": "TestCase2"
}
]
}
]
},
{
"req_id": 2,
"req_description": "<character varying> 2 ",
"test_suites": [
{
"ts_id": 2,
"name": "TestSuite",
"test_cases": [
{
"tc_id": 2,
"tc_title": "TestCase2"
}
]
}
]
}
]