How to create a table dynamically based on json data? 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-&gt;'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.
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-&gt;&gt;'tablename', c.cols)
   FROM   public.json_metadata m
   CROSS  JOIN LATERAL (
      SELECT string_agg(quote_ident(col-&gt;&gt;'name')
                        || ' ' ||  (col-&gt;&gt;'datatype')::regtype, ', ') AS cols
      FROM   json_array_elements(metadata-&gt;'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:


How to do this binary appearance analysis
SQL injection in Postgres functions vs prepared queries
Table name as a PostgreSQL function parameter