Adriana, parte de nuestro equipo de trabajo, nos hizo el favor de crear esta crónica porque conoce perfectamente el tema.
Solución:
Pruebe aquí para obtener una introducción básica a PostgreSQL y JSON.
Además, la documentación de PostgreSQL es bastante buena, así que pruébelo aquí. Revisar la pretty_bool opción.
Su pregunta original era “¿Hay alguna manera de exportar los datos de la tabla de postgres como JSON“. Lo querías en este formato
'id':1,'name':'David'
'id':2,'name':'James'
...
No tenía una instancia en ejecución de PostgreSQL así que descargué, compilé e instalé 9.4.
Para responder a esto, primero CREATEed una mesa (fred)
CREATE TABLE fred (mary INT, jimmy INT, paulie VARCHAR(20));
INSERT INTO fred VALUES (2, 43, 'asfasfasfd' );
INSERT INTO fred VALUES (3, 435, 'ererere' );
INSERT INTO fred VALUES (6, 43343, 'eresdfssfsfasfae');
Luego, para verificar:
test=# select * from fred;
mary | jimmy | paulie
------+-------+------------------
2 | 43 | asfasfasfd
3 | 435 | ererere
6 | 43343 | eresdfssfsfasfae
Entonces emití este comando
test=# COPY (SELECT ROW_TO_JSON(t)
test(# FROM (SELECT * FROM fred) t)
test-# TO '/paulstuff/sware/db/postgres/inst/myfile';
COPY 3
test=#
Luego salí de psql y enumeré el archivo myfile.
test=# q
[[email protected] inst]$ more myfile
"mary":2,"jimmy":43,"paulie":"asfasfasfd"
"mary":3,"jimmy":435,"paulie":"ererere"
"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"
[[email protected] inst]$
(puede experimentar con la salida de
COPY (SELECT ROW_TO_JSON(t, TRUE) -- <-- Note addition of "TRUE" here!
en su tiempo libre).
@ Offby1 señaló que la salida (aunque corresponde a la pregunta del OP) no es correcta JSON. @EvanCarroll señaló que o también es una forma de generar un archivo, así que combiné las soluciones a estos dos problemas en esta declaración (con la ayuda de aquí):
test=# o out.json
test=# SELECT array_to_json(array_agg(fred), FALSE) AS ok_json FROM fred;
-- <-- "TRUE" here will produce plus
("+) signs in the output. "FALSE"
is the default anyway.
test=# o
da:
[[email protected] inst]$ more out.json
ok_json
----------------------------------------------------------------------------------------------------------------------------------------------
["mary":2,"jimmy":43,"paulie":"asfasfasfd","mary":3,"jimmy":435,"paulie":"ererere","mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"]
(1 row)
[[email protected] inst]$
FINALMENTE, está la barra invertida () problema al que alude @AdamGent en su publicación. Esto fue un poco complicado, pero es posible sin recurrir al procesamiento posterior a la consulta. Voilà:
INSERT INTO fred VALUES (35, 5, 'wrewsdfsd');
INSERT INTO fred VALUES (3, 44545, 'sdfs\sfs\gf');
Y el uso de REGEXP_REPLACE así (tenga en cuenta el elenco :: TEXT) elimina el exceso de barras negras.
test=# o slash.json
test=# SELECT REGEXP_REPLACE(ROW_TO_JSON(t)::TEXT, '\\', '\', 'g')
test=# FROM (SELECT * FROM fred) AS t; -- I found that using a CTE was helpful for legibility
test=# o
test=# q
da:
[[email protected] inst]$ more slash.json
regexp_replace
------------------------------------------------------
"mary":2,"jimmy":43,"paulie":"asfasfasfd"
"mary":3,"jimmy":435,"paulie":"ererere"
"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"
"mary":35,"jimmy":5,"paulie":"wrewsdfsd"
"mary":3,"jimmy":44545,"paulie":"sdfs\sfs\gf"
(5 rows)
[[email protected] inst]$
(PD: En cuanto al comentario de @Zoltán, esto puede ser una versión, ¡no se puede reproducir!).
Si estas usando psql entonces no hay razón para usar COPY en absoluto.
t
a
o file.json
SELECT row_to_json(r) FROM my_table AS r;
Este es el mismo método que usamos para sacar png / jpgs / tifs de la base de datos con PostGIS para pruebas rápidas, y también para generar archivos de script con extensiones PostgreSQL.
Para mí, la respuesta de @ Vérace no mantuvo los nombres de las columnas, pero asignó nombres predeterminados (f1, f2, etc.) en su lugar. Estoy usando PostgreSQL 9.1 con la extensión JSON.
Si desea exportar la tabla completa, no es necesaria una subconsulta. Además, este voluntad mantener los nombres de las columnas. Usé la siguiente consulta:
COPY (SELECT row_to_json(t) FROM fred as t) to '/home/pol/Downloads/software/postgres/inst/myfile';
Si te ha resultado de utilidad nuestro artículo, sería de mucha ayuda si lo compartieras con más juniors de este modo contrubuyes a extender nuestra información.