我有一个类型为jsonb
的列,其中包含json数组,其形式为
[
{
"Id": 62497,
"Text": "BlaBla"
}
]
我想将Id
更新为另一个表word
中的列word_id
(类型uuid
)的值。
我试过这个
update inflection_copy
SET inflectionlinks = s.json_array
FROM (
SELECT jsonb_agg(
CASE
WHEN elems->>'Id' = (
SELECT word_copy.id::text
from word_copy
where word_copy.id::text = elems->>'Id'
) THEN jsonb_set(
elems,
'{Id}'::text [],
(
SELECT jsonb(word_copy.word_id::text)
from word_copy
where word_copy.id::text = elems->>'Id'
)
)
ELSE elems
END
) as json_array
FROM inflection_copy,
jsonb_array_elements(inflectionlinks) elems
) s;
直到现在我总是得到下面的错误:
invalid input syntax for type json
DETAIL: Token "c66a4353" is invalid.
CONTEXT: JSON data, line 1: c66a4353...
c66a4535是word
表的一个uuid的一部分。我不明白为什么它被标记为无效输入。
编辑:
给予一个uuid的例子:
select to_jsonb(word_id::text) from word_copy limit(5);
返回
+----------------------------------------+
| to_jsonb |
|----------------------------------------|
| "078c979d-e479-4fce-b27c-d14087f467c2" |
| "ef288256-1599-4f0f-a932-aad85d666c9a" |
| "d1d95b60-623e-47cf-b770-de46b01042c5" |
| "f97464c6-b872-4be8-9d9d-83c0102fb26a" |
| "9bb19719-e014-4286-a2d1-4c0cf7f089fc" |
+----------------------------------------+
根据请求,word
表中的相应列id
和word_id
:
+---------------------------------------------------+
| row |
|---------------------------------------------------|
| ('27733', '078c979d-e479-4fce-b27c-d14087f467c2') |
| ('72337', 'ef288256-1599-4f0f-a932-aad85d666c9a') |
| ('72340', 'd1d95b60-623e-47cf-b770-de46b01042c5') |
| ('27741', 'f97464c6-b872-4be8-9d9d-83c0102fb26a') |
| ('72338', '9bb19719-e014-4286-a2d1-4c0cf7f089fc') |
+---------------------------------------------------+
+----------------+----------+----------------------------+
| Column | Type | Modifiers |
|----------------+----------+----------------------------|
| id | bigint | |
| value | text | |
| homonymnumber | smallint | |
| pronounciation | text | |
| audio | text | |
| level | integer | |
| alpha | bigint | |
| frequency | bigint | |
| hanja | text | |
| typeeng | text | |
| typekr | text | |
| word_id | uuid | default gen_random_uuid() |
+----------------+----------+----------------------------+
1条答案
按热度按时间xuo3flqw1#
我建议您修改子查询如下:
LEFT JOIN
子句将在没有与e.elems->>'id'
对应的wc.id
时返回wc.word_id = NULL
,因此e.elems
在CASE
中保持不变。聚合函数
jsonb_agg
中的ORDER BY
子句将确保jsonb数组中的顺序不变。使用
jsonb_path_query
而不是jsonb_array_elements
,以便在ic.inflectionlinks
不是jsonb数组并且在lax模式(默认行为)下使用时不会引发错误。请参见dbfiddle中的测试结果