如何将嵌套JSON插入PostgreSQL？现有方案返回空值求解决

你的代码存在两个核心问题：

1. json_populate_record仅匹配JSON顶层键与表列名，但目标字段Country、Numbers嵌套在Address对象中，顶层无对应键，因此查询返回空值。
2. 代码仅执行了查询操作，从未向Log表插入数据，最终查询表自然为空。

以下是几种将嵌套JSON插入表的实用方法：

方法一：直接提取嵌套字段插入 #

使用->>操作符（或json_extract_path_text）从嵌套JSON中提取目标字段，再执行插入：

CREATE TABLE Log (
  "Name" TEXT,
  "Age"  TEXT,
  "Country" TEXT,
  "Numbers" TEXT
);

-- 定义待插入的JSON数据，执行插入
WITH json_data AS (
  SELECT '{
    "Name": "qazwsx",
    "HostName": "Age",
    "Address": {
      "Country": "MNB",
      "Numbers": [
        {
          "Cell": 7418520
        }
      ]
    }
  }'::json AS data
)
INSERT INTO Log ("Name", "Age", "Country", "Numbers")
SELECT
  data->>'Name' AS "Name",
  data->>'HostName' AS "Age", -- 映射JSON的HostName到表的Age列
  data->'Address'->>'Country' AS "Country",
  data->'Address'->>'Numbers' AS "Numbers" -- 将JSON数组转为文本存储
FROM json_data;

-- 验证结果
SELECT * FROM Log;

方法二：用json_to_record解析嵌套结构 #

如果需要更清晰地拆解多层JSON结构，可使用json_to_record定义层级结构：

WITH json_data AS (
  SELECT '{
    "Name": "qazwsx",
    "HostName": "Age",
    "Address": {
      "Country": "MNB",
      "Numbers": [
        {
          "Cell": 7418520
        }
      ]
    }
  }'::json AS data
)
INSERT INTO Log ("Name", "Age", "Country", "Numbers")
SELECT
  rec."Name",
  rec."HostName" AS "Age",
  addr."Country",
  addr."Numbers"::text
FROM json_data,
     json_to_record(data) AS rec("Name" text, "HostName" text, "Address" json),
     json_to_record(rec."Address") AS addr("Country" text, "Numbers" json[]);

SELECT * FROM Log;

方法三：PostgreSQL 12+ 使用JSON路径表达式 #

若使用PostgreSQL 12及以上版本，可通过JSON路径更灵活地提取嵌套字段：

CREATE TABLE Log (
  "Name" TEXT,
  "Age"  TEXT,
  "Country" TEXT,
  "Numbers" TEXT
);

WITH json_data AS (
  SELECT '{
    "Name": "qazwsx",
    "HostName": "Age",
    "Address": {
      "Country": "MNB",
      "Numbers": [
        {
          "Cell": 7418520
        }
      ]
    }
  }'::jsonb AS data -- jsonb类型支持路径查询
)
INSERT INTO Log ("Name", "Age", "Country", "Numbers")
SELECT
  jsonb_path_query_first(data, '$.Name')#>>'{}' AS "Name",
  jsonb_path_query_first(data, '$.HostName')#>>'{}' AS "Age",
  jsonb_path_query_first(data, '$.Address.Country')#>>'{}' AS "Country",
  jsonb_path_query_first(data, '$.Address.Numbers')#>>'{}' AS "Numbers"
FROM json_data;

SELECT * FROM Log;

如果需要保留Numbers的JSON数组结构，建议将表中Numbers列的类型改为JSON或JSONB，而非TEXT，这样后续可直接对数组进行JSON查询操作。

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