CODEWITHSUNDEEP
couchbasesql++
megamoth
megamoth

Reputation: 693

N1QL: Using select directly on arrays

I am having problems on querying objects inside an array.

I have this sample document

{
  ...
  status: 'active',
  current: { ... },
  history: [
    {
      id: '73bae187-1101-4fb3-a71a-2bbf90026eb3',
      date: '2017-03-28T09:32:22.884Z',
      content: 'This is second content'
    },
    {
      id: 'd6a6c63d-42db-4ef5-88e9-616cfe539a57',
      date: '2017-03-25T09:32:22.884Z',
      content: 'This is first content'
    },
    {
      id: '3fbdb846-2b55-4ff8-8960-86997ef31556',
      schedule: '1970-01-01T00:00:00.000Z',
      content: 'This is a very old content'
    }
  ]
}

I want to directly query from the history array sub document in which I want to apply date filters.

Is it possible in n1ql to retrieve the history array that will only contain the objects that satisfies the condition?

Can I apply a limit to which I can control the number of the the returned objects inside the array?

I tried some queries using splicing [0:$1] where limit is an input but doesn't work when the limit is greater than the array size.

Thanks for the help.

Upvotes: 1

Views: 1314

Answers (1)

vsr
vsr

Reputation: 7414

Try either of the following approach:

SELECT h[0:least(ARRAY_LENGTH(h), $arraylimit)] As h FROM default As d
LET h = ARRAY v FOR v IN d.history WHEN v.id  IN  [ 'xyz', 'pqr'] END;

Or:

SELECT h[0:least(ARRAY_LENGTH(h),$limit)] As h (SELECT ARRAY_AGG(h) As h 
FROM default As d UNNEST d.history As h WHERE h.id IN  [ 'xyz', 'pqr'] GROUP BY d) As q;

Upvotes: 4

Related Questions