我有一个喜欢的表以下

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或空白。我想做的是检查rel_id是否匹配map项目的键值，如果lat或long为null或空白，则从map中设置其相应的值，如下所示：

"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) {
        values.lat = coordinates[values.rel_id].lat;
        values.long = coordinates[values.rel_id].long;
    }
}

型
我需要这样的逻辑在SQL。- 谢谢-谢谢
我试过了，但我做不到：

DO
$$
    DECLARE
        coordinates jsonb := '[
          {
            "rel_id": "1",
            "lat": 1231.123,
            "long": 1423.2342
          },
          {
            "rel_id": "2",
            "lat": 1231.123,
            "long": 1423.2342
          },
          {
            "rel_id": "3",
            "lat": 1231.123,
            "long": 1423.2342
          },
          {
            "rel_id": "4",
            "lat": 1231.123,
            "long": 1423.2342
          },
          {
            "rel_id": "5",
            "lat": 1231.123,
            "long": 1423.2342
          },
          {
            "rel_id": "6",
            "lat": 1231.123,
            "long": 1423.2342
          },
          {
            "rel_id": "7",
            "lat": 1231.123,
            "long": 1423.2342
          },
          {
            "rel_id": "8",
            "lat": 1231.123,
            "long": 1423.2342
          }
        ]'::jsonb;
    BEGIN

        UPDATE t1
        SET lat  = ci.clat,
            long = ci.clong
        from (select json_array_elements(coordinates) ->> 'rel_id' as crel_id, 'lat' as clat, 'long' as clong) as ci
        WHERE t1.rel_id = ci.crel_id && (t1.long is NULL or t1.lat is NULL);
    END
$$;

型
我在JSON中有一个恒定的坐标Map。我尝试做的是更新lat和long值，其中记录的rel_id与coordinatesmap中的id匹配，并且lat或long值为null。