json_build_object

PostgreSQL 9.2 row_to_json() con uniones anidadas

Question

Estoy tratando de asignar los resultados de una consulta a JSON utilizando la función row_to_json() que se agregó en PostgreSQL 9.2.

Tengo problemas para encontrar la mejor forma de representar las filas unidas como objetos anidados (relaciones 1: 1)

Esto es lo que he intentado (código de configuración: tablas, datos de muestra, seguido de consulta):

-- some test tables to start out with:
create table role_duties (
    id serial primary key,
    name varchar
);

create table user_roles (
    id serial primary key,
    name varchar,
    description varchar,
    duty_id int, foreign key (duty_id) references role_duties(id)
);

create table users (
    id serial primary key,
    name varchar,
    email varchar,
    user_role_id int, foreign key (user_role_id) references user_roles(id)
);

DO $$
DECLARE duty_id int;
DECLARE role_id int;
begin
insert into role_duties (name) values (''Script Execution'') returning id into duty_id;
insert into user_roles (name, description, duty_id) values (''admin'', ''Administrative duties in the system'', duty_id) returning id into role_id;
insert into users (name, email, user_role_id) values (''Dan'', ''[email protected]'', role_id);
END$$;

La consulta en sí:

select row_to_json(row)
from (
    select u.*, ROW(ur.*::user_roles, ROW(d.*::role_duties)) 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
) row;

Descubrí que si utilizaba ROW() , podía separar los campos resultantes en un objeto secundario, pero parece estar limitado a un único nivel. No puedo insertar más declaraciones AS XXX , como creo que debería necesitar en este caso.

Se me proporcionan nombres de columna, porque selecciono el tipo de registro apropiado, por ejemplo con ::user_roles , en el caso de los resultados de esa tabla.

Esto es lo que devuelve esa consulta:

{
   "id":1,
   "name":"Dan",
   "email":"[email protected]",
   "user_role_id":1,
   "user_role":{
      "f1":{
         "id":1,
         "name":"admin",
         "description":"Administrative duties in the system",
         "duty_id":1
      },
      "f2":{
         "f1":{
            "id":1,
            "name":"Script Execution"
         }
      }
   }
}

Lo que quiero hacer es generar JSON para las uniones (de nuevo 1: 1 está bien) de forma que pueda agregar uniones, y hacer que se representen como objetos secundarios de los padres a los que se unen, es decir, como los siguientes:

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

Cualquier ayuda es apreciada. Gracias por leer.

Answer 1

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 prolijo debido a la necesidad de nombrar todos los campos de forma explícita:

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, sigue leyendo.

No está limitado a una sola fila, es un poco doloroso. No puede alias los tipos de fila compuestos utilizando 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"
    }
  }
}

array_to_json(array_agg(...)) utilizar array_to_json(array_agg(...)) cuando tenga una relación 1: many, por cierto.

La consulta anterior idealmente debe 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 el constructor ROW de PostgreSQL no acepta alias de columna AS . Tristemente.

Afortunadamente, optimizan lo mismo. Compare los planes:

• La versión de subconsulta anidada ; vs
• Este último anida la versión del constructor ROW con los alias eliminados para que se ejecute

Debido a que los CTE son vallas de optimización, la reformulación de la versión de subconsulta anidada para utilizar CTE encadenados ( WITH expresiones) puede no funcionar tan bien y no dará como resultado el mismo plan. En este caso, estás atascado con subconsultas anidadas feas hasta que consigamos algunas mejoras en row_to_json o una manera de anular los nombres de columna en un constructor ROW más directa.

De todos modos, en general, el principio es que cuando desee crear un objeto json con las columnas a, b, c , y desee que simplemente pueda escribir la sintaxis ilegal:

ROW(a, b, c) AS outername(name1, name2, name3)

en su lugar, puede usar subconsultas escalares que devuelvan valores de tipo 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 valores json sin json adicionales, por ejemplo, si coloca la salida de json_agg dentro de row_to_json , el resultado interno json_agg no se obtendrá como una cadena, sino que 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 producto json_agg , [{"a":1,"b":2}, {"a":1,"b":2}] , no se ha escapado de nuevo, como sería el text .

Esto significa que puede componer operaciones json para construir filas, no siempre tiene que crear tipos compuestos de PostgreSQL enormemente complejos y luego llamar a row_to_json en la salida.

Answer 2

Mi sugerencia para la mantenibilidad 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é usuarios. *, No tendré que actualizar esta función si necesito actualizar la vista para incluir más campos en un registro de usuario.