主页 / dba / 问题 / 206082
Accepted
virtual void
Asked: 2018-05-08 11:17:00 +0800 CST

如何根据json数据动态创建表？

  • 772

我有一个作为文件获取的 json，我已将其加载到 Postgres 数据库中。这是json数据：

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

}

现在我必须动态创建一个表，我正在考虑在 Postgres 中编写一个函数来做到这一点。因此，该表将命名为 test，其中包含 2 个字段，一个为字符串，另一个为 bigint。

我可以通过如下选择来获取表名：

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

但是，我很难让所有嵌套的列名形成一个 create table 语句。

1-你将如何去做，任何内置的 Postgres 函数来提取它。

2- Postgres 函数是解决此问题的最佳方法，还是我应该用 python（我必须学习 Python）或 shell 脚本编写它。

列数不会是固定的，不同的 json 文件会有不同的列数。

postgresql functions

2 个回答

  1. 为此，您需要动态 SQL，这会带来 SQL 注入的危险。
    但是，如果操作得当，这对 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$;

    表名和列名被视为区分大小写。（您可能需要小写字母。）类型名称被视为不区分大小写，并且任何有效的类型名称都有效。

    当然，这会为您显示的不存在的数据类型引发异常。 String尝试使用text。

    注意使用format()、对象标识符类型regclass、 、子查询quote_ident()中的列聚合以及执行动态 SQL的命令。LATERALDO

    有关的：

    • 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

相关问题