Unable to update document via SQL: number to string (in double quotes)

Couchbase Server
1

Pasting a small section in my document: partNumber: 129433130
I need to update it as partNumber:“129433130”

I used below queries but they are not helping. can you please let know if there is anything that can fix this.

Update default AS cont
Set part.partNumber = TOSTRING(part.partNumber), part.lotCode = TOSTRING(part.lotCode) FOR part in cont.parts END
WHERE meta(cont).id = ‘container_323b530d3b094f31b757089f0f18088d’

Update default AS cont
Set part.partNumber = TO_STRING(part.partNumber), part.lotCode = TO_STRING(part.lotCode) FOR part in cont.parts END
WHERE meta(cont).id = ‘container_323b530d3b094f31b757089f0f18088d’

2

In SET clause comma separation is separate expression you need to have it own FOR clause otherwise it is looking part as the top level document. Which is MISSING so no update happens.

   UPDATE default AS cont USE KEYS ["container_323b530d3b094f31b757089f0f18088d"]
    SET part.partNumber = TO_STRING(part.partNumber) FOR part IN cont.parts END,
        part.lotCode = TO_STRING(part.lotCode) FOR part in cont.parts END
    WHERE ARRAY_LENGTH(cont.parts) > 0;

OR

UPDATE default AS cont USE KEYS ["container_323b530d3b094f31b757089f0f18088d"]
SET cont.parts = ARRAY OBJECT_PUT(
                           OBJECT_PUT(part,"partNumber",TO_STRING(part.partNumber)),
                           "lotCode",TO_STRING(part.lotCode))
                 FOR part IN cont.parts END
WHERE ARRAY_LENGTH(cont.parts) > 0;
3

It was urgent, So we fixed all the documents manually :frowning:
Hope I can use this in future.

Can you let know how to fix for selected documents like condition query below:

select meta(cont).id, cont.name from default AS cont
USE INDEX (container_search USING GSI)
UNNEST cont.parts part
where meta(cont).id like 'container%'and meta(cont).id not like ‘%_AuditLog’ and (ISNUMBER(part.partNumber) or ISNUMBER(part.lotCode))

4 
    UPDATE default AS cont 
    SET part.partNumber = TO_STRING(part.partNumber) FOR part IN cont.parts WHEN ISNUMBER(part.partNumber) END,
        part.lotCode = TO_STRING(part.lotCode) FOR part in cont.parts WHEN ISNUMBER(part.lotCode) END
     WHERE  META(cont).id LIKE "container%" 
          AND META(cont).id NOT LIKE "%_AuditLog" 
          AND ANY part IN cont.parts  SATISFIES (ISNUMBER(part.partNumber) OR ISNUMBER(part.lotCode)) END;
1 Like