英文:
How to set values from map (associative array) of items in 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
或空白。我想要做的是检查rel_id
是否与地图项的键值匹配,并且如果lat
或long
为null
或空白,则将其对应的值从地图中设置如下:
"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}
这样就不会在表中出现null
值。伪代码如下:
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 AND (t1.long IS NULL OR t1.lat IS NULL);
END
$$;
我有一个常量坐标的JSON映射。我尝试的是,当记录的rel_id
与coordinates
映射中的id
匹配且lat
或long
的值为空时,更新lat
和long
的值。
英文:
I have a following like table
CREATE TABLE
IF NOT EXISTS t1
(
ID bigserial PRIMARY KEY,
name text,
lat varchar(255),
long varchar(255),
rel_id varchar(255)
);
Here some values in lat, long columns are null or blank. What I want to do is to check if rel_id matches key value of map items and if lat or long is null or blank set its corresponding value from map like this:
"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},
so that there won't be null values in table. Pseudocode might look like this:
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;
}
}
I need such logic in SQL. Thank you.
What I tried was, but I can't make it work:
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
$$;
I have a constant map of coordinates in json. What I'm trying to do is update lat and long values where rel_id of records match with id in coordinates map and lat or long values are null.
答案1
得分: 2
步骤:
- 读取输入的jsonb并将
crel_id
、clat
和clong
的值转换为单独的列。我认为这是查询中最棘手的部分。 - 使用
json_data
表中的值更新t1
表。如果t1.lat
或t1.long
为空或为null,则更新值。
最终的过程可能是:
DO
$$
DECLARE
coordinates jsonb := '[
{
"rel_id": "1708237",
"lat": 1,
"long": 2
},
{
"rel_id": "1703206",
"lat": 3,
"long": 4
},
{
"rel_id": "1703217",
"lat": 5,
"long": 6
},
{
"rel_id": "1703209",
"lat": 7,
"long": 8
},
{
"rel_id": "1703230",
"lat": 9,
"long": 10
}
]'::jsonb;
BEGIN
update t1
set lat = json_data.clat,
long = json_data.clong
from (select
coords->>'rel_id' as crel_id, --将jsonb值转换为字符串
coords->>'lat' as clat,
coords->>'long' as clong
from (select jsonb_array_elements(coordinates) as coords)
as coords_data)
as json_data
where t1.rel_id = json_data.crel_id and
-- 检查'lat'和'long'列的值可能为null或为空
(t1.long is null or t1.long = '' or t1.lat is null or t1.lat = '');
END
$$;
英文:
Steps:
- Read input jsonb and convert
crel_id
,clat
andclong
values into separate columns. I would say that this is the trickiest part of the query. - Update
t1
table using values fromjson_data
table. Update values if there ist1.lat
ort1.long
is empty or null.
Final procedure could be:
DO
$$
DECLARE
coordinates jsonb := '[
{
"rel_id": "1708237",
"lat": 1,
"long": 2
},
{
"rel_id": "1703206",
"lat": 3,
"long": 4
},
{
"rel_id": "1703217",
"lat": 5,
"long": 6
},
{
"rel_id": "1703209",
"lat": 7,
"long": 8
},
{
"rel_id": "1703230",
"lat": 9,
"long": 10
}
]'::jsonb;
BEGIN
update t1
set lat = json_data.clat,
long = json_data.clong
from (select
coords->'rel_id' #>> '{}' as crel_id, --convert jsonb value to string
coords->'lat' as clat,
coords->'long' as clong
from (select jsonb_array_elements(coordinates) as coords)
as coords_data)
as json_data
where t1.rel_id = json_data.crel_id and
-- check value may be null or empty for both 'lat' and 'long' columns
(t1.long is null or t1.long = '' or t1.lat is null or t1.lat = '');
END
$$;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论