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.

• Michael Kutz: How do you know the source didnt misspell data_type? Or was that datatype or case sensitive dataType or DATATYPE or ..... ?? You get the picture. Bad Idea
• RBB: Probably its bad idea. Just try something like this. select 'create table ' || (metadata->'tablename') || '(' || (metadata->'columns'-> 0 -> 'name') || ' ' || (metadata->'columns'-> 0 -> 'datatype') || ', ' || (metadata->'columns'-> 1 -> 'name') || ' ' || (metadata->'columns'-> 1 -> 'datatype') || ');' from json_tables Yeah, you should find solution to replace column index position since It can be n number of column and I couldn't find any.

Answer 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:

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

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

• virtual void: I was able to create the function as above.