How to create a table dynamically based on json data?

Question

I have a json that I get as a file, I have loaded that in the Postgres database. Here is the json data:

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

}

Now I have to create a table dynamically, I am thinking of writing a function in Postgres to do that. So the table would be named test with 2 fields one as string and another as bigint.

I am able to get the table name by doing a select as below:

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

However, I am having difficulty getting all the nested column names to form a create table statement.

1- How would you go about doing that, any built in Postgres functions to extract that.

2- Is a Postgres function the best way to approach this problem, or should I write this in python (I will have to learn Python) or shell script.

The number of columns would not be fixed, different json files will have different number of columns.

Erwin Brandstetter · Answer 1 · 2018-08-13T07:07:06.317

You need dynamic SQL for that, which carries the hazard of SQL injection.
However, done properly, this is safe against 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$;

Table and column names are treated as case-sensitive. (You may want lower-case instead.) The type name is treated as case-insensitive, and any valid type name works.

Of course, this would raise an exception for the non-existent data type String you display. Try with text instead.

Note the use of format(), the object identifier type regclass, quote_ident(), the aggregation of columns in the LATERAL subquery and the DO command to execute dynamic SQL.

score 0 · Accepted Answer · answered Nov 09 '18 at 21:37

-- 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;

How to create a table dynamically based on json data?

2 Answers2

Linked