postgresql 更新jsonb数组中的值时返回错误“类型json的输入语法无效”

2eafrhcq  于 2022-11-04  发布在  PostgreSQL
关注(0)|答案(1)|浏览(482)

我有一个类型为jsonb的列,其中包含json数组,其形式为

  1. [
  2. {
  3. "Id": 62497,
  4. "Text": "BlaBla"
  5. }
  6. ]

我想将Id更新为另一个表word中的列word_id(类型uuid)的值。
我试过这个

  1. update inflection_copy
  2. SET inflectionlinks = s.json_array
  3. FROM (
  4. SELECT jsonb_agg(
  5. CASE
  6. WHEN elems->>'Id' = (
  7. SELECT word_copy.id::text
  8. from word_copy
  9. where word_copy.id::text = elems->>'Id'
  10. ) THEN jsonb_set(
  11. elems,
  12. '{Id}'::text [],
  13. (
  14. SELECT jsonb(word_copy.word_id::text)
  15. from word_copy
  16. where word_copy.id::text = elems->>'Id'
  17. )
  18. )
  19. ELSE elems
  20. END
  21. ) as json_array
  22. FROM inflection_copy,
  23. jsonb_array_elements(inflectionlinks) elems
  24. ) s;

直到现在我总是得到下面的错误:

  1. invalid input syntax for type json
  2. DETAIL: Token "c66a4353" is invalid.
  3. CONTEXT: JSON data, line 1: c66a4353...

c66a4535word表的一个uuid的一部分。我不明白为什么它被标记为无效输入。
编辑:

给予一个uuid的例子:

  1. select to_jsonb(word_id::text) from word_copy limit(5);

返回

  1. +----------------------------------------+
  2. | to_jsonb |
  3. |----------------------------------------|
  4. | "078c979d-e479-4fce-b27c-d14087f467c2" |
  5. | "ef288256-1599-4f0f-a932-aad85d666c9a" |
  6. | "d1d95b60-623e-47cf-b770-de46b01042c5" |
  7. | "f97464c6-b872-4be8-9d9d-83c0102fb26a" |
  8. | "9bb19719-e014-4286-a2d1-4c0cf7f089fc" |
  9. +----------------------------------------+

根据请求,word表中的相应列idword_id

  1. +---------------------------------------------------+
  2. | row |
  3. |---------------------------------------------------|
  4. | ('27733', '078c979d-e479-4fce-b27c-d14087f467c2') |
  5. | ('72337', 'ef288256-1599-4f0f-a932-aad85d666c9a') |
  6. | ('72340', 'd1d95b60-623e-47cf-b770-de46b01042c5') |
  7. | ('27741', 'f97464c6-b872-4be8-9d9d-83c0102fb26a') |
  8. | ('72338', '9bb19719-e014-4286-a2d1-4c0cf7f089fc') |
  9. +---------------------------------------------------+
  10. +----------------+----------+----------------------------+
  11. | Column | Type | Modifiers |
  12. |----------------+----------+----------------------------|
  13. | id | bigint | |
  14. | value | text | |
  15. | homonymnumber | smallint | |
  16. | pronounciation | text | |
  17. | audio | text | |
  18. | level | integer | |
  19. | alpha | bigint | |
  20. | frequency | bigint | |
  21. | hanja | text | |
  22. | typeeng | text | |
  23. | typekr | text | |
  24. | word_id | uuid | default gen_random_uuid() |
  25. +----------------+----------+----------------------------+
xuo3flqw

xuo3flqw1#

我建议您修改子查询如下:

  1. update inflection_copy AS ic
  2. SET inflectionlinks = s.json_array
  3. FROM
  4. (SELECT jsonb_agg(CASE WHEN wc.word_id IS NULL THEN e.elems ELSE jsonb_set(e.elems, array['Id'], to_jsonb(wc.word_id::text)) END ORDER BY e.id ASC) AS json_array
  5. FROM inflection_copy AS ic
  6. CROSS JOIN LATERAL jsonb_path_query(ic.inflectionlinks, '$[*]') WITH ORDINALITY AS e(elems, id)
  7. LEFT JOIN word_copy AS wc
  8. ON wc.id::text = e.elems->>'Id'
  9. ) AS s

LEFT JOIN子句将在没有与e.elems->>'id'对应的wc.id时返回wc.word_id = NULL,因此e.elemsCASE中保持不变。
聚合函数jsonb_agg中的ORDER BY子句将确保jsonb数组中的顺序不变。
使用jsonb_path_query而不是jsonb_array_elements,以便在ic.inflectionlinks不是jsonb数组并且在lax模式(默认行为)下使用时不会引发错误。
请参见dbfiddle中的测试结果

相关问题