Início / dba / Perguntas / 312245
Accepted
bqback
Asked: 2022-05-18 06:27:10 +0800 CST

Importando CSV com uma matriz json de strings em uma das colunas

  • 772

As colunas do CSV são do formato text(pode incluir linhas vazias), datee um array JSON de strings (algo como ['a', 'b', 'c']. Eu tenho tentado copiar esse CSV para uma tabela PostgreSQL (usando psycopg2's copy_expert, que simplesmente executa o SQL dado COPYcomando, se isso importa)

A tabela é criada com

CREATE TABLE posts(
    id SERIAL PRIMARY KEY,
    text TEXT NOT NULL,
    created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    rubrics jsonb[] NOT NULL
);

e o comando de cópia é

COPY posts(text, created_date, rubrics)
FROM STDIN
WITH CSV HEADER

onde STDINestá o arquivo CSV.

O erro que recebo é

malformed array literal: "['a', 'b', 'c']"
DETAIL:  "[" must introduce explicitly-specified array dimensions.
CONTEXT:  COPY posts, line 15, column rubrics: "['a', 'b', 'c']"

Eu tentei todos os 4 tipos de dados relacionados a JSON ( jsone jsonbcom e sem []ou [3]), incluindo colchetes produz o erro acima, enquanto omiti-los ( rubrics jsonb NOT NULLao criar) fornece um novo:

invalid input syntax for type json
DETAIL:  Token "'" is invalid.
CONTEXT:  JSON data, line 1: ['...
COPY posts, line 15, column rubrics: "['a', 'b', 'c']"

Tenho algum recurso além de corrigir manualmente o .csv para usar {}em vez de []copiá-lo? Parece que sim, mas não consegui encontrar nada além de algumas perguntas, mas não totalmente relevantes.

Atualização sobre os comentários

Alterei a criação da tabela para

CREATE TABLE posts(
    id SERIAL PRIMARY KEY,
    text TEXT NOT NULL,
    created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    rubrics jsonb NOT NULL
);

e deixou uma única entrada no CSV para testar, então fica assim

text,created_date,rubrics
"Lorem

Ipsum

Test",2019-07-25 12:42:13,'["f", "o", "o"]'

Coisas que eu tentei e erros que eu tenho

Usando o CSV como está:

 extra data after last expected column
CONTEXT:  COPY posts, line 6: ""Lorem

Ipsum

Test",2019-07-25 12:42:13,'["f", "o", "o"]'"

Um par adicional de ""ao redor da matriz ( "'["f", "o", "o"]'")

 invalid input syntax for type json
DETAIL:  Token "'" is invalid.
CONTEXT:  JSON data, line 1: '...
COPY posts, line 6, column rubrics: "'[f, o, o]'"

Sem aspas (apenas o ["f", "o", "o"])

extra data after last expected column
CONTEXT:  COPY posts, line 6: ""Lorem

Ipsum

Test",2019-07-25 12:42:13,["f", "o", "o"]"

Aspas duplas em vez de aspas simples ( "["f", "o", "o"]")

invalid input syntax for type json
DETAIL:  Token "f" is invalid.
CONTEXT:  JSON data, line 1: [f...
COPY posts, line 6, column rubrics: "[f, o, o]"

Aspas duplas fora, aspas simples dentro ( "['f', 'o', 'o']")

invalid input syntax for type json
DETAIL:  Token "'" is invalid.
CONTEXT:  JSON data, line 1: ['...
COPY posts, line 6, column rubrics: "['f', 'o', 'o']"

Poderia ser um problema com a biblioteca Python que estou usando afinal?

postgresql json

1 respostas

  1. Best Answer

    JSON é uma camada de encapsulamento com uma sintaxe padronizada que o campo JSON em sua entrada deve respeitar.

    CSV é outra camada de encapsulamento que se aplica por cima, com uma sintaxe mais ou menos padronizada na rfc 4180 . Conforme mencionado na RFC, programas que lêem ou escrevem CSV nem sempre obedecem estritamente a todas essas regras, mas em relação às regras de citação, o Postgres está em conformidade.

    JSON usa aspas duplas em literais de string, portanto, todas as tentativas na questão de usar aspas simples em strings são inválidas para o analisador JSON

    O CSV exige que as aspas duplas sejam duplicadas, veja as regras nº 5 e nº 7 da rfc 4180, portanto, todas as tentativas de usar aspas duplas em JSON sem dobrá-las para CSV ou sem colocar o campo inteiro entre aspas duplas são inválidas para o analisador CSV.

    1. Cada campo pode ou não estar entre aspas duplas (no entanto, alguns programas, como o Microsoft Excel, não usam aspas duplas). Se os campos não estiverem entre aspas duplas, as aspas duplas podem não aparecer dentro dos campos
    1. Se aspas duplas forem usadas para incluir campos, uma aspa dupla que aparece dentro de um campo deve ser precedida por outra aspa dupla

    Seu exemplo ligeiramente editado para estar em conformidade com CSV e JSON funciona:

    CREATE TABLE posts(
    id SERIAL PRIMARY KEY,
    text TEXT NOT NULL,
    created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    rubrics jsonb NOT NULL
);

COPY posts(text, created_date, rubrics)
FROM STDIN WITH CSV;
foo,2022-05-17,"[""a"", ""b"", ""c""]"
\.

select * from posts;
 id | text |    created_date     |     rubrics     
----+------+---------------------+-----------------
  1 | foo  | 2022-05-17 00:00:00 | ["a", "b", "c"]

    Se você quisesse um array Postgres de objetos JSON em rubrics, isso seria mais complicado, pois expressar o array Postgres como texto requer outra camada de encapsulamento com suas próprias regras de sintaxe , então a produção de dados válidos para importar com COPY teria que fazer

    Citação JSON -> Citação de array postgres -> Citação CSV

    Mas, como visto em seu exemplo, parece que você não precisa de uma matriz Postgres de objetos JSON como uma coluna, mas sim de um único valor JSON que contém uma matriz JSON.

    • 0

relate perguntas