Update of value in array of jsonb returns error"invalid input syntax for type json"-CodePudding

I have a column of type jsonb which contains json arrays of the form

[
  {
    "Id": 62497,
    "Text": "BlaBla"
  }
]

I'd like to update the Id to the value of a column word_id (type uuid) from a different table word.

I tried this

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;

Until now I always get the following error:

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

The c66a4535 is part of one of the uuids of the word table. I don't understand why this is marked as invalid input.

EDIT:

To give an example of one of the uuids:

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

returns

 ---------------------------------------- 
| 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" |
 ----------------------------------------

As requested the respective columns id and word_id from the word table:

 --------------------------------------------------- 
| 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() |
 ---------------- ---------- ----------------------------

CodePudding user response：

I would suggest you to modify your sub query as follow :

update inflection_copy AS ic
SET inflectionlinks = s.json_array
FROM 
(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
   FROM inflection_copy AS ic
  CROSS JOIN LATERAL jsonb_path_query(ic.inflectionlinks, '$[*]') WITH ORDINALITY AS e(elems, id)
   LEFT JOIN word_copy AS wc
     ON wc.id::text = e.elems->>'Id'
) AS s

The LEFT JOIN clause will return wc.word_id = NULL when there is no wc.id which corresponds to e.elems->>'id', so that e.elems is unchanged in the CASE.

The ORDER BY clause in the aggregate function jsonb_agg will ensure that the order is unchanged in the jsonb array.

jsonb_path_query is used instead of jsonb_array_elements so that to not raise an error when ic.inflectionlinks is not a jsonb array and it is used in lax mode (which is the default behavior).

see the test result in dbfiddle