Eksportuj tabelę Postgres jako json

Czy istnieje sposób na eksport danych tabeli Postgres jako pliku Json do pliku? Potrzebuję, aby wynik był wiersz po wierszu, na przykład:

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

EDYCJA: wersja postgres: 9.3.4

Wypróbuj tutaj, aby uzyskać podstawowe wprowadzenie do PostgreSQLi JSON.

Ponadto dokumentacja PostgreSQL jest całkiem dobra, więc spróbuj tutaj . Sprawdź pretty_boolopcję.

Twoje pierwotne pytanie brzmiało: „Czy istnieje sposób na eksport danych tabeli postgres jako JSON”. Chciałeś go w tym formacie

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

Nie miałem uruchomionej instancji, PostgreSQLwięc pobrałem, skompilowałem i zainstalowałem 9.4.

Aby odpowiedzieć na to pytanie, najpierw CREATEedytowałem tabelę (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');

Następnie, aby sprawdzić:

test=# select * from fred;

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

Potem wydałem to polecenie

test=# COPY (SELECT ROW_TO_JSON(t) 
test(# FROM (SELECT * FROM fred) t) 
test-# TO '/paulstuff/sware/db/postgres/inst/myfile';
COPY 3
test=# 

Następnie zamknąłem psql i wymieniłem plik mój_plik.

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]$

(możesz eksperymentować z danymi wyjściowymi z

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

w wolnym czasie).

@ Offby1 wskazało, że dane wyjściowe (odpowiadające pytaniu PO) są nieprawidłowe JSON. @EvanCarroll zwrócił uwagę, że \ojest to również sposób na wyjście do pliku, dlatego w tym zestawieniu połączyłem rozwiązania dla tych dwóch czarnuchów (z pomocą tutaj ):

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

daje:

[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]$ 

WRESZCIE istnieje \problem odwrotnego ukośnika ( ), o którym wspomniał @AdamGent w swoim poście. To było trochę trudne, ale jest to możliwe bez uciekania się do przetwarzania po zapytaniu. Voilà:

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

A użycie REGEXP_REPLACE w ten sposób (zwróć uwagę na obsadę :: TEKST) usuwa nadmiar czarnych plam.

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

daje:

[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 Jeśli chodzi o komentarz @ Zoltána - może to być wersja - nie można go odtworzyć!).

Jeśli używasz, psqlnie ma żadnego powodu, aby z niego korzystać \COPY.

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

Jest to ta sama metoda, której używamy do pobierania png / jpgs / tifs z bazy danych za pomocą PostGIS do szybkich testów, a także do generowania plików skryptów z rozszerzeniami PostgreSQL.

Dla mnie odpowiedź @ Verace za nie utrzymać nazwy kolumn, ale przypisane domyślne nazwy ( f1, f2, itd.) Zamiast. Używam PostgreSQL 9.1 z rozszerzeniem JSON .

Jeśli chcesz wyeksportować całą tabelę, nie ma potrzeby podkwerendy. Ponadto zachowa nazwy kolumn. Użyłem następującego zapytania:

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

Dodam specjalne zastrzeżenie do odpowiedzi Verace'a . Trzeba zrobić pocztowy przetwarzanie na wysyłanego pliku JSON, jeśli masz kolumny tekstu ze znakami backslash:\ .

W przeciwnym razie otrzymasz duplikat ( \-> \\) w najlepszym wypadku i całkowicie niepoprawny JSON w gorszym stanie, tj .:

To:

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

Staje się

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

Co wygląda dobrze, ale jest całkowicie niepoprawny JSON.

Możesz zamienić na \\na \sed:

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

Z Postgres COPY, w którym mówią o tym:

Obecnie funkcja COPY TO nigdy nie będzie emitować ciągów odwrotnych ukośników w postaci liczb ósemkowych lub szesnastkowych, ale w przypadku tych znaków kontrolnych używa innych sekwencji wymienionych powyżej. Każdy inny znak odwrócony, który nie jest wymieniony w powyższej tabeli, będzie reprezentowany. Uważaj jednak na niepotrzebne dodawanie ukośników odwrotnych, ponieważ może to przypadkowo wygenerować ciąg pasujący do znacznika końca danych (.) Lub ciąg zerowy (domyślnie \ N). Ciągi te zostaną rozpoznane przed wykonaniem jakiegokolwiek innego przetwarzania odwrotnego ukośnika.

Zdecydowanie zaleca się, aby aplikacje generujące dane COPY przekształcały nowe wiersze danych i powrót karetki odpowiednio do sekwencji \ n i \ r. Obecnie możliwe jest reprezentowanie powrotu karetki danych za pomocą ukośnika odwrotnego i powrotu karetki oraz reprezentowanie nowej linii danych za pomocą ukośnika odwrotnego i nowej linii. Jednak te oświadczenia mogą nie zostać zaakceptowane w przyszłych wydaniach. Są również bardzo podatne na uszkodzenie, jeśli plik COPY jest przesyłany między różnymi komputerami (na przykład z systemu Unix na system Windows lub odwrotnie).

COPY TO zakończy każdy wiersz uniksową linią w stylu uniksowym („\ n”). Serwery działające w systemie Microsoft Windows zamiast tego wyprowadzają znak powrotu / nowego wiersza („\ r \ n”), ale tylko do KOPIOWANIA do pliku serwera; dla zachowania spójności na różnych platformach, COPY TO STDOUT zawsze wysyła „\ n” niezależnie od platformy serwera. Funkcja COPY FROM może obsługiwać linie kończące się znakami nowej linii, zwrotów karetki lub powrotu / linii karetki. Aby zmniejszyć ryzyko błędu wynikającego z nieoznaczonych wstecz linii nowego wiersza lub zwrotów karetki, które miały być danymi, COPY FROM będzie narzekać, jeśli zakończenia linii na wejściu nie będą takie same.

Aby uzyskać ogólne (MySQL, Postgres, SQLite ..) i bezpłatne rozwiązanie, dla którego nie musisz instalować żadnego oprogramowania (oprócz Docker), zobacz https://github.com/function61/sql2json

Pełne ujawnienie: napisałem to oprogramowanie.

Jest to jedyna metoda, która wyprowadza prawidłowy JSON (tablica obiektów).

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

( źródło )