json - 自动创建表并从json文件中插入数据

Question

我有将近 50 个 json 文件。我想基于这些文件创建 Postgres 数据库。每个文件包含一张表的数据。文件不是很大(最多几千条记录)。来自customers.json的示例数据(其实还有更多的字段，我已经简化了):

[ 
    {
    "Id": 55948,
    "FullName": "Full name #1",
    "Address": "Address #1",
    "Turnover": 120400.5,
    "DateOfRegistration": "2014-02-13",
    "LastModifiedAt": "2015-11-03 12:04:44" },
    {
    "Id": 55949,
    "FullName": "Full name %2",
    "Address": "Address #2",
    "Turnover": 120000.0,
    "DateOfRegistration": "2012-12-01",
    "LastModifiedAt": "2015-11-04 17:14:21" }
]

我尝试编写一个函数来创建一个表并将所有数据插入其中。我的尝试基于使用 EXECUTE 的动态查询:

CREATE OR REPLACE FUNCTION import_json(table_name text, data json)
RETURNS VOID AS $$
DECLARE
    query text;
    colname text;
BEGIN
    query := 'CREATE TABLE ' || table_name || ' (';
    FOR colname IN SELECT json_object_keys(data->0)
    LOOP query := query || lower(colname) || ' text,';
    END LOOP;
    query := rtrim(query, ',') || ');';
    EXECUTE(query);
END $$ LANGUAGE plpgsql;

我的函数创建了一个包含预期列名称的表，但所有列都是文本类型。问题是我不知道如何定义正确的列类型。

json 文件格式正确，包含整数、数字、日期、时间戳和文本值。我想拿到 table :

CREATE TABLE customers (
  id integer, 
  fullname text, 
  address text, 
  turnover numeric, 
  date_of_registration date, 
  last_modified_at timestamp);

主要问题:如何识别生成表中的列类型？

此外，是否有一种简单的方法可以将 Pascal 转换为下划线表示法(“DateOfRegistration”->“date_of_registration”)？

Answer 1

您可以通过检查值来确定列的类型。下面的函数从一对(键，值)格式化列的定义。它使用 regex pattern matching .它还将列的名称转换为带下划线的符号(使用 regexp_replace() 函数)。当然，如果值为NULL，该函数将无法正常工作，因此您必须检查第一个json记录是否具有所有非空值。

create or replace function format_column(ckey text, cval text)
returns text language sql immutable as $$
    select format('%s %s',
        lower(regexp_replace(ckey, '(.)([A-Z])', '\1_\2', 'g')),
        case 
            when cval ~ '^[\+-]{0,1}\d+$' then 'integer'
            when cval ~ '^[\+-]{0,1}\d*\.\d+$' then 'numeric'
            when cval ~ '^"\d\d\d\d-\d\d-\d\d"$' then 'date'
            when cval ~ '^"\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d"$' then 'timestamp'
            else 'text' 
        end
    )
$$;

select format_column(key, value)
from (
    values 
        ('Id', '55948'),
        ('FullName', '"Full name #1"'),
        ('Turnover', '120400.5'),
        ('DateOfRegistration', '"2014-02-13"')
    ) val(key, value);

       format_column       
---------------------------
 id integer
 full_name text
 turnover numeric
 date_of_registration date
(4 rows)        

在主函数中你不需要变量或循环。使用 format()使用参数和 string_agg() 格式化字符串的函数创建文本列表。由于您同时需要键和值，因此请使用 json_each() 而不是 json_object_keys()。在第二个查询中，您可以使用 row_number()以确保聚合值列表被划分为连续记录。

create or replace function import_table(table_name text, jdata json)
returns void language plpgsql as $$
begin
    execute format('create table %s (%s)', table_name, string_agg(col, ', '))
    from (
        select format_column(key::text, value::text) col
        from json_each(jdata->0)
        ) sub;

    execute format('insert into %s values %s', table_name, string_agg(val, ','))
    from (
        with lines as (
            select row_number() over () rn, line
            from (
                select json_array_elements(jdata) line
                ) sub
            )
        select rn, format('(%s)', string_agg(value, ',')) val
        from (
            select rn, format('%L', trim(value::text, '"')) as value
            from lines, json_each(line)
            ) sub
        group by 1
        ) sub;
end $$; 

测试:

select import_table('customers', 
    '[{ "Id": 55948,
        "FullName": "Full name #1",
        "Address": "Address #1",
        "Turnover": 120400.5,
        "DateOfRegistration": "2014-02-13",
        "LastModifiedAt": "2015-11-03 12:04:44" },
    {   "Id": 55949,
        "FullName": "Full name %2",
        "Address": "Address #2",
        "Turnover": 120000.0,
        "DateOfRegistration": "2012-12-01",
        "LastModifiedAt": "2015-11-04 17:14:21" }]');

\d customers
                    Table "public.customers"
        Column        |            Type             | Modifiers 
----------------------+-----------------------------+-----------
 id                   | integer                     | 
 full_name            | text                        | 
 address              | text                        | 
 turnover             | numeric                     | 
 date_of_registration | date                        | 
 last_modified_at     | timestamp without time zone |

select * from customers;

  id   |  full_name   |  address   | turnover | date_of_registration |  last_modified_at   
-------+--------------+------------+----------+----------------------+---------------------
 55948 | Full name #1 | Address #1 | 120400.5 | 2014-02-13           | 2015-11-03 12:04:44
 55949 | Full name %2 | Address #2 | 120000.0 | 2012-12-01           | 2015-11-04 17:14:21
(2 rows)