0

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 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 – Michael Kutz May 7 '18 at 21:46
  • 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. – Spike May 8 '18 at 6:56
  • So do you have your answer? – Erwin Brandstetter Aug 13 '18 at 7:07
2

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:

0
-- 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;
  • I was able to create the function as above. – virtual void Nov 9 '18 at 21:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.