CODEWITHSUNDEEP
arraylistcouchbasesql++
MSD
MSD

Reputation: 1407

Querying couchbase array column

Im trying to query the array column in couchbase using UNNESt. But getting the below error.

Query:

SELECT * FROM alerts UNNEST timestamps WHERE timestamps '2018-04-21T06:37:44.512Z' BETWEEN '2018-04-21T08:37:44.512Z'

Error messgae:

[
  {
    "code": 3000,
    "msg": "syntax error - at '2018-04-21T06:37:44.512Z'",
    "query_from_user": "SELECT * FROM `alerts` UNNEST timestamps WHERE timestamps '2018-04-21T06:37:44.512Z'  BETWEEN '2018-04-21T08:37:44.512Z'"
  }
]

Table content: SELECT * FROM 'alerts';

[
  {
    "alerts": {
      "alertid": "3c217a81450278b38f3ad1a7260955d4",
      "severity": 3,
      "tally": 540,
      "timestamps": [
        "2018-04-21T07:37:44.512Z"
      ],
      "type": "ALERTS"
    }
  }
]

Upvotes: 1

Views: 258

Answers (1)

Matthew Groves
Matthew Groves

Reputation: 26169

I'm not sure exactly what results you're trying to get, but first thing is that I see a syntax error: I assume you're trying to find something between two dates, but you've got a date literal, then BETWEEN then another date literal. This is not correct syntax. It would be more like BETWEEN <literal> AND <literal>.

My first guess, since you are trying to UNNEST, is maybe you are trying to do an intra-document cross join, returning the join product with the timestamps that fit within a range, which you could do like this:

SELECT a.*, t
FROM alerts a
UNNEST a.timestamps t
WHERE t BETWEEN '2018-04-21T06:37:44.512Z' AND '2018-04-21T08:37:44.512Z';

My next guess, is that if you are trying to find all alert documents where any of the timestamps fell within a certain range, this would work:

SELECT a.*
FROM alerts a
WHERE 
   ANY x IN a.timestamps
   SATISFIES (x BETWEEN '2018-04-21T06:37:44.512Z' and '2018-04-21T08:37:44.512Z')
END;

Hopefully one of those two answers will fit your needs.

Upvotes: 1

Related Questions