Saltar al contenido

¿PostgreSQL devuelve el conjunto de resultados como matriz JSON?

Solución:

TL; DR

SELECT json_agg

para una matriz JSON de objetos, y

SELECT
    json_build_object(
        'a', json_agg(t.a),
        'b', json_agg(t.b)
    )
FROM t

para un objeto JSON de matrices.

Lista de objetos

Esta sección describe cómo generar una matriz JSON de objetos, y cada fila se convierte en un solo objeto. El resultado se ve así:

[{"a":1,"b":"value1"},{"a":2,"b":"value2"},{"a":3,"b":"value3"}]

9.3 y más

los json_agg La función produce este resultado fuera de la caja. Automáticamente descubre cómo convertir su entrada en JSON y la agrega en una matriz.

SELECT json_agg

No hay jsonb (introducido en 9.4) versión de json_agg. Puede agregar las filas en una matriz y luego convertirlas:

SELECT to_jsonb(array_agg

o combinar json_agg con un yeso:

SELECT json_agg

Mis pruebas sugieren que agregarlos en una matriz primero es un poco más rápido. Sospecho que esto se debe a que el elenco tiene que analizar todo el resultado JSON.

9.2

9.2 no tiene el json_agg o to_json funciones, por lo que debe utilizar la antigua array_to_json:

SELECT array_to_json(array_agg

Opcionalmente, puede incluir un row_to_json llamar en la consulta:

SELECT array_to_json(array_agg(row_to_json

Esto convierte cada fila en un objeto JSON, agrega los objetos JSON como una matriz y luego convierte la matriz en una matriz JSON.

No pude discernir ninguna diferencia de rendimiento significativa entre los dos.

Objeto de listas

Esta sección describe cómo generar un objeto JSON, donde cada clave es una columna en la tabla y cada valor es una matriz de los valores de la columna. Es el resultado que se ve así:

{"a":[1,2,3], "b":["value1","value2","value3"]}

9.5 y más

Podemos aprovechar el json_build_object función:

SELECT
    json_build_object(
        'a', json_agg(t.a),
        'b', json_agg(t.b)
    )
FROM t

También puede agregar las columnas, crear una sola fila y luego convertirla en un objeto:

SELECT to_json(r)
FROM (
    SELECT
        json_agg(t.a) AS a,
        json_agg(t.b) AS b
    FROM t
) r

Tenga en cuenta que es absolutamente necesario asignar un alias a las matrices para garantizar que el objeto tenga los nombres deseados.

Cuál es más claro es cuestión de opinión. Si usa el json_build_object función, recomiendo encarecidamente poner un par clave / valor en una línea para mejorar la legibilidad.

También podrías usar array_agg en lugar de json_agg, pero mis pruebas indican que json_agg es un poco más rápido.

No hay jsonb versión de la json_build_object función. Puede agregar en una sola fila y convertir:

SELECT to_jsonb(r)
FROM (
    SELECT
        array_agg(t.a) AS a,
        array_agg(t.b) AS b
    FROM t
) r

A diferencia de otras consultas para este tipo de resultado, array_agg parece ser un poco más rápido cuando se usa to_jsonb. Sospecho que esto se debe al análisis general y la validación del resultado JSON de json_agg.

O puede usar un elenco explícito:

SELECT
    json_build_object(
        'a', json_agg(t.a),
        'b', json_agg(t.b)
    )::jsonb
FROM t

los to_jsonb la versión te permite evitar el yeso y es más rápida, según mis pruebas; de nuevo, sospecho que esto se debe a la sobrecarga de analizar y validar el resultado.

9.4 y 9.3

los json_build_object La función era nueva en 9.5, por lo que debe agregar y convertir a un objeto en versiones anteriores:

SELECT to_json(r)
FROM (
    SELECT
        json_agg(t.a) AS a,
        json_agg(t.b) AS b
    FROM t
) r

o

SELECT to_jsonb(r)
FROM (
    SELECT
        array_agg(t.a) AS a,
        array_agg(t.b) AS b
    FROM t
) r

dependiendo de si quieres json o jsonb.

(9.3 no tiene jsonb.)

9.2

En 9.2, ni siquiera to_json existe. Debes usar row_to_json:

SELECT row_to_json(r)
FROM (
    SELECT
        array_agg(t.a) AS a,
        array_agg(t.b) AS b
    FROM t
) r

Documentación

Busque la documentación de las funciones JSON en funciones JSON.

json_agg está en la página de funciones agregadas.

Diseño

Si el rendimiento es importante, asegúrese de comparar sus consultas con su propio esquema y datos, en lugar de confiar en mis pruebas.

Si es un buen diseño o no, realmente depende de su aplicación específica. En términos de mantenibilidad, no veo ningún problema en particular. Simplifica el código de su aplicación y significa que hay menos que mantener en esa parte de la aplicación. Si PG puede brindarle exactamente el resultado que necesita, la única razón por la que puedo pensar para no usarlo sería por consideraciones de rendimiento. No reinventes la rueda y todo.

Nulos

Las funciones agregadas suelen devolver NULL cuando operan sobre cero filas. Si esta es una posibilidad, es posible que desee utilizar COALESCE para evitarlos. Un par de ejemplos:

SELECT COALESCE(json_agg

O

SELECT to_jsonb(COALESCE(array_agg

Gracias a Hannes Landeholm por señalar esto

Además, si desea un campo seleccionado de la tabla y agregado, entonces como matriz.

SELECT json_agg(json_build_object('data_a',a,
                                  'data_b',b,
))  from t;

El resultado llegará.

 [{'data_a':1,'data_b':'value1'}
  {'data_a':2,'data_b':'value2'}]
¡Haz clic para puntuar esta entrada!
(Votos: 0 Promedio: 0)


Tags :

Utiliza Nuestro Buscador

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *