如何从postgresql中的项目映射(关联数组)设置值

我有一张跟我一样的桌子

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

在这里，LATH、LONG列中某些值为空或空白.我要做的是判断rel_id是否与映射项的键值匹配，如果lat或long为空或空，则从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 .我要做是更新LATH和LONG值，其中记录的rel_id与coordinates映射中的ID匹配，且LATE或LONG值为空.