Conditional logic for S3 Select query for a JSON

Question

I have 2 JSON objects that will be uploaded in a S3 bucket

Structure 1

{
  "name" : {
    "messageType" : "POSITIVE",
    
  },
  "Request" : {
    "subrequest1" : [ "1234" ]
  }
}

Structure 2

{
  "name": {
    "messageType": "NEGATIVE",
    
  },
  "Request": {
    {
      "subrequest2": [
        {
          "subrequest3": "5678",
          
        }
      ]
    }
  }
}

Now i need a single query combo to extract the value of subrequest1 in Structure1 and subrequest3 in Structure2.

I have tried multiple queries to get this working

Trial 1 Query

SELECT * FROM s3object[*].Request.subrequest2[*].subrequest3
      SELECT * FROM s3object[*].Request.subrequest1[*]

This combination is working for one structure and is failing for the other structure, and when i reverse the order of the query it works for one structure and fails for the other structure

Trial 2

SELECT s.Request.subrequest2[*].subrequest3 FROM s3object s WHERE s.name.messageType = 'NEGATIVE'
SELECT s.Request.subrequest1[*] FROM s3object s WHERE s.name.messageType = 'POSITIVE'

This combination is working for one structure and is failing for the other structure, and when i reverse the order of the query it works for one structure and fails for the other structure

TRIAL 3

SELECT 
  CASE 
    WHEN s.name.messageType = 'NEGATIVE' = 'NEGATIVE' THEN s.Request.subrequest2[*].subrequest3
    WHEN s.name.messageType = 'POSITIVE' THEN s.Request.subrequest1[*]
    ELSE NULL
  END 
FROM 
  s3object s

This is not working at all

Answer 1

You JSONs in the original post are malformed. Here's a query that works on slightly corrected versions of them:

{
  "name": {
    "messageType": "POSITIVE"
  },
  "Request": {
    "subrequest1": [
      "1234"
    ]
  }
}

{
  "name": {
    "messageType": "NEGATIVE"
  },
  "Request": {
    "subrequest2": [
      {
        "subrequest3": "5678"
      }
    ]
  }
}

SELECT  CASE s.name.messageType WHEN 'POSITIVE' THEN s.Request.subrequest1[0] WHEN 'NEGATIVE' THEN s.Request.subrequest2[0].subrequest3 END AS "value" FROM s3object[*] s