How to cast JSON text field to UUID in PostgreSQL plpgsql function-CodePudding

I have the following function that I use in a RPC (Remote Procedural Call) within which I attempt to access a JSON object's optional text field and cast it to a UUID:

CREATE OR REPLACE FUNCTION update_wcomponent (item wcomponents)
returns wcomponents
language plpgsql
security definer
SET search_path = public
as $$
DECLARE
  existing wcomponents%ROWTYPE;
BEGIN

  UPDATE wcomponents
  SET
    modified_at = now(),
    title = item.json::json->'title',
    type = item.json::json->'type',
    -- Following line errors
    attribute_id = (item.json::json->'attribute_wcomponent_id')::text::uuid,
    json = item.json
  WHERE id = item.id AND modified_at = item.modified_at;

  -- Get the latest value
  SELECT * INTO existing FROM wcomponents WHERE id = item.id;

  return existing;
END;
$$;

However it errors on casting the text to uuid with the error:

invalid input syntax for type uuid: ""310edbfb-0af1-411b-9275-3fc87948c3a5""

Where I post it a JSON object of the following structure:

{
    "item": {
        "base_id": 18,
        "id": "27e46ec1-3d9a-412d-9807-8e08e515988d",
        "json": {
            "id": "27e46ec1-3d9a-412d-9807-8e08e515988d",
            "title": "",
            "type": "state_value",
            "attribute_wcomponent_id": "310edbfb-0af1-411b-9275-3fc87948c3a5"
        },
        "modified_at": "2022-04-23T22:11:13.533Z"
    }
}

attribute_wcomponent_id is optional, so this works fine when its undefined as it will just be absent.

How do I successfully cast it from a json text attribute to uuid? It seems the text string is quoted as "310edbfb-0af1-411b-9275-3fc87948c3a5" rather than just 310edbfb-0af1-411b-9275-3fc87948c3a5.

CodePudding user response：

You need to use the ->> operator to return your attribute_wcomponent_id value as text, rather than json. Then the conversion to type text prior to uuid is not necessary, and you no longer have the double quoting issue that you are seeing.

attribute_id = (item.json::json->>'attribute_wcomponent_id')::uuid

Demo on dbfiddle