Exportar tabela Postgres como json

Tente aqui para uma introdução básica para PostgreSQLe JSON.

Além disso, a documentação do PostgreSQL é muito boa, então experimente aqui . Confira a pretty_boolopção.

Sua pergunta original era "Existe uma maneira de exportar dados da tabela postgres como JSON". Você queria neste formato

{'id':1,'name':'David'}
{'id':2,'name':'James'}
...

Eu não tinha uma instância em execução, PostgreSQLentão baixei, compilei e instalei o 9.4.

Para responder a isso, primeiro CREATEed uma tabela (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');

Em seguida, para verificar:

test=# select * from fred;

 mary | jimmy |      paulie      
------+-------+------------------
    2 |    43 | asfasfasfd
    3 |   435 | ererere
    6 | 43343 | eresdfssfsfasfae

Então eu emiti 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=# 

Saí do psql e listei o arquivo myfile.

test=# \q
[pol@polhost inst]$ more myfile 
{"mary":2,"jimmy":43,"paulie":"asfasfasfd"}
{"mary":3,"jimmy":435,"paulie":"ererere"}
{"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"}
[pol@polhost inst]$

(você pode experimentar com a saída de

COPY (SELECT ROW_TO_JSON(t, TRUE)  -- <-- Note addition of "TRUE" here!

quando quiser).

Foi apontado por @offby1 que a saída (enquanto corresponde à pergunta do OP) não está correta JSON. @EvanCarroll apontou que \otambém é uma maneira de enviar para um arquivo, então combinei as soluções para essas duas imperfeições nesta declaração (com ajuda daqui ):

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

dá:

[pol@polhost 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)
[pol@polhost inst]$ 

FINALMENTE , há o \problema da barra invertida ( ) mencionado por @AdamGent em seu post. Isso foi um pouco complicado, mas é possível sem recorrer ao processamento pós-consulta. Voilà:

INSERT INTO fred VALUES (35, 5, 'wrew\sdfsd');
INSERT INTO fred VALUES (3, 44545, '\sdfs\\\sfs\\gf');

E usando REGEXP_REPLACE assim (observe o cast ::TEXT) remove as barras pretas em excesso.

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

dá:

[pol@polhost 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":"wrew\sdfsd"}
 {"mary":3,"jimmy":44545,"paulie":"\sdfs\\\sfs\\gf"}
(5 rows)
[pol@polhost inst]$ 

(ps Quanto ao comentário de @Zoltán - isso pode ser uma coisa de versão - incapaz de reproduzir!).

Se você estiver usando psql, não há motivo para usar \COPY.

\t
\a
\o file.json
SELECT row_to_json(r) FROM my_table AS r;

Este é o mesmo método que usamos para tirar png/jpgs/tifs do banco de dados com PostGIS para testes rápidos e também para gerar arquivos de script com extensões PostgreSQL.

Por favor, encontre abaixo a única resposta que gera um JSON realmente válido (ou seja, matriz de objetos).

\t
\a
\o data.json
select json_agg(t) FROM (SELECT * from table) t;

( fonte )

Para mim, a resposta de @Vérace não manteve os nomes das colunas, mas atribuiu nomes padrão ( f1, f2, etc.) em vez disso. Estou usando o PostgreSQL 9.1 com a extensão JSON .

Se você deseja exportar a tabela inteira, não há necessidade de uma subconsulta. Além disso, isso manterá os nomes das colunas. Usei a seguinte consulta:

COPY (SELECT row_to_json(t) FROM fred as t) to '/home/pol/Downloads/software/postgres/inst/myfile';

Vou adicionar uma ressalva especial à resposta de Verace . Você precisa fazer o pós-processamento no arquivo JSON de saída se tiver colunas de texto com caracteres de barra invertida:\ .

Caso contrário, você obterá duplicado ( \-> \\) na melhor das hipóteses e JSON completamente inválido na pior das hipóteses, ou seja:

Este:

{ "f1" : "crap\""}.

Torna-se

{ "f1" : "crap\\""}.

O que parece bom, mas é JSON completamente inválido.

Você pode substituir o \\into \por sed:

sed -i -e 's/\\\\/\\/g' PG_OUT_JSON_FILE.json

Do Postgres COPY onde eles mencionam:

Atualmente, COPY TO nunca emitirá uma sequência de barra invertida octal ou de dígitos hexadecimais, mas usa as outras sequências listadas acima para esses caracteres de controle. Qualquer outro caractere de barra invertida que não seja mencionado na tabela acima será usado para representar a si mesmo. No entanto, tome cuidado ao adicionar barras invertidas desnecessariamente, pois isso pode produzir acidentalmente uma string correspondente ao marcador de fim de dados (.) ou a string nula (\N por padrão). Essas strings serão reconhecidas antes que qualquer outro processamento de barra invertida seja feito.

É altamente recomendável que os aplicativos que geram dados COPY convertam as novas linhas de dados e os retornos de carro para as sequências \n e \r, respectivamente. Atualmente, é possível representar um retorno de carro de dados por uma barra invertida e um retorno de carro, e representar uma nova linha de dados por uma barra invertida e uma nova linha. No entanto, essas representações podem não ser aceitas em versões futuras. Eles também são altamente vulneráveis ​​à corrupção se o arquivo COPY for transferido entre máquinas diferentes (por exemplo, do Unix para o Windows ou vice-versa).

COPY TO terminará cada linha com uma nova linha no estilo Unix ("\n"). Em vez disso, os servidores executados no Microsoft Windows produzem retorno de carro/nova linha ("\r\n"), mas apenas para COPIAR para um arquivo de servidor; para consistência entre plataformas, COPY TO STDOUT sempre envia "\n" independentemente da plataforma do servidor. COPY FROM pode manipular linhas que terminam com novas linhas, retornos de carro ou retorno de carro/novas linhas. Para reduzir o risco de erro devido a novas linhas sem barra invertida ou retornos de carro que deveriam ser dados, COPY FROM reclamará se as terminações de linha na entrada não forem todas iguais.

Para uma solução genérica (MySQL, Postgres, SQLite..) e gratuita para a qual você não precisa instalar nenhum software (exceto o Docker), consulte https://github.com/function61/sql2json

Divulgação completa: eu escrevi esse software.

Isso é muito fácil de conseguir com spyql :

$ psql -U my_user -h my_host -c "SELECT * FROM my_users" --csv my_db | spyql "SELECT * FROM csv TO json"
{"id": 1, "name": "David"}
{"id": 2, "name": "James"}
{"id": 3, "name": "Joana"}
{"id": 4, "name": "Hellen"}

Estamos usando o psql para exportar uma consulta/tabela para CSV e, em seguida, o spyql para converter as linhas CSV em json.

Para o registro, criei uma tabela de amostra com o seguinte comando SQL:

CREATE TABLE my_users AS
SELECT *
FROM (VALUES (1, 'David'), (2, 'James'), (3, 'Joana'), (4, 'Hellen')) AS t(id, name);

Isenção de responsabilidade: eu sou o autor do spyql

Variante da resposta de @gunar-gessner com mas com arquivo de saída especificado embutido

\t
\a
select json_agg(t) FROM (SELECT * from table) t \g data.json