4 -- First, explode the field into constituent parts
5 WITH format_parts_array AS (
7 STRING_TO_ARRAY(a.holdable_formats, '-') AS parts
8 FROM action.hold_request a
9 WHERE a.hold_type = 'M'
10 AND a.fulfillment_time IS NULL
11 ), format_parts_wide AS (
13 regexp_split_to_array(parts[1], '') AS item_type,
14 regexp_split_to_array(parts[2], '') AS item_form,
16 FROM format_parts_array
17 ), converted_formats_flat AS (
19 CASE WHEN ARRAY_LENGTH(item_type,1) > 0
20 THEN '"0":[{"_attr":"item_type","_val":"' || ARRAY_TO_STRING(item_type,'"},{"_attr":"item_type","_val":"') || '"}]'
23 CASE WHEN ARRAY_LENGTH(item_form,1) > 0
24 THEN '"1":[{"_attr":"item_form","_val":"' || ARRAY_TO_STRING(item_form,'"},{"_attr":"item_form","_val":"') || '"}]'
27 CASE WHEN item_lang <> ''
28 THEN '"2":[{"_attr":"item_lang","_val":"' || item_lang ||'"}]'
31 FROM format_parts_wide
32 ) UPDATE action.hold_request SET holdable_formats = '{' ||
33 converted_formats_flat.item_type || ',' ||
34 converted_formats_flat.item_form || ',' ||
35 converted_formats_flat.item_lang || '}'
36 FROM converted_formats_flat WHERE converted_formats_flat.id = action.hold_request.id;