为什么Postgres表在使用JSON数据执行SQL插入查询后，每个列中显示了NULL

Question

我试图使用此查询将JSON数据插入到Postgres表中 -

INSERT INTO rf_dsgns 
SELECT * FROM json_populate_recordset(NULL::rf_dsgns,
'[
  {
    "Tracking_ID": 2377125,
    "Constr_Zone": "Cleveland",
    "AF_Name": "PbCleveland_10236716P",       
    "Address": "4755 1/2 Rose Avenue",
    "Zip_Code": 44867,
    "Latitude": 5.8923486,
    "Longitude": -71.71052258,        
  },{
    "Tracking_ID": 2377126,
    "Constr_Zone": "Cleveland",
    "AF_Name": "PggClevelandCLE_25236718P",       
    "Street_Address": "4413 1/3 Clain Avenue",  
    "Zip_Code": 44225,
    "Latitude": 40.88960254,
    "Longitude": -71.20898567,        
  }]');

我在创建表格时使用的数据类型分别是整数，字符，角色，角色，整数，数字，数字。我的创建表脚本是

CREATE TABLE rf_dsgns
(
    tracking_id integer,
    constr_zone character(300),
    af_name character(300),       
    address character(300),        
    zip_code integer,
    latitude numeric,
    longitude numeric       
);

Answer 1

您很可能在不使用列名的双引号（这是一件好事）的情况下创建表。但是，json_populate_recordset()匹配案例敏感，因此表中的较低案例列名称与JSON中的混合案例名称不匹配。

这个：

create table rf_dsgns ("Tracking_ID" int, "Constr_Zone" text, "AF_Name" text, "Address" text, "Zip_Code" text, "Latitude" numeric, "Longitude" numeric);
SELECT * 
FROM json_populate_recordset(NULL::rf_dsgns,
'[
  {
    "Tracking_ID": 2377125,
    "Constr_Zone": "Cleveland",
    "AF_Name": "PbCleveland_10236716P",       
    "Address": "4755 1/2 Rose Avenue",
    "Zip_Code": 44867,
    "Latitude": 5.8923486,
    "Longitude": -71.71052258
  },{
    "Tracking_ID": 2377126,
    "Constr_Zone": "Cleveland",
    "AF_Name": "PggClevelandCLE_25236718P",       
    "Street_Address": "4413 1/3 Clain Avenue",  
    "Zip_Code": 44225,
    "Latitude": 40.88960254,
    "Longitude": -71.20898567        
  }]');

返回：

Tracking_ID | Constr_Zone | AF_Name                   | Address              | Zip_Code | Latitude    | Longitude   
------------+-------------+---------------------------+----------------------+----------+-------------+-------------
    2377125 | Cleveland   | PbCleveland_10236716P     | 4755 1/2 Rose Avenue |    44867 |   5.8923486 | -71.71052258
    2377126 | Cleveland   | PggClevelandCLE_25236718P |                      |    44225 | 40.88960254 | -71.20898567

但是，当没有引号创建表时：

create table rf_dsgns (tracking_id int, constr_zone text, af_name text, address text, zip_code text, latitude numeric, longitude numeric);

然后不匹配列，一切都将是 NULL

i将不是用案例敏感的列名重新创建表。我可以看到两个解决方法：创建一种使用引用标识符的新类型，但与表定义相同，并使用它来映射JSON数据。

而是使用json_array_elements()，然后手动拼写所有列名，这有点键入，但不会复制类型的定义（实际上更灵活）：

insert into rf_dsgns
SELECT (j ->> 'Tracking_ID')::int, 
       j ->> 'Constr_Zone',
       j ->> 'AF_Name',
       j ->> 'Addres', 
       j ->> 'Zip_Code',
       (j ->> 'Latitude')::numeric,
       (j ->> 'longitude')::numeric
FROM json_array_elements('.... your json here ') a t(j);