How to format the N1QL output for a subquery

SQL++
1

I have a query which uses a subquery that gets some data via by using Key of the doc and looks like this.

SELECT META().id,
       msg_count,
       click_count,
       notify,
       send_DateTime,
       send_to,
       send_from,
       subject,
       campaign,
       (
           SELECT summary
           FROM Contacts USE KEYS c.campaign)
FROM Contacts c
WHERE _type = "track_request"
    AND ANY b IN c.send_to SATISFIES b IN ['customer@aol.com'] END
ORDER BY send_DateTime DESC

All works as expected except i would like the

"$1": [
      {
        "subject": "Housing Report for Week of xxxxxxx"
      }
    ],

to be just a single key in the json result no array and no $1. How can i get this done. Below is the full output of the current query

  {
    "$1": [
      {
        "summary": "Housing Report: Permanently Parked"
      }
    ],
    "campaign": "email_campaign::aa32d79a-1044-4043-adbc-6701aff8ecf2",
    "click_count": 0,
    "id": "track_request::c0945513-d38b-4a6c-a2e8-d824faf03f1d",
    "msg_count": 0,
    "notify": false,
    "send_DateTime": "2021-07-30T23:42:25.476Z",
    "send_from": "sender@mail.com",
    "send_to": [
      "customer@aol.com"
    ],
    "subject": "Housing Report for Week of xxxxxxx"
  },
2

Try this. N1QL allows returning multiple values from a subquery and hence, they’re returned as arrays. When you need/expect just one value, simply get the first value (object) of the array and deference it.

SELECT META().id,
       msg_count,
       click_count,
       notify,
       send_DateTime,
       send_to,
       send_from,
       subject,
       campaign,
       (
           SELECT summary
           FROM Contacts USE KEYS c.campaign)[0].subject AS subject
FROM Contacts c
WHERE _type = "track_request"
    AND ANY b IN c.send_to SATISFIES b IN ['customer@aol.com'] END
ORDER BY send_DateTime DESC
3

Thanks, that did not provide the output i expected, but based on your info i used this one and it worked.

 (
           SELECT RAW summary
           FROM Contacts USE KEYS c.campaign)[0]  AS summary
2 Likes