如何在PostgreSQL中利用映射数组填充表中缺失的经纬度值

表结构 #

CREATE TABLE
    IF NOT EXISTS t1
(
    ID                bigserial PRIMARY KEY,
    name              text,
    lat               varchar(255),
    long              varchar(255),
    rel_id varchar(255)
);

需求说明 #

表中lat、long字段存在部分值为null或空白的情况，需要实现以下逻辑：

• 当t1.rel_id与指定映射的键匹配时
• 若lat或long为null/空白，则用映射中对应的坐标值填充这两个字段

映射数组与逻辑说明 #

坐标映射（JSON对象格式） #

{
  "1708237": [40.003196, 68.766304],
  "1703206": [40.855638, 71.951236],
  "1703217": [40.789588, 71.703445],
  "1703209": [40.696825, 71.893556],
  "1703230": [40.692121, 72.072769],
  "1703202": [40.777208, 72.195201],
  "1703214": [40.912185, 72.261577],
  "1703232": [40.967893, 72.411201],
  "1703203": [40.814196, 72.464561],
  "1703211": [40.733920, 72.635528],
  "1703220": [40.769949, 72.872072],
  "1703236": [40.644870, 72.589310],
  "1703224": [40.667720, 72.237619],
  "1703210": [40.609053, 72.487692],
  "1703227": [40.522460, 72.306502],
  "1730212": [40.615228, 71.140965],
  "1730215": [40.438027, 70.528916],
  "1730209": [40.495830, 71.219648],
  "1730203": [40.463302, 71.456543],
  "1730224": [40.368501, 71.201116],
  "1730242": [40.646348, 71.658763]
}

伪代码逻辑 #

coordinates = {
  "1708237": {lat: 40.003196, long: 68.766304},
  "1703206": {lat: 40.855638, long: 71.951236},
  "1703217": {lat: 40.789588, long: 71.703445}
}

for values in results {
    if (values.lat == Null or values.long == Null or values.lat.trim() == '' or values.long.trim() == '') {
        values.lat = coordinates[values.rel_id].lat;
        values.long = coordinates[values.rel_id].long;
    }
}

错误代码问题分析 #

你提供的DO块存在以下问题：

• JSON结构错误：应该用键值对对象而非数组，因为需要通过rel_id（键）直接匹配
• JSON解析方式错误：json_array_elements用于处理数组，处理键值对对象应该用jsonb_each
• 赋值错误：直接写'lat'和'long'是赋值字符串，而非提取映射中的坐标值
• 条件运算符错误：PostgreSQL中&&是数组重叠运算符，逻辑与应该用AND
• 未处理空白值：只判断了null，没处理空字符串的情况

正确的SQL解决方案 #

方案1：使用DO块执行批量更新 #

DO
$$
DECLARE
    coordinates jsonb := '{
        "1708237": [40.003196, 68.766304],
        "1703206": [40.855638, 71.951236],
        "1703217": [40.789588, 71.703445],
        "1703209": [40.696825, 71.893556],
        "1703230": [40.692121, 72.072769],
        "1703202": [40.777208, 72.195201],
        "1703214": [40.912185, 72.261577],
        "1703232": [40.967893, 72.411201],
        "1703203": [40.814196, 72.464561],
        "1703211": [40.733920, 72.635528],
        "1703220": [40.769949, 72.872072],
        "1703236": [40.644870, 72.589310],
        "1703224": [40.667720, 72.237619],
        "1703210": [40.609053, 72.487692],
        "1703227": [40.522460, 72.306502],
        "1730212": [40.615228, 71.140965],
        "1730215": [40.438027, 70.528916],
        "1730209": [40.495830, 71.219648],
        "1730203": [40.463302, 71.456543],
        "1730224": [40.368501, 71.201116],
        "1730242": [40.646348, 71.658763]
    }'::jsonb;
BEGIN
    UPDATE t1
    SET 
        lat = (ci.coords ->> 0)::varchar(255),
        long = (ci.coords ->> 1)::varchar(255)
    FROM (
        SELECT 
            key::varchar(255) AS rel_id,
            value AS coords
        FROM jsonb_each(coordinates)
    ) ci
    WHERE 
        t1.rel_id = ci.rel_id
        AND (
            t1.lat IS NULL OR TRIM(t1.lat) = ''
            OR t1.long IS NULL OR TRIM(t1.long) = ''
        );
END
$$;

方案2：直接用UPDATE语句（无需DO块） #

如果不需要复用映射，也可以直接把JSON写在UPDATE语句中：

UPDATE t1
SET 
    lat = (ci.coords ->> 0)::varchar(255),
    long = (ci.coords ->> 1)::varchar(255)
FROM (
    SELECT 
        key::varchar(255) AS rel_id,
        value AS coords
    FROM jsonb_each('{
        "1708237": [40.003196, 68.766304],
        "1703206": [40.855638, 71.951236],
        "1703217": [40.789588, 71.703445],
        "1703209": [40.696825, 71.893556],
        "1703230": [40.692121, 72.072769],
        "1703202": [40.777208, 72.195201],
        "1703214": [40.912185, 72.261577],
        "1703232": [40.967893, 72.411201],
        "1703203": [40.814196, 72.464561],
        "1703211": [40.733920, 72.635528],
        "1703220": [40.769949, 72.872072],
        "1703236": [40.644870, 72.589310],
        "1703224": [40.667720, 72.237619],
        "1703210": [40.609053, 72.487692],
        "1703227": [40.522460, 72.306502],
        "1730212": [40.615228, 71.140965],
        "1730215": [40.438027, 70.528916],
        "1730209": [40.495830, 71.219648],
        "1730203": [40.463302, 71.456543],
        "1730224": [40.368501, 71.201116],
        "1730242": [40.646348, 71.658763]
    }'::jsonb)
) ci
WHERE 
    t1.rel_id = ci.rel_id
    AND (
        t1.lat IS NULL OR TRIM(t1.lat) = ''
        OR t1.long IS NULL OR TRIM(t1.long) = ''
    );

关键说明 #

• 使用jsonb_each遍历JSON对象的键值对，key对应rel_id，value是包含lat和long的数组
• 通过->> 0和->> 1提取数组中的第一个（纬度）和第二个（经度）元素，转换为varchar类型匹配表字段
• 条件中增加TRIM(t1.lat) = ''处理空白字符串的情况
• 确保映射中的键与t1.rel_id类型一致（都转为varchar(255)）

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