couchbaseflattensql++

Return flat couchbase array for query over multiple documents

I have a couchbase query SELECT DISTINCT(itemID) FROM main_bucket._default._default WHERE createdDateTime> "2023-01-01T00:00:00"

returns the array

[
  {
    "itemID ": "515000"
  },
  {
    "itemID": "514900"
  },
...
]

Where each itemID is from a different document. What I want is to return a flat array like ["515000", "514900"...]. Any help? it's a list of approx 500 IDs from a store of documents in 6 or 7 figures, if that's relevant

I'm new to couchbase/N1QL and have entry level SQL skills, so if I'm missing something obvious please say so.

Solution

  • Use RAW. RAW is allowed only when single projection

    SELECT DISTINCT RAW itemID 
FROM main_bucket._default._default AS t
WHERE createdDateTime> "2023-01-01T00:00:00"