N1Ql Query Help - Returning the first/last matching entry from array

SQL++
1

Hi,

I have an object like this:

{
  "a": "1",
  "b": [
    {
      "id": 1,
      "c": "IN",
      "d": "1st In Value"
    },
     ... other items in array
    {
      "id": 2,
      "c": "OUT",
      "d": "2nd Value"
      
    },
    ... other items in array
    {
      "id": 3,
      "c": "IN",
      "d": "last In Value"
    }
    ... other items in array none of which has "c" as "IN"
  ]
}

I want the n1ql query to return me results based on first and last entry where “c” is “IN”.

The first query (based on where condition having first entry of c is “IN”) should return

{
  "a": "1",
  "b": [{
      "id": 1,
      "c": "IN",
      "d": "1st In Value"
  }]
}

The 2nd query (based on where condition having last entry of c is “IN”) should return

{
  "a": "1",
  "b": [{
      "id": 3,
      "c": "IN",
      "d": "last In Value"
  }]
}
2 
WITH t AS ({"a":1,"b":[{"id":0},{"id":1,"c":true},{"id":2,"c":true},{"id":3,"c":true},{"id":4}]})
SELECT t.a, [ARRAY b FOR b IN t.b WHEN b.c IS VALUED END[0]] b
FROM t
;

(You could also use the syntax [FIRST b FOR b IN t.b WHEN b.c IS VALUED] b.)

and

WITH t AS ({"a":1,"b":[{"id":0},{"id":1,"c":true},{"id":2,"c":true},{"id":3,"c":true},{"id":4}]})
SELECT t.a, [ARRAY b FOR b IN t.b WHEN b.c IS VALUED END[-1]] b
FROM t
;

should get what you describe. You can of course extent/alter the WHEN condition to select other elements. The negative array index syntax is a short-cut indicating relative to the end of the array. You asked for the results in an array rather than simply as the object “b”, hence the literal array construction ([ and ]) surrounding the ARRAY…END[…] collection operator element reference.

HTH.

3

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.