合并多条JSON输出查询：liaison表JSON字段更新SQL技术问询

Got it, let's combine those two update queries into a single statement while preserving every bit of the original logic—turning single ID fields into arrays and renaming the keys appropriately.

Simplified Merged Query #

This version works great for most cases, handling records with either (or both) of the target JSON fields:

UPDATE liaison 
SET content = 
  replace(
    replace(
      replace(
        replace(content::varchar, 
                content->>'important_maintenance_problem_id', 
                '{' || (content->>'important_maintenance_problem_id') || '}'
        ), 
        'important_maintenance_problem_id', 
        'important_maintenance_problem_ids'
      ),
      content->>'urgent_maintenance_problem_id', 
      '{' || (content->>'urgent_maintenance_problem_id') || '}'
    ), 
    'urgent_maintenance_problem_id', 
    'urgent_maintenance_problem_ids'
  )::JSON 
WHERE content ? 'important_maintenance_problem_id' 
   OR content ? 'urgent_maintenance_problem_id';

What this does: #

• Preserves original logic: For records with important_maintenance_problem_id, it wraps the value in curly braces to make it an array, then renames the key to important_maintenance_problem_ids—exactly like your first query.
• Handles the second field: Does the same transformation for urgent_maintenance_problem_id → urgent_maintenance_problem_ids, matching your second query.
• Covers all target records: The OR in the WHERE clause ensures any record with either field gets updated, even if it has both.
• No interference: The nested replace operations run sequentially, so modifying one field doesn't affect the other.

More Rigorous Version (Avoid Edge Cases) #

If you want to guard against super rare edge cases (like a value that accidentally matches a key name), use a CASE statement to handle each scenario explicitly:

UPDATE liaison 
SET content = CASE
  -- Handle records with both fields
  WHEN content ? 'important_maintenance_problem_id' AND content ? 'urgent_maintenance_problem_id' THEN
    replace(
      replace(
        replace(
          replace(content::varchar, 
                  content->>'important_maintenance_problem_id', 
                  '{' || (content->>'important_maintenance_problem_id') || '}'
          ), 
          'important_maintenance_problem_id', 
          'important_maintenance_problem_ids'
        ),
        content->>'urgent_maintenance_problem_id', 
        '{' || (content->>'urgent_maintenance_problem_id') || '}'
      ), 
      'urgent_maintenance_problem_id', 
      'urgent_maintenance_problem_ids'
    )::JSON
  -- Handle records with only the important field
  WHEN content ? 'important_maintenance_problem_id' THEN
    replace(
      replace(content::varchar, 
              content->>'important_maintenance_problem_id', 
              '{' || (content->>'important_maintenance_problem_id') || '}'
      ), 
      'important_maintenance_problem_id', 
      'important_maintenance_problem_ids'
    )::JSON
  -- Handle records with only the urgent field
  WHEN content ? 'urgent_maintenance_problem_id' THEN
    replace(
      replace(content::varchar, 
              content->>'urgent_maintenance_problem_id', 
              '{' || (content->>'urgent_maintenance_problem_id') || '}'
      ), 
      'urgent_maintenance_problem_id', 
      'urgent_maintenance_problem_ids'
    )::JSON
END
WHERE content ? 'important_maintenance_problem_id' 
   OR content ? 'urgent_maintenance_problem_id';

This version splits the logic into three clear cases, ensuring each transformation runs exactly as your original queries did, no surprises.

内容的提问来源于stack exchange，提问作者Pranav