如何将JSON字典的organization设为新键并导入PostgreSQL数据库？

1. 将JSON转换为以organization为键的结构 #

可以用Python快速实现结构转换，代码示例如下：

import json

# 原JSON数据
original_data = '''
{
    "objects": {
      "record": [
        {
          "organization": 3,
          "code": 34,
          "name": "Luku' Dean",
          "address_1": "",
          "address_2": "",
          "city": "",
          "postcode": "",
          "state": "",
          "country": "",
          "vat_number": "",
          "telephone_number": "",
          "fax_number": "",
          "currency": "EUR",
          "start_date": "2001-01-01",
          "end_date": "2999-12-31",
          "status": "ACTIVE"
        },
        {
          "organization": 4,
          "code": 45,
          "name": "Mr Adr",
          "address_1": "",
          "address_2": "",
          "city": "",
          "postcode": "",
          "state": "",
          "country": "",
          "vat_number": "",
          "telephone_number": "",
          "fax_number": "",
          "currency": "EUR",
          "start_date": "2001-01-01",
          "end_date": "2999-12-31",
          "status": "ACTIVE"
        },
        {
          "organization": 5,
          "code": 67,
          "name": "MR. K.J.Abhinand",
          "address_1": "",
          "address_2": "",
          "city": "",
          "postcode": "",
          "state": "",
          "country": "",
          "vat_number": "",
          "telephone_number": "",
          "fax_number": "",
          "currency": "EUR",
          "start_date": "2001-01-01",
          "end_date": "2999-12-31",
          "status": "ACTIVE"
        }
      ]
    }
}
'''

# 解析并转换结构
parsed = json.loads(original_data)
result = {}
for item in parsed['objects']['record']:
    org_id = item.pop('organization')
    result[str(org_id)] = item  # 用字符串作为键，符合JSON键的类型规范

# 输出转换后的JSON
print(json.dumps(result, indent=2))

转换后的最终JSON结构：

{
  "3": {
    "code": 34,
    "name": "Luku' Dean",
    "address_1": "",
    "address_2": "",
    "city": "",
    "postcode": "",
    "state": "",
    "country": "",
    "vat_number": "",
    "telephone_number": "",
    "fax_number": "",
    "currency": "EUR",
    "start_date": "2001-01-01",
    "end_date": "2999-12-31",
    "status": "ACTIVE"
  },
  "4": {
    "code": 45,
    "name": "Mr Adr",
    "address_1": "",
    "address_2": "",
    "city": "",
    "postcode": "",
    "state": "",
    "country": "",
    "vat_number": "",
    "telephone_number": "",
    "fax_number": "",
    "currency": "EUR",
    "start_date": "2001-01-01",
    "end_date": "2999-12-31",
    "status": "ACTIVE"
  },
  "5": {
    "code": 67,
    "name": "MR. K.J.Abhinand",
    "address_1": "",
    "address_2": "",
    "city": "",
    "postcode": "",
    "state": "",
    "country": "",
    "vat_number": "",
    "telephone_number": "",
    "fax_number": "",
    "currency": "EUR",
    "start_date": "2001-01-01",
    "end_date": "2999-12-31",
    "status": "ACTIVE"
  }
}

2. 将JSON数据加载到PostgreSQL的各个字段 #

完全可以实现，PostgreSQL对JSON有原生且完善的支持，提供两种常用方案：

方案一：解析JSON插入结构化表 #

先创建与数据字段匹配的表：

CREATE TABLE organizations (
    organization INT PRIMARY KEY,
    code INT,
    name VARCHAR(255),
    address_1 VARCHAR(255),
    address_2 VARCHAR(255),
    city VARCHAR(100),
    postcode VARCHAR(20),
    state VARCHAR(100),
    country VARCHAR(100),
    vat_number VARCHAR(50),
    telephone_number VARCHAR(50),
    fax_number VARCHAR(50),
    currency VARCHAR(3),
    start_date DATE,
    end_date DATE,
    status VARCHAR(20)
);

使用jsonb_to_recordset函数解析原JSON并插入数据：

INSERT INTO organizations
SELECT * FROM jsonb_to_recordset('{"objects": {"record": [{"organization":3,"code":34,"name":"Luku'' Dean","address_1":"","address_2":"","city":"","postcode":"","state":"","country":"","vat_number":"","telephone_number":"","fax_number":"","currency":"EUR","start_date":"2001-01-01","end_date":"2999-12-31","status":"ACTIVE"},{"organization":4,"code":45,"name":"Mr Adr","address_1":"","address_2":"","city":"","postcode":"","state":"","country":"","vat_number":"","telephone_number":"","fax_number":"","currency":"EUR","start_date":"2001-01-01","end_date":"2999-12-31","status":"ACTIVE"},{"organization":5,"code":67,"name":"MR. K.J.Abhinand","address_1":"","address_2":"","city":"","postcode":"","state":"","country":"","vat_number":"","telephone_number":"","fax_number":"","currency":"EUR","start_date":"2001-01-01","end_date":"2999-12-31","status":"ACTIVE"}]}}'::jsonb->'objects'->'record')
AS x(organization INT, code INT, name VARCHAR(255), address_1 VARCHAR(255), address_2 VARCHAR(255), city VARCHAR(100), postcode VARCHAR(20), state VARCHAR(100), country VARCHAR(100), vat_number VARCHAR(50), telephone_number VARCHAR(50), fax_number VARCHAR(50), currency VARCHAR(3), start_date DATE, end_date DATE, status VARCHAR(20));

方案二：存储转换后的JSON对象到JSONB字段 #

如果需要保留JSON结构的灵活性，可以创建包含JSONB类型字段的表：

CREATE TABLE org_json (
    organization INT PRIMARY KEY,
    details JSONB
);

-- 插入转换后的JSON数据
INSERT INTO org_json VALUES
(3, '{"code":34,"name":"Luku'' Dean","address_1":"","address_2":"","city":"","postcode":"","state":"","country":"","vat_number":"","telephone_number":"","fax_number":"","currency":"EUR","start_date":"2001-01-01","end_date":"2999-12-31","status":"ACTIVE"}'::jsonb),
(4, '{"code":45,"name":"Mr Adr","address_1":"","address_2":"","city":"","postcode":"","state":"","country":"","vat_number":"","telephone_number":"","fax_number":"","currency":"EUR","start_date":"2001-01-01","end_date":"2999-12-31","status":"ACTIVE"}'::jsonb),
(5, '{"code":67,"name":"MR. K.J.Abhinand","address_1":"","address_2":"","city":"","postcode":"","state":"","country":"","vat_number":"","telephone_number":"","fax_number":"","currency":"EUR","start_date":"2001-01-01","end_date":"2999-12-31","status":"ACTIVE"}'::jsonb);

后续可以直接查询JSON内部字段，例如：

SELECT details->>'name' AS org_name FROM org_json WHERE organization = 3;

内容的提问来源于stack exchange，提问作者AstroInTheOcean