Início / dba / Perguntas / 206082
Accepted
virtual void
Asked: 2018-05-08 11:17:00 +0800 CST

Como criar uma tabela dinamicamente com base em dados json?

  • 772

Eu tenho um json que recebo como um arquivo, carreguei isso no banco de dados Postgres. Aqui estão os dados json:

{
"tablename": "test",
"columns": [
    {
        "name": "field1",
        "datatype": "BigInt"
    },
    {
        "name": "field2",
        "datatype": "String"
    }
]

}

Agora tenho que criar uma tabela dinamicamente, estou pensando em escrever uma função no Postgres para fazer isso. Então a tabela seria nomeada teste com 2 campos um como string e outro como bigint.

Eu sou capaz de obter o nome da tabela fazendo um select como abaixo:

select (metadata->'tablename') from public.json_metadata;

No entanto, estou tendo dificuldade em obter todos os nomes de colunas aninhadas para formar uma instrução de criação de tabela.

1- Como você faria isso, qualquer função do Postgres embutida para extrair isso.

2- Uma função Postgres é a melhor maneira de abordar este problema, ou devo escrever isso em python (terei que aprender Python) ou script de shell.

O número de colunas não seria fixo, diferentes arquivos json terão um número diferente de colunas.

postgresql functions

2 respostas

  1. Você precisa de SQL dinâmico para isso, o que traz o risco de injeção de SQL.
    No entanto, feito corretamente, isso é seguro contra SQLi:

    DO
$do$
BEGIN

EXECUTE (
   SELECT format('CREATE TABLE %I(%s)', metadata->>'tablename', c.cols)
   FROM   public.json_metadata m
   CROSS  JOIN LATERAL (
      SELECT string_agg(quote_ident(col->>'name')
                        || ' ' ||  (col->>'datatype')::regtype, ', ') AS cols
      FROM   json_array_elements(metadata->'columns') col
      ) c
   );
END
$do$;

    Os nomes de tabela e coluna são tratados com distinção entre maiúsculas e minúsculas. (Você pode preferir letras minúsculas.) O nome do tipo é tratado como não diferenciando maiúsculas de minúsculas e qualquer nome de tipo válido funciona.

    Obviamente, isso geraria uma exceção para o tipo de dados inexistente Stringexibido. Tente com textem vez disso.

    Observe o uso de format(), o tipo de identificador de objeto regclass, quote_ident(), a agregação de colunas na LATERALsubconsulta e o DOcomando para executar SQL dinâmico.

    Relacionado:

    • 2
  2. Best Answer
    -- Read json fields and create the create table statement
        -- Check if primarykey is null
        if v_pkey::text = 'null'
        then
            v_create_stmt := 'SELECT format(''CREATE TABLE IF NOT EXISTS %s (%s);'', y.tname2, y.cols)
                    FROM
                        (select s.tname1 as tname2,
                        string_agg(common.f_remove_non_alphanumerics(lower((s.details ->> ''dbname'')::text), '''') || '' '' || lower((s.details->>''datatype'')::text) || '' '' || case when (s.details->>''isRequired'') = ''true''
                        then ''not null''
                        else ''null''
                        end, '', '') AS cols
                        from (
                            select schema ||''.''|| tname as tname1, json_array_elements(colname) as details
                            from common.json_metadata
                        ) s
                        group by s.tname1
                    ) y;';
        else
            v_create_stmt := 'SELECT format(''CREATE TABLE IF NOT EXISTS %s (%s, PRIMARY KEY (%s));'', y.tname2, y.cols, y.pkey)
                  FROM
                      (select s.tname1 as tname2,
                      string_agg(common.f_remove_non_alphanumerics(lower((s.details ->> ''dbname'')::text), '''') || '' '' || lower((s.details->>''datatype'')::text) || '' '' || case when (s.details->>''isRequired'') = ''true''
                      then ''not null''
                      else ''null''
                      end, '', '') AS cols,
                      string_agg( case when (s.pkey_dtl ->> ''namepk'')::text is not null
                      then common.f_remove_non_alphanumerics((s.pkey_dtl ->> ''namepk'')::text, '''')
                      else null
                      end, '', '') AS pkey
                      from (
                          select schema ||''.''|| tname as tname1, json_array_elements(colname) as details, json_array_elements(primarykey) as pkey_dtl
                          from common.json_metadata
                      ) s
                      group by s.tname1
                  ) y;';
        end if;
    • 0

relate perguntas