How to create a table dynamically based on json data?

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Related:

Method 2

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply