28 lines
1.0 KiB
SQL
28 lines
1.0 KiB
SQL
UPDATE creative_projects AS project
|
|
SET cast_members = (
|
|
SELECT jsonb_agg(
|
|
CASE
|
|
WHEN jsonb_typeof(member.value->'follower_count') = 'string'
|
|
AND (member.value->>'follower_count') ~ '^[0-9]+$'
|
|
AND (member.value->>'follower_count')::numeric <= 9223372036854775807
|
|
THEN jsonb_set(
|
|
member.value,
|
|
'{follower_count}',
|
|
to_jsonb((member.value->>'follower_count')::bigint)
|
|
)
|
|
WHEN jsonb_typeof(member.value->'follower_count') = 'string'
|
|
THEN jsonb_set(member.value, '{follower_count}', 'null'::jsonb)
|
|
ELSE member.value
|
|
END
|
|
ORDER BY member.ordinality
|
|
) AS members
|
|
FROM jsonb_array_elements(project.cast_members) WITH ORDINALITY AS member(value, ordinality)
|
|
)
|
|
WHERE EXISTS (
|
|
SELECT 1
|
|
FROM jsonb_array_elements(project.cast_members) AS member(value)
|
|
WHERE jsonb_typeof(member.value->'follower_count') = 'string'
|
|
);
|
|
|
|
COMMENT ON COLUMN creative_projects.cast_members IS NULL;
|